Create view issue

Question

Post reply

Create view issue

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please run the following code and post the first 8 (eight) lines of code.

It will only print the SQL statement, not execute it. I added USE MASTER to protect your data.

USE MASTER

GO

DECLARE @BASEDB VARCHAR(50)

DECLARE @DESDB VARCHAR(50)

DECLARE @NAME varchar(50)

DECLARE @sql VARCHAR(2000)

SET @BASEDB = DB_NAME()

SET @DESDB = 'test' -- not relvant, since only the creation of the statement matters at the moment

DECLARE VIEW_LIST CURSOR

FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

OPEN VIEW_LIST

FETCH NEXT FROM VIEW_LIST INTO @NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql ='USE '+@DESDB+'

GO '

SET @sql = @sql+'

CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+'

GO'

PRINT @sql

FETCH NEXT FROM VIEW_LIST INTO @NAME

END

CLOSE VIEW_LIST

DEALLOCATE VIEW_LIST

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

HERE YOU GO..

USE test

GO

CREATE VIEW spt_fallback_db AS SELECT * FROM master.DBO.spt_fallback_db

GO

USE test

GO

CREATE VIEW spt_fallback_dev AS SELECT * FROM master.DBO.spt_fallback_dev

GO

USE test

GO

CREATE VIEW spt_fallback_usg AS SELECT * FROM master.DBO.spt_fallback_usg

GO

USE test

GO

CREATE VIEW spt_monitor AS SELECT * FROM master.DBO.spt_monitor

GO

USE test

GO

CREATE VIEW spt_values AS SELECT * FROM master.DBO.spt_values

LutzM SSC Guru Points: 107049 More actions · Answer 3

Ok,

now change the value of @DESDB to your target db (or tempdb, if you want).

Run the script again. Copy the result into a new query windo and run it. Post the error message (if there's any...).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

Actually, the issue was not to copy the script and then run it. I know it is working but my goal was to generate the view from the code and then run on different database without cut and copy the script.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Well, since we just figured that the code is actually working, change the print statement to EXEC(@SQL).

Post the error message (if there is one...).

Again, it's running just fine on my system.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 6

It gave me the error again..

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 3

'CREATE VIEW' must be the first statement in a query batch.

WayneS SSC Guru Points: 95460 More actions · Answer 7

balbirsinghsodhi (3/3/2010)
It gave me the error again..
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 3
'CREATE VIEW' must be the first statement in a query batch.

Try replacing all occurrances of "GO" with "GO;" (add the semi-colon), and see if that helps.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

balbirsinghsodhi (3/3/2010)
I having issue using script which create views in the batch. I am generating a views from the database based on all the tables and store those views on different database. I am using following code to generate views. It works when I run the script but when I use EXEC command inside the code I get error.. "Create view must be the first statement in the query batch"

Ty this

DECLARE @BASEDB VARCHAR(50)

DECLARE @DESDB VARCHAR(50)

DECLARE @NAME varchar(50)

DECLARE @sql VARCHAR(2000)

SET @BASEDB = DB_NAME()

SET @DESDB = 'Foundation_SMF'

DECLARE VIEW_LIST CURSOR

FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

OPEN VIEW_LIST

FETCH NEXT FROM VIEW_LIST INTO @NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql ='EXEC '+@DESDB+'..sp_executesql '''

SET @sql = @sql+'CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+''''

EXEC(@SQL)

--PRINT @sql

FETCH NEXT FROM VIEW_LIST INTO @NAME

END

CLOSE VIEW_LIST

DEALLOCATE VIEW_LIST

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);