Sunday, March 25, 2012
cursor How to >> use @dbname
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
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...
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
Sunday, February 19, 2012
CTE and data type
I have a recursive CTE and within it is a concatenated string field. It fails with this message
Msg 240, Level 16, State 1, Procedure spGetBill, Line 14
Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".
Here is the T-Sql:
WITH ExplodedParts (PID, CID, BID, PBOMID)
AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID
FROM dbo.vwLatestPBOMIDs
WHERE ParentID=@.ParentID
UNION ALL
SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID
FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN
ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)
SELECT PID, CID, BID, PBOMId
FROM ExplodedParts AS ExplodedParts_1
ORDER BY PID
The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.
Does anyone have a clue why this is causing the datatype issue?
Any help is appreciated.
In anchor part try changing:
convert(varchar,ParentID) as BID
to:
convert(varchar(max),ParentID) as BID
|||Change:
convert(varchar, ParentID)
to:
cast(ParentID as varchar(255)) -- or whatever maximum length you need
And change:
EP.PID + '.' + convert(varchar,NextLevel.ParentID)
to:
cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))
By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.
|||Thank you, that solved the issue.CTE and data type
I have a recursive CTE and within it is a concatenated string field. It fails with this message
Msg 240, Level 16, State 1, Procedure spGetBill, Line 14
Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".
Here is the T-Sql:
WITH ExplodedParts (PID, CID, BID, PBOMID)
AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID
FROM dbo.vwLatestPBOMIDs
WHERE ParentID=@.ParentID
UNION ALL
SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID
FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN
ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)
SELECT PID, CID, BID, PBOMId
FROM ExplodedParts AS ExplodedParts_1
ORDER BY PID
The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.
Does anyone have a clue why this is causing the datatype issue?
Any help is appreciated.
In anchor part try changing:
convert(varchar,ParentID) as BID
to:
convert(varchar(max),ParentID) as BID
|||Change:
convert(varchar, ParentID)
to:
cast(ParentID as varchar(255)) -- or whatever maximum length you need
And change:
EP.PID + '.' + convert(varchar,NextLevel.ParentID)
to:
cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))
By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.
|||Thank you, that solved the issue.Friday, February 17, 2012
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.