Answer

Stored Procedure in SQL 2008

Lefteris Gkinis

15y

2.7k

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?

Lefteris Gkinis
[email protected]
0030 22960 32843