SSMS Create Script not correct

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SSMS Create Script not correct

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-04 : 10:37:34

I have this query that I run and it runs correctly. But when I create a view with it (the view runs correctly) and then script it back out to the query grid, it adds extra quote single marks ('). The script that was scripted out will then not run until I find out what was wrong. It was confusing at first. Is there a way to avoid this. Here is my script. I have duplicated the lines in red the way they come back when being scripted out. As you can see, they are definitely changed:


SELECT [ResourceID]
      ,[ComputerName]
      ,[PrimaryUser]
      ,RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)) PrimaryUserShort
      ,[EmployeeNumber]
      ,[EMPLID]
      ,[UserFullName]
      ,[UserGivenName] FirstName
      ,[UserSurname] LastName
FROM [SoftwareReporting].[dbo].[General_Info_PM]
WHERE LEN(RIGHT(PrimaryUser,LEN(PrimaryUser)- CHARINDEX('\',PrimaryUser,1))) = 7 
AND SUBSTRING(RIGHT(PrimaryUser,LEN(PrimaryUser)- CHARINDEX('\',PrimaryUser,1)),2,1) BETWEEN '1' and '9'
AND substring(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)),1,1) BETWEEN 'A' and 'Z'
AND nullif(EMPLID,'') IS NOT NULL

-- Now, the way it comes back:

SELECT [ResourceID]
      ,[ComputerName]
      ,[PrimaryUser]
      ,RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex(''\'',PrimaryUser,1)) PrimaryUserShort
      ,[EmployeeNumber]
      ,[EMPLID]
      ,[UserFullName]
      ,[UserGivenName] FirstName
      ,[UserSurname] LastName
FROM [SoftwareReporting].[dbo].[General_Info_PM]
WHERE LEN(RIGHT(PrimaryUser,LEN(PrimaryUser)- CHARINDEX(''\'',PrimaryUser,1))) = 7 
AND SUBSTRING(RIGHT(PrimaryUser,LEN(PrimaryUser)- CHARINDEX(''\'',PrimaryUser,1)),2,1) BETWEEN ''1'' and ''9''
AND substring(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex(''\'',PrimaryUser,1)),1,1) BETWEEN ''A'' and ''Z''
AND nullif(EMPLID,'''') IS NOT NULL

This is very inconvenient at best and could be very confusing especially the first time around. I have not included the actual create view lines here. Also, I attempted to put a comment in there with the correct syntax and it even added quotes to the comment. Thank you for any help in this.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-04 : 10:47:15

Wierd. I've never seen this and I cannot repro it. What version of sql are you using? Since this is the 2008 forum I assume 2008, but are you using R2?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 11:20:44

This is one reason why you do it via Query Analyser

Madhivanan

Failing to plan is Planning to fail

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-04 : 11:31:50

10.0.2531.0 SP1 (2008) Standard Edition. I end up making sure I save the scripts in a folder because the ones that come back do not work unless I correct every line where these extra double quotes occur. (I usually save them anyway just for precaution)
Thanks for trying.

To madhivanan - I am not sure what you mean. I thought I am using Query Analyzer. Isn't that the same as SSMS?

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-04 : 12:35:59

Someone pointed out the answer for me. Go to Tools, Options, Scripting (under Object Scripting options) and if 'Include IF NOT EXISTS clause' is set to TRUE, it puts the entire code in quotes and doubles the single quotes. So just set it to FALSE. And I don't know why. Go figure.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources