Stored Procedure in SQL 2008

Question

I have create a stored procedure with VB.NET

This procedure goes to a remote Server (for the moment on my near server Win 2003) until now everything are ok

The procedure is created and i call her from my program

The name is sp_AddStreamDB

mycommand = new sqlcommand("EXEC sp_AddStreamDB 'C:\sqlDATA\', 'RemoteDB'",RemoteSQLConn)
myCommand.ExecuteNonQuery()

Normally the procedure will take the two parameters and she put them in there own right places to executed, but that is not Happen

CREATE PROCEDURE [dbo].[sp_AddStreamDB] (
     -- Add the parameters for the stored procedure here
     @DPath varchar(max)
    ,@DBName varchar(50)
    ,@Qchar varchar(1) = "'"
    )
AS 
    BEGIN_TRY:
    SET QUOTED_IDENTIFIER ON ;
    SET NOCOUNT ON ;
    -- Insert statements for procedure here
    DECLARE @ErrMsg nvarchar(4000)
       ,@DBName1 varchar(50)
       ,@DBName2 varchar(50)
       ,@DBNamefs varchar(50)
       ,@DBNamelog varchar(50)
       ,@FileGroupN varchar(100)
       ,@DATName varchar(max)
       ,@LOGName varchar(max)
       ,@FSName varchar(max)

    SET @DBName1 = ( @DBName + '1' )
    SET @DBName2 = ( @DBName + '2' )
    SET @DBNamefs = ( @DBName + 'fs' )
    SET @DBNamelog = ( @DBName + 'log' )
    SET @FileGroupN = ( @DBname + 'StreamGroup' )
    SET @DATName = ( @Qchar + @DPath + @DBName + '_dat.mdf' + @Qchar )
    SET @LOGName = ( @Qchar + @DPath + @DBName + '_log.ldf' + @Qchar )
    SET @FSName = ( @Qchar + @DPath + @DBName + '_fs' + @Qchar )

    BEGIN_CATCH:
    SELECT  ERROR_MESSAGE() AS ErrorMessage ;
    SELECT  @ErrMsg = ERROR_MESSAGE()

    CREATE DATABASE DBName
    ON PRIMARY 
       (
         NAME = DBName1
        ,FILENAME = DATName
       ),
    FILEGROUP FileGroupN CONTAINS FILESTREAM
      (
        NAME = DBNamefs
        ,FILENAME = FSName
       )
    LOG ON
       (
        NAME = DBNamelog
        ,FILENAME = LOGName
        )

    RAISERROR (@ErrMsg,1,1)
    RETURN 0
    END_CATCH:
    END_TRY:

=====================

CREATE DATABASE is the important issue

This procedure gives me an error "The File Name 'DATName' is not in the right format ....."

The varable @DATName is 'C:\sql\DATA\RemoteDB.dat.mdf' ( i have check this by PRINT @DATName and it is OK

But for some reason the line CREATE DATABASE refused to take the value of the variable and takes just the name (only the name) DATName which tries to excecute the name as value and of course that returns me the error

NOW

IS THERE ANYBODY WHICH CAN ASSIST ME ON THIS ISSUE?

After some troubleshooting tips i've change my code to this:

PRINT @DATName
CREATE DATABASE [@DBName]
ON PRIMARY
   (
     NAME = '@DBName1'
    ,FILENAME = '@DATName'
   ),
FILEGROUP [@FileGroupN] CONTAINS FILESTREAM
  (
    NAME = '@DBNamefs'
   ,FILENAME = '@FSName'
  )
LOG ON 
  (
   NAME = '@DBNamelog'
   ,FILENAME = '@LOGName'
   )

and i'm taking the error: A file activation error occurred. The physical file name '@DATName' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Of course the @DATName is that: 'C:\sqlDATA\RemoteDB_dat.mdf'

The same i take it when i remove the brackets And the same i take it when i remove the single quotes

Lefteris Gkinis [email protected] 0030 22960 32843

Answer 1

CREATE DATABASE DBName ON PRIMARY (NAME = DBName1, FILENAME = DATName), FILEGROUP FileGroupN CONTAINS FILESTREAM (NAME = DBNamefs, FILENAME = FSName) LOG ON (NAME = DBNamelog, FILENAME = LOGName)

may be you left the @ in the variables of that command¿

CREATE DATABASE @DBName ON PRIMARY (NAME = @DBName1, FILENAME = @DATName), FILEGROUP @FileGroupN CONTAINS FI

EDIT:

Another solution, more complicated but I'm sure it works (I have used it many times):

• Dinamicaly create your create database command in VB (all text, without parameters)
• Run a command from VB to create a new SP in master database with your new fixed parameter in it.
• Run the new master SP from VB (the database will be created)
• Run a command from VB to delete the master SP

It is not as smart as your initial solution, but it works.

Answer 2

I'm fairly certain that you can't pass variables to the CREATE DATABASE statement. You'll need to construct an entire CREATE DATABASE within a varchar variable and then pass that to sp_executesql.

Answer 3

Dear j.ja.estevan

After many retries with various combinations now i'm turned to your place i see that this command (CREATE DATABASE) can't works with variable from insitde of a procedure

Because from vb.net works just fine

And if there is a such of problem now i'm coming to the reason of why i use stored procedure

If i use the CREATE DATABASE from inside the code then he can't understood my path C:\sqlDATA\RemoteDB_dat.mdf

I mean he things that i'm trying to created in my own PC (or what else)

Because it turns back the message Access Deny when i'm tring to write the file RemoteDB_dat.mdf

And i'm interpriter this message that he (the sql server) to run the command from iside of his machine

So When i put my real path and names to the stored procedure then he bypass all the previous problems and gives me the last error (of Access deny)

So we are in same place where we have start it

!@#$%^& any Help? !@#$%^&*

Answer 4

This works over here

CREATE PROCEDURE [dbo].[sp_AddStreamDB] (
     @Path nvarchar(500)
    ,@DBName nvarchar(120)
    )
AS 
    SET NOCOUNT ON
    BEGIN TRY

        DECLARE @File1Logical nvarchar(120)
           ,@File1Physical nvarchar(500)
           ,@StreamGroup nvarchar(120)
           ,@StreamLogical nvarchar(120)
           ,@StreamPhysical nvarchar(500)
           ,@LogLogical nvarchar(120)
           ,@LogPhysical nvarchar(500)
           ,@exe nvarchar(4000)
           ,@ErrMsg nvarchar(2048)


        SET @File1Logical = @DBName + N'1'
        SET @File1Physical = @Path + +@DBName + N'_dat.mdf'
        SET @LogLogical = @DBName + N'log'
        SET @LogPhysical = @Path + @DBName + N'_log.ldf'
        SET @StreamGroup = @DBName + N'fs'
        SET @StreamLogical = @DBname + N'StreamGroup'
        SET @StreamPhysical = @Path + @DBName + N'_fs'

        SET @exe = N'
        CREATE DATABASE <db_name_here>
        ON PRIMARY 
            (
              NAME = <file1_logical_here>
             ,FILENAME = ''<file1_physical_here>''
             ),
        FILEGROUP <streamgroup_here> CONTAINS FILESTREAM
            (
              NAME = <stream_logical_here>
             ,FILENAME = ''<stream_physical_here>''
             )
        LOG ON 
             (
               NAME = <log_logical_here>
              ,FILENAME = ''<log_physical_here>''
              )'

        SET @exe = REPLACE(@exe, N'<db_name_here>', @DBName)
        SET @exe = REPLACE(@exe, N'<file1_logical_here>', @File1Logical)
        SET @exe = REPLACE(@exe, N'<file1_physical_here>', @File1Physical)
        SET @exe = REPLACE(@exe, N'<log_logical_here>', @LogLogical)
        SET @exe = REPLACE(@exe, N'<log_physical_here>', @LogPhysical)
        SET @exe = REPLACE(@exe, N'<streamgroup_here>', @StreamGroup)
        SET @exe = REPLACE(@exe, N'<stream_logical_here>', @StreamLogical)
        SET @exe = REPLACE(@exe, N'<stream_physical_here>', @StreamPhysical)

        EXEC sp_executesql @exe
    END TRY
    BEGIN CATCH
        SET @ErrMsg = ERROR_MESSAGE()
        RAISERROR (@ErrMsg,1,1)
        RETURN 0
    END CATCH
    RETURN 1

To test:

DECLARE @RC int

EXECUTE @RC = [dbo].[sp_AddStreamDB]  @Path =N'c:\db\',@DBName =N'SomeDB'
SELECT @RC

The c:\db\ directory must exists and sql service account must have read/write permissions on it.