cursor How to >> use @dbname

The following query ERRORs out with this >>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name
, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
===================================
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
===================================
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================
> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>

cursor How to >> use @dbname

The following query ERRORs out with this >>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
==================================="Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
==================================="WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>> Try this...
>>
>> SET NOCOUNT ON
>> DECLARE @.DBname varchar(11)
>> declare db_cursor cursor for
>> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
>> order by name
>> open DB_Cursor
>> fetch next from DB_Cursor
>> into @.DBname
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> declare @.sql varchar(500)
>> Set @.sql = 'use ' + @.DBname +'
>> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
>> from sysobjects
>> where user_name(uid) !=''DBO''
>> order by uid, name'
>> Exec (@.sql)
>> fetch next from DB_Cursor
>> into @.DBname
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>

Cursor Error...

...I can't seem to work it out, maybe you can help. I get a
Server: Msg 137, Level 15, State 2, Line 22
Must declare the variable '@.tbl_name'.
when I run...
declare @.counter int, @.tbl_name varchar(40)
select @.counter = 1
declare loop_cursor cursor for SELECT distinct name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
UPDATE @.tbl_name
SET marcA = substring(marcA,1,2)
WHERE marc = 1
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
END
FETCH loop_cursor INTO @.tbl_name
CLOSE loop_cursor
DEALLOCATE loop_cursor
am i missing something blatently obvious, it's friday so probably so arrrgh
The creates are as follows for anyone wishing to try it
drop table marc, marc1
create table marc (marc int, marcA varchar(5))
create table marc1 (marc int, marcA varchar(5))
insert into marc values(1, 'ABC')
insert into marc values(2, 'ABC')
insert into marc1 values(1, 'ABC')
insert into marc1 values(2, 'ABC')The SQL Server parser doesn't resolve variables as table/column names.
You'll need to use dynamic SQL to do this in Transact-SQL.
See http://www.sommarskog.se/dynamic_sql.html for an article on dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:38C354EE-43DC-42D8-B4B9-E503B72D34EA@.microsoft.com...
> ...I can't seem to work it out, maybe you can help. I get a
> Server: Msg 137, Level 15, State 2, Line 22
> Must declare the variable '@.tbl_name'.
> when I run...
> declare @.counter int, @.tbl_name varchar(40)
> select @.counter = 1
> declare loop_cursor cursor for SELECT distinct name FROM
> sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> UPDATE @.tbl_name
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> END
> FETCH loop_cursor INTO @.tbl_name
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> am i missing something blatently obvious, it's friday so probably so
> arrrgh
>
> The creates are as follows for anyone wishing to try it
> drop table marc, marc1
> create table marc (marc int, marcA varchar(5))
> create table marc1 (marc int, marcA varchar(5))
> insert into marc values(1, 'ABC')
> insert into marc values(2, 'ABC')
> insert into marc1 values(1, 'ABC')
> insert into marc1 values(2, 'ABC')|||thanks Dan, I replaced
/*
UPDATE @.tbl_name1
SET marcA = substring(marcA,1,2)
WHERE marc = 1
*/
to dynamic as follows but it only updates the first row of the first
table...Any ideas
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command|||> but it only updates the first row of the first
> table...Any ideas
It appears you need a FETCH within the WHILE loop. Try:
declare @.counter int, @.tbl_name varchar(40)
declare @.command nvarchar(4000)
select @.counter = 1
declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
FETCH loop_cursor INTO @.tbl_name
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
> thanks Dan, I replaced
> /*
> UPDATE @.tbl_name1
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> */
> to dynamic as follows but it only updates the first row of the first
> table...Any ideas
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
>|||thankyou Dan, appreciate it.
"Dan Guzman" wrote:

> It appears you need a FETCH within the WHILE loop. Try:
>
> declare @.counter int, @.tbl_name varchar(40)
> declare @.command nvarchar(4000)
> select @.counter = 1
> declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> FETCH loop_cursor INTO @.tbl_name
> END
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
>
>sql

CSV file into SQL Server

Hello GUyz,

When I do a Bulk insert to import a CSV file into the SQL Server i get the following error.

Msg 511, Level 16, State 1, Line 1

Cannot create a row of size 20809 which is greater than the allowable maximum of 8060.

The statement has been terminated.

Is there a way to get over it. Thank you.

--SRI.

Did you specify both a Row Delimiter and a Column Delimiter?

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes. I did it. But doesn't work.|||

Sri. wrote:

Yes. I did it. But doesn't work.

You're going to have to provide plenty more details than, "doesn't work."

Can you provide sample data? How long is each row, typically? What have you tried for the various settings in the source config?|||

It has like 982 columns and 2545 rows.

Some of the data in the rows is really big . It has like more than 200 characters.

SO I went to the advanced properties in the data source and changed the type as Unicode string and changed the OutputColumnwidth to 300 and tried to execute it but it says "Text was truncated". So I tried with bulkInsert but it gave me the error I showed above.

|||If it's truly a comma separated file, then you shouldn't have any issues, unless the data types defined in the source connection aren't long enough. You need to go through the advance properties and select the columns to verify that the lengths are correct.|||

Are you sure your file is properly formatted? All delimiters in each row need to be present in order for the file to be properly parsed.

Try to look for rows that cause problems, either by dividing the file in halves or redirecting rows with errors/truncations.

HTH.

|||

What are the data types of the columns in the SQL Server table? If you define them all as VARCHAR(8000) or NVARCHAR(4000) for example, it is possible to have a valid table definition, but have scenarios where the data being inserted to create a new row (or supplied to update an existing row) exceed the maximum table size.

A single "standard" row in SQL Server cannot exceed the maximum row size. You can get around that by defining your columns as TEXT or NTEXT instead of VARCHAR or NVARCHAR, or by defining them as VARCHAR(MAX) or NVARCHAR(MAX) if you are using SQL Server 2005. These BLOB data types are stored outside the row itself, so you can get around the row size limit.