CREATE VIEW in dynamic SQL
-
This is a weird one.
I have a sproc that executes a CREATE VIEW statement in a dynamic SQL. The trouble is that my sproc sits in another database to where I want the view and I cannot say something like CREATE VIEW DatabaseName..ViewName ....
So I tried putting a 'USE DatabaseName' into the dynamic SQL. This time it blew with:
"'CREATE VIEW' must be the first statement in a query batch."
So I tried putting a GO into my dynamic SQL and of course this time it blew cos GO isn't a SQL keyword, its only recognised by isql/osql/Query Analyser.
Is there any way of getting around this without putting the sproc into the same database in which I want to create the view?
Thanks
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
can you use openrowset?
openrowset (correctserver, dbname, 'Create view...')
-
Great suggestion Remi,
I'll give it a go and report back!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
K.. I'll be waiting
. -
Nice idea, but using this statement:
select *
from openrowset('SQLOLEDB','SERVER=dwsqldevinc4;database=dwMETADATA;UID=***;PWD=***', 'create view myview as select * from loadprocess') a
I got this error:
Could not process object 'create view myview as select * from loadprocess'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=create view myview as select * from loadprocess'].
Which makes sense I suppose. CREATE VIEW doesn't return anything!
I'm resigning myself to having to put the sproc into the same DB. For reasons I won't go into I'd prefer not to do this but it looks as tho I have no choice
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Create view;Select null
-
Nope!
select *
from openrowset('SQLOLEDB','SERVER=dwsqldevinc4;database=dwMETADATA;UID=***;PWD=***', 'create view myview as select * from loadprocess;select null') a
gives error:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
Any more ideas?
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Not ATM.
-
This just in from my SQL bible...
"Some activities cannot be executed via a linked server arrangement - these include:
DDL Statements(CREATE, DROP, ALTER) or anything that implies any one of these (SELECT INTO, for example). You can, in most instances, get away with this using OPENROWSET (which just does a pass through on whatever code you supply it, so, as long as it's valid at the other end, it should work), but I advise you against doing this since the error handling is so poor"....SQLServer 2000 Programming
Jamie - is your goal just to select certain columns from one/many tables on a remote server ?! I'm not clear on why you want to create the view - can't you just do a "select *..." ??
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila,
Not that its important but...
The views I want to create are provided for reporting purposes and the database I'm trying to create them in is our reporting database. The views are (or will be) created on a nightly basis and are based on metadata maintained by the users. The users can change that metadata whenever they want hence we need to recreate the views nightly
The metadata is basically a view name and the list of elements to appear in that view. The elements are data values and the requirement includes pivoting the elements to have them as columns (see here). Hence this is some pretty nasty dynamic SQL to build my CREATE VIEW statement
.I dno't want to put the sproc into my reporting DB simply because we have project standards which include all code going into 1 database and all reporting objects into another database etc...
There is no actual requirement to create them on a remote server. Remi was just suggesting a way that I could execute a CREATE VIEW statement to create a view in a different DB to the one in which my sproc resides. It seems it can't be done
Hope this makes sense.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
the only advice my book has to offer is to "play" with OPENQUERY AND OPENROWSET...
My suggestion would be to shoot an email/pm to noeld - there is NOTHING that he does not know and when all other sources turn up naught I've known him to come up with a solution!
**ASCII stupid question, get a stupid ANSI !!!** -
Based on what you yourself said before Sushila I'm pretty sure anythnig using OLEDB linked servers isn't going to work.
I'm currently exploring ways of doing it using the sp_OA* sprocs!
-Jamie
UPDATE: When I say I was looking at it I basically meant a colleague, Paul Mcmillan, was looking at it. He has solved this using sp_OA* calls. As I write he is drafting a reply which should appear below, soon!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Why not output your DDL to a .SQL file on the other server and run it on the other server using OSQL?
Andy
-
Hi David/Andy/whatever
,That's definately an option although its less preferable than putting the sproc into the target database. We prefer to keep everything "within" if you know what I mean.
Note that there is no "other" server involved. Everything is getting run on the 1 server.
Thanks for the suggestion.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Jamie
You can do this via the Ole Automation sp_OA's. You need to write a stored procedure that uses the ExecuteImmediate Method on a specific named database. First make a connection to SQL Server (via Integrated Security) then obtain the collection of database objects and then set the object property of the database you want to run the create view statement in as part of the call to the ExecuteImmediate method. Then ExecuteImmediate the DDL
Note:
Ensure that there are at least 2 parameters to the sp: 1 for the database you want to use, the second for the DDLStatement
Ensure you destroy all objects to avoid memory leaks
Note: If you are really that way inclined you can get the sp to use an NTEXT parameter and thus create DDL > 8000 chars. But you have to call the SP itself via Dynamic SQL and the EXEC () methods
This is how I have done this before to solve your exact requirement on another project, and I have passed on some code to you, to show you how to do this..
Paul
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply