Arthur Fuller
fuller.artful at gmail.com
Sat Jun 2 09:00:10 CDT 2007
As far as I know, you cannot get from here to there, at least not in the way you are trying. As the error message says, you cannot use the USE [database] statement in a sproc, even if you hard coded it. However, there may be another approach. It depends on where you are calling your sproc from. You didn't really indicate that. If it's from a query window and you are calling the sproc yourself, then there is a way around the problem, but if you're trying to do this from code, then you may have problems. First thing I would suggest is that you place the stored procedure in the master database, so that it's available all the time. From the query window, issue the USE statement yourself, followed by the call to the procedure (which will now run in the used database, even though it lives in the master database). Note that you may have to run the procedure using its fully qualified name, that is: exec master..dbo.sprocname. hth, Arthur P.S. I would also add this create script to VSS or your preferred version control program. On the off chance that you have to reinstall SQL, you'd overwrite master, so you'd want to get the sproc back out of VSS to reinstall it. On 6/2/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > > I am trying to build a table in a database "on demand". I have a sql > statement (see immediately below) that I got from the "script to query > window wizard". It works just fine and I have used it several times (to > test it) just changing the "USE database" statement. > > USE [MyDatabaseName] > GO > /****** Object: Table [dbo].[tblDataMapCSVOut] Script Date: 06/02/2007 > 04:01:30 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_PADDING ON > GO > CREATE TABLE [dbo].[tblDataMapCSVOut]( > [CSVID] [int] IDENTITY(1,1) NOT NULL, > [CSVFldNameInTable] [varchar](50) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL, > [CSVFldNameInFile] [varchar](50) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > > GO > SET ANSI_PADDING OFFEND > GO > > Works fine. Now I want to put that in a stored procedure so that I can > just > call the sp, pass in the name of the database to create the table in and > go. > I created a stored procedure as below: > > -- ================================================ > -- Template generated from Template Explorer using: > -- Create Procedure (New Menu).SQL > -- > -- Use the Specify Values for Template Parameters > -- command (Ctrl-Shift-M) to fill in the parameter > -- values below. > -- > -- This block of comments will not be included in > -- the definition of the procedure. > -- ================================================ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > -- ============================================= > -- Author: <Author,,Name> > -- Create date: <Create Date,,> > -- Description: <Description,,> > -- ============================================= > CREATE PROCEDURE sp_CreateDataMapCSVOut > -- Add the parameters for the stored procedure here > @DatabaseName varchar(255) > AS > BEGIN > -- SET NOCOUNT ON added to prevent extra result sets from > -- interfering with SELECT statements. > SET NOCOUNT ON; > > -- Insert statements for procedure here > USE '[' + @DatabaseName + ']' > GO > /****** Object: Table [dbo].[tblDataMapCSVOut] Script Date: 06/02/2007 > 04:01:30 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_PADDING ON > GO > CREATE TABLE [dbo].[tblDataMapCSVOut]( > [CSVID] [int] IDENTITY(1,1) NOT NULL, > [CSVFldNameInTable] [varchar](50) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL, > [CSVFldNameInFile] [varchar](50) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > > GO > SET ANSI_PADDING OFFEND > GO > > > Notice that I am trying to pass in the name of the database and use that > parameter to determine where the table is created. This does not work > fine, > giving me the following errors when I try to run it. > > Msg 154, Level 15, State 1, Procedure sp_CreateDataMapCSVOut, Line 16 > a USE database statement is not allowed in a procedure, function or > trigger. > Msg 102, Level 15, State 4, Line 1 > Incorrect syntax near 'ANSI_PADDING'. > > Is there a way to do this? Can someone "fix me up" here so that this > works? > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >