script error

Question

Post reply

script error

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 1

Here is me assuming it's 2008 :blush: What is it they say about assuming !

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

michael.higgins (2/24/2014)
Here is me assuming it's 2008 :blush: What is it they say about assuming !

Yeah, I mean it's the SQL Server 2008 forum, surely it must be SQL Server 2008... 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 3

GilaMonster (2/24/2014)
michael.higgins (2/24/2014)
Here is me assuming it's 2008 :blush: What is it they say about assuming !
Yeah, I mean it's the SQL Server 2008 forum, surely it must be SQL Server 2008... 😉

Go figure :crazy:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

michael.higgins (2/24/2014)
GilaMonster (2/24/2014)
michael.higgins (2/24/2014)
Here is me assuming it's 2008 :blush: What is it they say about assuming !
Yeah, I mean it's the SQL Server 2008 forum, surely it must be SQL Server 2008... 😉
Go figure :crazy:

There's nothing in your answer that's 2008-specific, should all work on 2005 just as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramyours2003 SSChampion Points: 12268 More actions · Answer 5

ramyours2003

SSChampion

Points: 12268

February 24, 2014 at 7:16 am

#1691532

Microsoft SQL Server 2005 - 9.00.4060.00 (X64)

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 6

GilaMonster (2/24/2014)
michael.higgins (2/24/2014)
GilaMonster (2/24/2014)
michael.higgins (2/24/2014)
Here is me assuming it's 2008 :blush: What is it they say about assuming !
Yeah, I mean it's the SQL Server 2008 forum, surely it must be SQL Server 2008... 😉
Go figure :crazy:
There's nothing in your answer that's 2008-specific, should all work on 2005 just as well.

Never spotted that to be honest. That is why I only have 3 blue squares and you have 15 :doze:

ramyours2003 SSChampion Points: 12268 More actions · Answer 7

ramyours2003

SSChampion

Points: 12268

February 24, 2014 at 7:21 am

#1691536

help me to resolve in sql 2005

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

ramyours2003 (2/24/2014)
help me to resolve in sql 2005

Identify the simplest component of your problem and tackle it. Try running this:

CREATE TABLE #DBROLES

(

DBName SYSNAME NOT NULL ,

UserName SYSNAME NOT NULL ,

db_owner VARCHAR(3) NOT NULL ,

db_accessadmin VARCHAR(3) NOT NULL ,

db_securityadmin VARCHAR(3) NOT NULL ,

db_ddladmin VARCHAR(3) NOT NULL ,

db_datareader VARCHAR(3) NOT NULL ,

db_datawriter VARCHAR(3) NOT NULL ,

db_denydatawriter VARCHAR(3) NOT NULL ,

db_denydatareader VARCHAR(3) NOT NULL ,

db_sysadmin VARCHAR(3) NOT NULL ,

db_serveradmin VARCHAR(3) NOT NULL ,

db_processadmin VARCHAR(3) NOT NULL ,

db_dbcreator VARCHAR(3) NOT NULL ,

Cur_Date DATETIME NOT NULL

DEFAULT GETDATE()

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

nm

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramyours2003 SSChampion Points: 12268 More actions · Answer 10

got the error while executing

CREATE TABLE #DBROLES

(

DBName SYSNAME NOT NULL ,

UserName SYSNAME NOT NULL ,

db_owner VARCHAR(3) NOT NULL ,

db_accessadmin VARCHAR(3) NOT NULL ,

db_securityadmin VARCHAR(3) NOT NULL ,

db_ddladmin VARCHAR(3) NOT NULL ,

db_datareader VARCHAR(3) NOT NULL ,

db_datawriter VARCHAR(3) NOT NULL ,

db_denydatawriter VARCHAR(3) NOT NULL ,

db_denydatareader VARCHAR(3) NOT NULL ,

db_sysadmin VARCHAR(3) NOT NULL ,

db_serveradmin VARCHAR(3) NOT NULL ,

db_processadmin VARCHAR(3) NOT NULL ,

db_dbcreator VARCHAR(3) NOT NULL ,

Cur_Date DATETIME NOT NULL

DEFAULT GETDATE()

)

error :Msg 2715, Level 16, State 7, Line 1

Column, parameter, or variable #1: Cannot find data type SYSNAME.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

4th time... Is your server case-sensitive?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramyours2003 SSChampion Points: 12268 More actions · Answer 12

ramyours2003

SSChampion

Points: 12268

February 24, 2014 at 7:33 am

#1691552

sorry .. its a CS

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Right, since it's case sensitive, any user data types (and that includes sysname) have to exactly match in case what they're specified in sys.types. Database names have to exactly match in case what they are specified as in sys.databases

Go check what case the type is listed as in the catalog views and use exactly that case in all of your code (and if you explicitly refer to TempDB, you'll have to check what case it is specified as in the catalog views)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramyours2003 SSChampion Points: 12268 More actions · Answer 14

@mic

i executed the script which u provided but i didnot get any output when i removed comments under

-- Remove the comments below if you want to execute this!

--EXECUTE (@mSql1)

PRINT @msql1

FETCH NEXT FROM DBName_Cursor INTO @dbname

END

i got the error declare the varaiable @mSQL1

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 15

This is likely to do with what Gail has mentioned already. Your database collation is case sensitive and as such anywhere you use the variable from the point of declaration will need to be in the same case in order for it to work.