Sql Activity log backup: convert

Showing posts with label convert. Show all posts

Saturday, February 25, 2012

adding leading zeros in select

Hi all,

I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

example:
table contents
123
456
789

desired select result:
000123
000456
000789

Who has a solution for this problem?select
replicate('0', 6-datalength(convert(varchar,NumericField)))
from MyTable

Or, better:

create function LZero(@.MyNum int, @.MyLength int)
returns varchar
as
begin
declare @.MyStr varchar
set @.MyStr = convert(varchar,@.MyNum)
if @.MyLength>datalength(@.MyStr)
set @.MyStr = replicate('0',(@.MyLength-datalength(@.MyStr)))+@.MyStr
return @.MyStr
end
go
select dbo.LZero(NumericField,6) from MyTable|||Thanx for your reply.

The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

But this will get me where I want. Thank you very much|||Ok.
I have no SQL Server near me to debug it, so go for the 1st option.|||Originally posted by kukuk
Ok.
I have no SQL Server near me to debug it, so go for the 1st option.

OK, I will, thanx again|||the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.|||Originally posted by jora
the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.

Thanx for your reply, I already found it out and fixed it.

Thursday, February 16, 2012

Adding database name at runtime

I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:

Adding database name at runtime

I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:

Adding database name at runtime

Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > Hi,
> >
> > I have below SQL query which calculates the database size for all
> > databases.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from dbo.sysfiles
> >
> > But I am not able to substitute the database name which I am getting
> > from the cursor at runtime.
> > I want to place the database name in the following query instead of
> > 'DBNAME'.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from >>DBNAME<<<.dbo.sysfiles
> >
> > Can we replace the 'DBNAME' with the actual database name from the
> > cursor and retrieve the values?
> >
> > Thanks,
> > Regards,
> > Pramod
> >|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>> Hi
>> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
>> ?.dbo.sysfiles'
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I have below SQL query which calculates the database size for all
>> > databases.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from dbo.sysfiles
>> >
>> > But I am not able to substitute the database name which I am getting
>> > from the cursor at runtime.
>> > I want to place the database name in the following query instead of
>> > 'DBNAME'.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from >>DBNAME<<<.dbo.sysfiles
>> >
>> > Can we replace the 'DBNAME' with the actual database name from the
>> > cursor and retrieve the values?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > Thanks for your feedback. It really worked.
> > Now, I have another question.
> >
> > I have a variable @.dbsize to which I am assigning the value of database
> > size and I am using the variable value in the code
> >
> > Below is my SQL query which returns the database free space in percent
> > for all the databases.
> >
> > SET nocount on
> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > master..sysdatabases
> >
> > OPEN AllDatabaseInfo
> >
> > IF object_id('tempdb..#test2') IS NOT NULL
> > BEGIN
> > DROP TABLE #test2
> > END
> >
> > CREATE TABLE #test2 (
> > [Database Name] [varchar] (1000),
> > [Database Space Available] [varchar] (1000)
> > )
> >
> > IF object_id('tempdb..#test3') IS NOT NULL
> > BEGIN
> > DROP TABLE #test3
> > END
> >
> > CREATE TABLE #test3 (
> > [dbsize] [varchar] (1000),
> > [logsize] [varchar] (1000)
> > )
> >
> > DELETE FROM #test2
> > DECLARE @.DBName nvarchar(1000)
> > DECLARE @.sql nvarchar(1000)
> > DECLARE @.str sysname
> > SET @.sql = ''
> > SET @.DBName = ''
> > DECLARE @.pages bigint
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --
> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > 0 end))FROM ?.dbo.sysfiles'
> >
> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > <> 0 then size else 0 end))
> > FROM dbo.sysfiles
> >
> > SELECT @.reservedpages = sum(a.total_pages)
> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > = a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > --
> >
> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > 15,2)
> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > @.str
> > EXEC sp_executesql @.sql
> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > END
> >
> > CLOSE AllDatabaseInfo
> > DEALLOCATE AllDatabaseInfo
> >
> > SELECT * FROM #test2
> > SET nocount off
> >
> >
> >
> > Now this code returns the free space value in percent only for one
> > database because I am unable to substitute the database name when I
> > calculate the @.dbsize.
> >
> > Can you help me?
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > Uri Dimant wrote:
> >> Hi
> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> >> ?.dbo.sysfiles'
> >>
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> > Hi,
> >> >
> >> > I have below SQL query which calculates the database size for all
> >> > databases.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from dbo.sysfiles
> >> >
> >> > But I am not able to substitute the database name which I am getting
> >> > from the cursor at runtime.
> >> > I want to place the database name in the following query instead of
> >> > 'DBNAME'.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >
> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> > cursor and retrieve the values?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>> What version of sql server you using..'
>> vt
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
>> > Hi Uri,
>> >
>> > Thanks for your feedback. It really worked.
>> > Now, I have another question.
>> >
>> > I have a variable @.dbsize to which I am assigning the value of database
>> > size and I am using the variable value in the code
>> >
>> > Below is my SQL query which returns the database free space in percent
>> > for all the databases.
>> >
>> > SET nocount on
>> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
>> > master..sysdatabases
>> >
>> > OPEN AllDatabaseInfo
>> >
>> > IF object_id('tempdb..#test2') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test2
>> > END
>> >
>> > CREATE TABLE #test2 (
>> > [Database Name] [varchar] (1000),
>> > [Database Space Available] [varchar] (1000)
>> > )
>> >
>> > IF object_id('tempdb..#test3') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test3
>> > END
>> >
>> > CREATE TABLE #test3 (
>> > [dbsize] [varchar] (1000),
>> > [logsize] [varchar] (1000)
>> > )
>> >
>> > DELETE FROM #test2
>> > DECLARE @.DBName nvarchar(1000)
>> > DECLARE @.sql nvarchar(1000)
>> > DECLARE @.str sysname
>> > SET @.sql = ''
>> > SET @.DBName = ''
>> > DECLARE @.pages bigint
>> > ,@.dbsize bigint
>> > ,@.logsize bigint
>> > ,@.reservedpages bigint
>> > ,@.unallocatedsize bigint
>> > ,@.totalsize bigint
>> >
>> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN
>> > --
>> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =>> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
>> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
>> > 0 end))FROM ?.dbo.sysfiles'
>> >
>> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
>> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
>> > <> 0 then size else 0 end))
>> > FROM dbo.sysfiles
>> >
>> > SELECT @.reservedpages = sum(a.total_pages)
>> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
>> > = a.container_id
>> > left join sys.internal_tables it on p.object_id = it.object_id
>> >
>> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
>> > (15,2),@.logsize))/128.00
>> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
>> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
>> > --
>> >
>> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
>> > 15,2)
>> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
>> > @.str
>> > EXEC sp_executesql @.sql
>> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
>> > END
>> >
>> > CLOSE AllDatabaseInfo
>> > DEALLOCATE AllDatabaseInfo
>> >
>> > SELECT * FROM #test2
>> > SET nocount off
>> >
>> >
>> >
>> > Now this code returns the free space value in percent only for one
>> > database because I am unable to substitute the database name when I
>> > calculate the @.dbsize.
>> >
>> > Can you help me?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>> > Uri Dimant wrote:
>> >> Hi
>> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> >> end))FROM
>> >> ?.dbo.sysfiles'
>> >>
>> >>
>> >>
>> >> <ipramod@.gmail.com> wrote in message
>> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> >> > Hi,
>> >> >
>> >> > I have below SQL query which calculates the database size for all
>> >> > databases.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from dbo.sysfiles
>> >> >
>> >> > But I am not able to substitute the database name which I am getting
>> >> > from the cursor at runtime.
>> >> > I want to place the database name in the following query instead of
>> >> > 'DBNAME'.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from >>DBNAME<<<.dbo.sysfiles
>> >> >
>> >> > Can we replace the 'DBNAME' with the actual database name from the
>> >> > cursor and retrieve the values?
>> >> >
>> >> > Thanks,
>> >> > Regards,
>> >> > Pramod
>> >> >
>> >
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > SQL Server 2005 RTM Version
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > vt wrote:
> >> What version of sql server you using..'
> >>
> >> vt
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> >> > Hi Uri,
> >> >
> >> > Thanks for your feedback. It really worked.
> >> > Now, I have another question.
> >> >
> >> > I have a variable @.dbsize to which I am assigning the value of database
> >> > size and I am using the variable value in the code
> >> >
> >> > Below is my SQL query which returns the database free space in percent
> >> > for all the databases.
> >> >
> >> > SET nocount on
> >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> >> > master..sysdatabases
> >> >
> >> > OPEN AllDatabaseInfo
> >> >
> >> > IF object_id('tempdb..#test2') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test2
> >> > END
> >> >
> >> > CREATE TABLE #test2 (
> >> > [Database Name] [varchar] (1000),
> >> > [Database Space Available] [varchar] (1000)
> >> > )
> >> >
> >> > IF object_id('tempdb..#test3') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test3
> >> > END
> >> >
> >> > CREATE TABLE #test3 (
> >> > [dbsize] [varchar] (1000),
> >> > [logsize] [varchar] (1000)
> >> > )
> >> >
> >> > DELETE FROM #test2
> >> > DECLARE @.DBName nvarchar(1000)
> >> > DECLARE @.sql nvarchar(1000)
> >> > DECLARE @.str sysname
> >> > SET @.sql = ''
> >> > SET @.DBName = ''
> >> > DECLARE @.pages bigint
> >> > ,@.dbsize bigint
> >> > ,@.logsize bigint
> >> > ,@.reservedpages bigint
> >> > ,@.unallocatedsize bigint
> >> > ,@.totalsize bigint
> >> >
> >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> >> > WHILE @.@.FETCH_STATUS = 0
> >> > BEGIN
> >> > --
> >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> >> > 0 end))FROM ?.dbo.sysfiles'
> >> >
> >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> >> > <> 0 then size else 0 end))
> >> > FROM dbo.sysfiles
> >> >
> >> > SELECT @.reservedpages = sum(a.total_pages)
> >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> >> > = a.container_id
> >> > left join sys.internal_tables it on p.object_id = it.object_id
> >> >
> >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> >> > (15,2),@.logsize))/128.00
> >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> >> > --
> >> >
> >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> >> > 15,2)
> >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> >> > @.str
> >> > EXEC sp_executesql @.sql
> >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> >> > END
> >> >
> >> > CLOSE AllDatabaseInfo
> >> > DEALLOCATE AllDatabaseInfo
> >> >
> >> > SELECT * FROM #test2
> >> > SET nocount off
> >> >
> >> >
> >> >
> >> > Now this code returns the free space value in percent only for one
> >> > database because I am unable to substitute the database name when I
> >> > calculate the @.dbsize.
> >> >
> >> > Can you help me?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >> > Uri Dimant wrote:
> >> >> Hi
> >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> >> end))FROM
> >> >> ?.dbo.sysfiles'
> >> >>
> >> >>
> >> >>
> >> >> <ipramod@.gmail.com> wrote in message
> >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have below SQL query which calculates the database size for all
> >> >> > databases.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from dbo.sysfiles
> >> >> >
> >> >> > But I am not able to substitute the database name which I am getting
> >> >> > from the cursor at runtime.
> >> >> > I want to place the database name in the following query instead of
> >> >> > 'DBNAME'.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >> >
> >> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> >> > cursor and retrieve the values?
> >> >> >
> >> >> > Thanks,
> >> >> > Regards,
> >> >> > Pramod
> >> >> >
> >> >
> >|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
> > Sorry buddy.. still using 2000
> >
> >
> >
> > <ipramod@.gmail.com> wrote in message
> > news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > > SQL Server 2005 RTM Version
> > >
> > > Thanks,
> > > Regards,
> > > Pramod
> > >
> > > vt wrote:
> > >> What version of sql server you using..'
> > >>
> > >> vt
> > >>
> > >>
> > >> <ipramod@.gmail.com> wrote in message
> > >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > >> > Hi Uri,
> > >> >
> > >> > Thanks for your feedback. It really worked.
> > >> > Now, I have another question.
> > >> >
> > >> > I have a variable @.dbsize to which I am assigning the value of database
> > >> > size and I am using the variable value in the code
> > >> >
> > >> > Below is my SQL query which returns the database free space in percent
> > >> > for all the databases.
> > >> >
> > >> > SET nocount on
> > >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > >> > master..sysdatabases
> > >> >
> > >> > OPEN AllDatabaseInfo
> > >> >
> > >> > IF object_id('tempdb..#test2') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test2
> > >> > END
> > >> >
> > >> > CREATE TABLE #test2 (
> > >> > [Database Name] [varchar] (1000),
> > >> > [Database Space Available] [varchar] (1000)
> > >> > )
> > >> >
> > >> > IF object_id('tempdb..#test3') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test3
> > >> > END
> > >> >
> > >> > CREATE TABLE #test3 (
> > >> > [dbsize] [varchar] (1000),
> > >> > [logsize] [varchar] (1000)
> > >> > )
> > >> >
> > >> > DELETE FROM #test2
> > >> > DECLARE @.DBName nvarchar(1000)
> > >> > DECLARE @.sql nvarchar(1000)
> > >> > DECLARE @.str sysname
> > >> > SET @.sql = ''
> > >> > SET @.DBName = ''
> > >> > DECLARE @.pages bigint
> > >> > ,@.dbsize bigint
> > >> > ,@.logsize bigint
> > >> > ,@.reservedpages bigint
> > >> > ,@.unallocatedsize bigint
> > >> > ,@.totalsize bigint
> > >> >
> > >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > >> > WHILE @.@.FETCH_STATUS = 0
> > >> > BEGIN
> > >> > --
> > >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> > 0 end))FROM ?.dbo.sysfiles'
> > >> >
> > >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > >> > <> 0 then size else 0 end))
> > >> > FROM dbo.sysfiles
> > >> >
> > >> > SELECT @.reservedpages = sum(a.total_pages)
> > >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > >> > = a.container_id
> > >> > left join sys.internal_tables it on p.object_id = it.object_id
> > >> >
> > >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > >> > (15,2),@.logsize))/128.00
> > >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > >> > --
> > >> >
> > >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > >> > 15,2)
> > >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > >> > @.str
> > >> > EXEC sp_executesql @.sql
> > >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > >> > END
> > >> >
> > >> > CLOSE AllDatabaseInfo
> > >> > DEALLOCATE AllDatabaseInfo
> > >> >
> > >> > SELECT * FROM #test2
> > >> > SET nocount off
> > >> >
> > >> >
> > >> >
> > >> > Now this code returns the free space value in percent only for one
> > >> > database because I am unable to substitute the database name when I
> > >> > calculate the @.dbsize.
> > >> >
> > >> > Can you help me?
> > >> >
> > >> > Thanks,
> > >> > Regards,
> > >> > Pramod
> > >> >
> > >> > Uri Dimant wrote:
> > >> >> Hi
> > >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> > >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> > >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > >> >> end))FROM
> > >> >> ?.dbo.sysfiles'
> > >> >>
> > >> >>
> > >> >>
> > >> >> <ipramod@.gmail.com> wrote in message
> > >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > >> >> > Hi,
> > >> >> >
> > >> >> > I have below SQL query which calculates the database size for all
> > >> >> > databases.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from dbo.sysfiles
> > >> >> >
> > >> >> > But I am not able to substitute the database name which I am getting
> > >> >> > from the cursor at runtime.
> > >> >> > I want to place the database name in the following query instead of
> > >> >> > 'DBNAME'.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from >>DBNAME<<<.dbo.sysfiles
> > >> >> >
> > >> >> > Can we replace the 'DBNAME' with the actual database name from the
> > >> >> > cursor and retrieve the values?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > Regards,
> > >> >> > Pramod
> > >> >> >
> > >> >
> > >

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYouWant] SET Sent =1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
--
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer
,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYou
Want] SET Sen
t =
1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtraile
r,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Sunday, February 12, 2012

Adding another step to the following

I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:

> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:

>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:

> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>

Saturday, February 25, 2012

adding leading zeros in select

Thursday, February 16, 2012

Adding database name at runtime

Adding database name at runtime

Adding database name at runtime

Adding data with a query

Adding data with a query

Sunday, February 12, 2012

Adding another step to the following

Sql Activity log backup

Blog Archive

About Me