Stored Procedure in SQL 2088 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure in SQL 2088

Author

Topic

Lefteris
Starting Member

3 Posts

Posted - 2009-12-19 : 11:27:34

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
her 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 put them in there right place 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 importand 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
pginis@principalit.gr
0030 22960 32843

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources