Cretae a view in one DB with a stored procedure from another DB

Hi,

Ik created an application with visuals basic.NET. This has a
connection string to one database, let's say 'A'. In this database a
stored procedure is called which should execute a string (which is
passed by the) VB tool. This string is a CREATE VIEW statement en this
should be executed in another database let's say 'B'.

I tried this in Transact - SQL

EXEC('USE B;' + Query)

An error occurs : CREATE VIEW should be the first in a batched
statement.

Could anyone help me with this one?

Greetz,
Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0405240403.724292f@.posting.google.co m...
> Hi,
> Ik created an application with visuals basic.NET. This has a
> connection string to one database, let's say 'A'. In this database a
> stored procedure is called which should execute a string (which is
> passed by the) VB tool. This string is a CREATE VIEW statement en this
> should be executed in another database let's say 'B'.
> I tried this in Transact - SQL
> EXEC('USE B;' + Query)
> An error occurs : CREATE VIEW should be the first in a batched
> statement.
> Could anyone help me with this one?
> Greetz,
> Hennie

You need a GO right before the CREATE VIEW statement, and this won't work in
dynamic SQL. In any case, this isn't really a good way to create a database
object - it would probably be a lot easier for your client application to
connect directly to database B. Or if you really want to do it from SQL,
then why not create the stored procedure in database B and call it from
database A?

It might help if you can explain what you're trying to achieve, and someone
may be able to suggest a better solution, as creating objects dynamically
from a user application can create significant problems with security and
maintenance. But since you didn't say what your goal is, you may have a good
reason for doing this.

Simon

Creation name for ComponentMetadata

Hi,
I want to get the TaskHost.CreationName equivalent on a IDTSComponentMetaData90 object. Basically the CreationName property returns me the string used to create an instance of the DtsContainer object. (DTS.Pipeline for example). How do I get an equivalent on a data flow component. (ComponentMetadata object). There is a ObjectType property which returns a enum of type DTSObjectType but I want a string representation of the enum.

Thanks,
-SuriI figured out a work around. The CreationName is the ProgId and the ComponentClassId on the ComponentMetadata object gives me the classid. I do a little Pinvoke on ole32.dll to use ProgIDFromCLSID.

let me know if there is a better way.

Thanks|||I'm not sure what you are doing, but you may want to look at the Microsoft.SqlServer.Dts.Runtime.Application object and the PipelineComponentInfos.

Each PipelineComponentInfo has a CreationName.

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98
You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98You cannot combine CREATE VIEW with any other command in the same batch ("ex
ecution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

creating tables

I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is

Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()

Next

I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying

There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]

Can anyone see what I am doing wrong.
Character 14 (the token referred to in the error message) is the character at which your table name should be. Are you sure that your string contains a value (ie. not an empty string "")? Try putting a try catch block around your code, and output the value of your SQL in a messagebox when the error occurs, and review the SQL in question.|||I have looked at the string when it messes up, and it shows or seems to show that the string is correct. I actually typed the string in hardcoded it, and it still messes up.
|||my ques are string numbers. like 1 as a string. if I hardcode in ONE then it works.

can I not use 1,2,3 as table names.
|||

Have a look at the help for the CREATE TABLE statement, and review what constitutes a valid table name.

As per http://msdn2.microsoft.com/en-US/library/ms174979(SQL.90).aspx:

table_name

Is the name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

Try putting square brackets around your table name - ie. start the query with something like:

CREATE TABLE [1] ...

rather than

CREATE TABLE 1 ...

This lets SQL server know that you're specifying a name, rather than using a reserved identifier