Problem setting Dynamic Path - 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

General SQL Server Forums

New to SQL Server Programming

Problem setting Dynamic Path

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-13 : 03:34:38

Hi,
I need to set a Dynamic Path:

SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK ' + CHAR(39) + @path + char(39) + ' , SINGLE_BLOB
	) AS xmlData
	)

I can't seem to create a new variable (@temp = '@xmlDoc = .... ') and exec it because SQL doesn't seem to accept that.

How do I make this dynamic variable work?

Thanks for your assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 03:59:58

you need to use sp_executesql for that

see example C in below link

http://msdn.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 00:24:54

Hi Visakh,
I still can't get it to work. I have tried several ways but still it doesn't work.
This is how the code looks like now:


DECLARE @xmlDoc xml
DECLARE @sqlString nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500);

SET @sqlString = N'SET @xmlDoc = (
SELECT * FROM OPENROWSET (
	BULK @location , SINGLE_BLOB
	) AS xmlData
	)'
	
	SET @ParmDefinition = N'@location nvarchar(1000)';
	SET @path = char(39) + @path + char(39)
	EXECUTE sp_executesql @sqlString, @ParmDefinition, @location = @path;

My error message is:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@location'.

I can't see any differences between my code and example on the homepage:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

Except that I am using a FROM OPENROWSET and BULK to retrieve my data.

Can you assist me on this one?

Thanks in advance!

Best regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:49:18

[code]
DECLARE @xmlDoc xml
DECLARE @sqlString nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500);

SET @sqlString = N'SET @xmlDoc = (
SELECT * FROM OPENROWSET (
BULK '''+ @location + ''' , SINGLE_BLOB
) AS xmlData
)'

SET @ParmDefinition = N'@location nvarchar(1000),@xmlDoc xml OUTPUT';
SET @path = char(39) + @path + char(39)
EXECUTE sp_executesql @sqlString, @ParmDefinition, @location = @path,@xmlDoc=@xmlDoc OUT
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 02:28:45

No, it doesn't work. I have tried so many different ways but I have no idea why it doesn't work.

For example, the current code is

SET @sqlString = N'SET  @xmlDoc = (
SELECT * FROM OPENROWSET (
	BULK '''+ @location + ''' , SINGLE_BLOB
	) AS xmlData
	)'
	
	SET @ParmDefinition = N'@location nvarchar(1000),@xmlDoc xml OUTPUT';
	SET @path = char(39) + @path + char(39)
	EXECUTE sp_executesql @sqlString, @ParmDefinition, @location = @path,@xmlDoc=@xmlDoc OUT;
	
	PRINT 'Location: ' + @location
	PRINT 'Path: ' + @path
	PRINT 'xmlDoc: ' + CAST(@xmlDoc as varchar(4000))

And the procedure is being being created without problems but when I run it I get this error message:

 
Path: 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_10.xml'
 
Success!
 
Msg 515, Level 16, State 2, Procedure uppgift1, Line 40
Cannot insert the value NULL into column 'faktura', table 'IV132_XML.Faktura.XMLFaktura'; column does not allow nulls. INSERT fails.
The statement has been terminated.
FAIL

And as you can see the "PRINT @location" and "PRINT @xmlDoc" isn't being printed and I don't know why either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 03:06:01

i'm sure you're not giving us full picture
The error suggests the problem in with insertion into table IV132_XML.Faktura.XMLFaktura but I cant see any insert portion in posted code
Unless you give us full picture i dont think anybody will be able to help you out as we dont know what's happening at insertion end and can only keep guessing what can be the issue!!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 03:19:39

Im sorry, here is the full code visakh:

ALTER PROCEDURE uppgift1 (@path nvarchar(1000))
AS
BEGIN TRANSACTION

SET @path = N'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_10.xml'

DECLARE @xmlDoc xml
DECLARE @sqlString nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @location nvarchar(500);

SET @sqlString = N'SET  @xmlDoc = (
SELECT * FROM OPENROWSET (
	BULK '''+ @location + ''' , SINGLE_BLOB
	) AS xmlData
	)'
	
	SET @ParmDefinition = N'@location nvarchar(1000),@xmlDoc xml OUTPUT';
	SET @path = char(39) + @path + char(39)
	EXECUTE sp_executesql @sqlString, @ParmDefinition, @location = @path,@xmlDoc=@xmlDoc OUT;
	
	PRINT 'Location: ' + @location
	PRINT 'Path: ' + @path
	PRINT 'xmlDoc: ' + CAST(@xmlDoc as varchar(4000))

	PRINT 'Success!'
	PRINT @sqlString
	
	INSERT INTO Faktura.XMLFaktura(faktura)
	SELECT @xmlDoc
	
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL'
		RETURN
	END
	
	
	INSERT INTO Faktura.Faktura(faktura_id, lev_orgnr, kopare, anst_id, fakturanr, datum, totalsum,totalmoms)
	SELECT
	SCOPE_IDENTITY(), 
	@xmlDoc.value('(/FAKTURA/FÖRETAG/ORGNR)[1]', 'char(15)'),
	@xmlDoc.value('(/FAKTURA/KÖPARE/FÖRETAGSNAMN)[1]', 'char(10)'),
	(SELECT top 1 anst_id FROM HR.Anställd
		WHERE LTRIM(RTRIM(fornamn)) + ' ' + LTRIM(RTRIM(efternamn)) = @xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'CHAR(41)')),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/FAKTURA_NR)[1]', 'int'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/DATUM)[1]', 'smalldatetime'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_SUMMA)[1]', 'char(20)'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_MOMS)[1]', 'char(20)')
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL2'
		RETURN
	END

	
	COMMIT TRANSACTION
	PRINT 'Faktura importerad!'

The procedure is suppose to take a dynamic parameter (@path) which is the location of the file and read it in however I can't get the dynamic parameter to work.

Best regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 03:43:14

try like this



ALTER PROCEDURE uppgift1 (@path nvarchar(4000))
AS

BEGIN TRANSACTION


DECLARE @xmlDoc xml
DECLARE @sqlString nvarchar(4000)
DECLARE @ParmDefinition nvarchar(1000);

SET @sqlString = N'SET  @xmlDoc = (SELECT * FROM OPENROWSET (
	BULK '''+ @path + ''' , SINGLE_BLOB
	) AS xmlData)
	'
	
	SET @ParmDefinition = N'@path nvarchar(1000),@xmlDoc xml OUTPUT';
	SET @path = char(39) + @path + char(39)

	EXECUTE sp_executesql @sqlString, @ParmDefinition, @path = @path,@xmlDoc=@xmlDoc OUT;
	

	PRINT 'Path: ' + @path
	PRINT 'xmlDoc: ' + CAST(@xmlDoc as varchar(max))

	PRINT 'Success!'
	
	
	INSERT INTO Faktura.XMLFaktura(faktura)
	SELECT @xmlDoc
	
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL'
		RETURN
	END
	
	
	INSERT INTO Faktura.Faktura(faktura_id, lev_orgnr, kopare, anst_id, fakturanr, datum, totalsum,totalmoms)
	SELECT
	SCOPE_IDENTITY(), 
	@xmlDoc.value('(/FAKTURA/FÖRETAG/ORGNR)[1]', 'char(15)'),
	@xmlDoc.value('(/FAKTURA/KÖPARE/FÖRETAGSNAMN)[1]', 'char(10)'),
	(SELECT top 1 anst_id FROM HR.Anställd
		WHERE LTRIM(RTRIM(fornamn)) + ' ' + LTRIM(RTRIM(efternamn)) = @xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'CHAR(41)')),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/FAKTURA_NR)[1]', 'int'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/DATUM)[1]', 'smalldatetime'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_SUMMA)[1]', 'char(20)'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_MOMS)[1]', 'char(20)')
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL2'
		RETURN
	END

	
	COMMIT TRANSACTION
	PRINT 'Faktura importerad!'
GO

call it like
EXEC uppgift1  @path = N'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_10.xml'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 04:28:26

You are a genius, thanks for the help!

I see that you changed the @location to @path = @path. Was that the problem?

Once again, thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 04:41:10

yes... as you just used @location in forming sql string just after declare and it made the resulting string as NULL as location had no value then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources