hi
I am try to exec the folowing statement i got error, syntax error near
go......


DECLARE @DBName as VARCHAR(100)
declare @SQL1 varchar(800)

SELECT @dbname = min(DBName) FROM tblesafedbs
WHILE @DBName IS NOT NULL
BEGIN --loop
select @sql1 =('USE ' + @dbname + char(13) + 'Go')
exec (@sql1)
select count(*) from table1

SELECT @DBName = MIN(DBName) FROM tblesafedbs WHERE DBName > @DBName
END --loop



I CAN'T use

SET @sql1 = 'select count(*) from ' + @dbname + '.table1'

go is not a SQL command. It is used by many front ends as a batch
separater, but not actually sent to SQL Server. So when in QA you write

Select Col1 From Table1
go
Select Col2 From Table2

QA does not send the go to SQL Server, instead it just sends two separate
batches to SQL Server. So you get your error because you are sending the go
to SQL Server in your exec statement. And while QA understands "go", SQL
Server doesn't and generates an error.

The second problem is that when you use a "USE" command in an exec()
command, the use of the new database is only effective why you are executing
that command. As soon as you return from the exec, you will be back to
using the original database. So you need to also put the select into the
exec().

Perhaps the following will work for you:

DECLARE @DBName as VARCHAR(100)
declare @SQL1 varchar(800)

SELECT @dbname = min(DBName) FROM tblesafedbs
WHILE @DBName IS NOT NULL
BEGIN --loop
select @sql1 =('USE ' + @dbname + ' select count(*) from table1')
exec (@sql1)


SELECT @DBName = MIN(DBName) FROM tblesafedbs WHERE DBName > @DBName
END --loop

Tom

"Mecn" <mecn@yahoo.com> wrote in message
news:%23UTJ1505IHA.3696@TK2MSFTNGP04.phx.gbl...
> hi
> I am try to exec the folowing statement i got error, syntax error near
> go......
>
>
> DECLARE @DBName as VARCHAR(100)
> declare @SQL1 varchar(800)
>
> SELECT @dbname = min(DBName) FROM tblesafedbs
> WHILE @DBName IS NOT NULL
> BEGIN --loop
> select @sql1 =('USE ' + @dbname + char(13) + 'Go')
> exec (@sql1)
> select count(*) from table1
>
> SELECT @DBName = MIN(DBName) FROM tblesafedbs WHERE DBName > @DBName
> END --loop
>
>
>
> I CAN'T use
>
> SET @sql1 = 'select count(*) from ' + @dbname + '.table1'
>

"Mecn" <mecn@yahoo.com> wrote in message
news:%23UTJ1505IHA.3696@TK2MSFTNGP04.phx.gbl...
> hi
> I am try to exec the folowing statement i got error, syntax error near
> go......
>
>
> DECLARE @DBName as VARCHAR(100)
> declare @SQL1 varchar(800)
>
> SELECT @dbname = min(DBName) FROM tblesafedbs
> WHILE @DBName IS NOT NULL
> BEGIN --loop
> select @sql1 =('USE ' + @dbname + char(13) + 'Go')
> exec (@sql1)
> select count(*) from table1
>
> SELECT @DBName = MIN(DBName) FROM tblesafedbs WHERE DBName > @DBName
> END --loop
>
>
>
> I CAN'T use
>
> SET @sql1 = 'select count(*) from ' + @dbname + '.table1'
>

I agree with everything Tom said. I am wondering why you can't use the last
statement, other than it is incorrect. Have you tried:

SET @sql = 'SELECT COUNT(*) FROM ' + @dbname + '..table1' -- Notice the
two periods. This will assume your default schema in that database. Your
version of the command would fail every time.


Rick Sawtell

Thanks for all responses. I will try. I can't use the last statement beacuse
I can't use variables in the statement by open cursor statment



"Rick Sawtell" <r_sawtell@nospam.hotmail.com> wrote in message
news:eewc26A6IHA.4988@TK2MSFTNGP04.phx.gbl...
>
> "Mecn" <mecn@yahoo.com> wrote in message
> news:%23UTJ1505IHA.3696@TK2MSFTNGP04.phx.gbl...
>> hi
>> I am try to exec the folowing statement i got error, syntax error near
>> go......
>>
>>
>> DECLARE @DBName as VARCHAR(100)
>> declare @SQL1 varchar(800)
>>
>> SELECT @dbname = min(DBName) FROM tblesafedbs
>> WHILE @DBName IS NOT NULL
>> BEGIN --loop
>> select @sql1 =('USE ' + @dbname + char(13) + 'Go')
>> exec (@sql1)
>> select count(*) from table1
>>
>> SELECT @DBName = MIN(DBName) FROM tblesafedbs WHERE DBName > @DBName
>> END --loop
>>
>>
>>
>> I CAN'T use
>>
>> SET @sql1 = 'select count(*) from ' + @dbname + '.table1'
>>
>
> I agree with everything Tom said. I am wondering why you can't use the
> last statement, other than it is incorrect. Have you tried:
>
> SET @sql = 'SELECT COUNT(*) FROM ' + @dbname + '..table1' -- Notice the
> two periods. This will assume your default schema in that database. Your
> version of the command would fail every time.
>
>
> Rick Sawtell
>
>