| Author |
Topic |
|
ronin2307
Posting Yak Master
126 Posts |
 Posted - 2006-11-07 : 16:14:35
|
'CREATE/ALTER FUNCTION' does not allow specifying the database name as a prefix to the object name.this is the message when I try this:CREATE FUNCTION [tempdb].[dbo].[fx_GetCompGUID] ... where tempdb is the database. Now, I have read that to get rid of this I only have to say USE tempdb and then create the function without referencing the db, but I am trying to do this from VB and this doesn't work, because by the time the create part is executed I am not using tempdb any more and everything is going into master db.sql = "USE [testdb]"ExecuteSQLStmt(sql)sql = "SET ANSI_NULLS ON"ExecuteSQLStmt(sql)sql = "SET QUOTED_IDENTIFIER ON"ExecuteSQLStmt(sql)sql = "CREATE FUNCTION [dbo].[fx_GetTaskGUID].... How do I get around this so that VB creates the function in the right db?Thanxbtw: using something like this does not work:sql="USE [testdb] GO SET ANSI_NULLS ON GO"ExecuteSQLStmt(sql) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-11-07 : 16:31:47
|
| Why are you are using VB code to create a user defined function in sql server? Sounds like a bad design...Be One with the OptimizerTG |
 |
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 16:35:20
|
| well, I am using VB to create the whole DB. Reason being is because it is rather time consuming and not straight forward thing to do, especially for somebody who has no clue about SQL.I need the DB created for an app I have written, so I am trying to automate the DB creation process |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 16:35:51
|
| If there are other ways, I am more than open to suggestions |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-11-07 : 16:50:18
|
| So users of your application will need this database created to support your app? Since you have written the applicaiton already I assume you have an existing database? How about if you use enterprise manager to generate a create script for your existing database? The incorporate that script as part of your "setup".Be One with the OptimizerTG |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 16:57:49
|
| yes in short:I created a small db that will support the app I have created as well. It's a scaled down, customized preventative maintenance app, which stores all related data in the db.Like I said, this is the first time I have done anything like this and I was trying to hide all the "complicated stuff" from the person installing the app and the db (it wont be me). That's why i am open to suggestions:However I know about the script wizard in sql 2005 express version so I can certainly do that. Didn't even think about it, but it certainly makes a whole lot more sense |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 17:00:36
|
| question: the script would need to be executed in a query analyzer or something like that. IS there any way to execute that script from an external app like VB? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-07 : 17:04:50
|
| I agree entirely with TG, but for future reference, in VB, just create a new database connection and specify a different database in your connection string.In fact if you execute a USE statement on a connection and then reuse that same connection you will get the database that you USEd, but it looks like you have created a method to execute statements and that's creating a new connection each time, which is why the USE isn't effective. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-07 : 17:07:09
|
quote:
Originally posted by ronin2307
question: the script would need to be executed in a query analyzer or something like that. IS there any way to execute that script from an external app like VB?
Just read the entire script into a string and then execute it as you would a single statement. Note though that it must not have any GO statements in it because they are not SQL statements. You could (and it would be a good idea to do this) parse the GO statements and execute the whole script broken at each GO. |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 17:11:31
|
quote:
Originally posted by snSQL
I agree entirely with TG, but for future reference, in VB, just create a new database connection and specify a different database in your connection string.
not sure what you mean by that and the reason...? |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 17:16:32
|
| btw, the statement about the "execute a USE statement" is entirely correct and it is my mistake that I didn't pay close attention to the method. It was just copied from MSDN and it worked, so I never looked back. It certainly explains why it wasn't working. |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-07 : 17:27:57
|
quote:
Originally posted by snSQL
quote:
Originally posted by ronin2307
question: the script would need to be executed in a query analyzer or something like that. IS there any way to execute that script from an external app like VB?
Just read the entire script into a string and then execute it as you would a single statement. Note though that it must not have any GO statements in it because they are not SQL statements. You could (and it would be a good idea to do this) parse the GO statements and execute the whole script broken at each GO.
when it comes down to it that is exactly what I was doing. I was just reseting that connection and that's why I was having problems.SO I am not sure how I this is a bad design???? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-07 : 18:59:15
|
| TG meant that it is a bad design to be creating database objects on the fly in your application, but if you are just executing the create database script in the application to create the database that would be OK - just don't start adding code that modifies the database structure based on user input - that's a bad idea. |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-08 : 09:11:12
|
| understood. But if you see my original post it is clear that I am executing script, so that's why I was confused about the "bad" design |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 10:59:29
|
| Your original post doesn't say anything about executing a script, it just says you want to create a User Defined Function from VB code. AND in the example you use the tempdb database which suggests that you are creating these UDFs in tempdb which means they aren't in your database and get dropped automatically (that's what temp does). So all in all, it did indeed look like a very bad design. |
|
|
|
ronin2307
Posting Yak Master
126 Posts |
Posted - 2006-11-08 : 11:03:30
|
| true it doesn't, but I thought it was obvious that I did because of the syntax that is visible there. However tempdb was my poor choice of words because I didn't want to write the true name of the db. SO with all this confusion out of the way, thanx for all your help |
|
|
|
|
CREATE/ALTER FUNCTION' does not allow specifying t