Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Read text file data to table (Table name provided dynamically)

Posted on 2010-01-12
12
Medium Priority
?
720 Views
Last Modified: 2012-05-08
Hi
I am using the following code to copy content of text file to a table and it is working fine in which the table name is static.
but i tried passing table name dynamically..it's not working.

I am posting the code with static table name which is working fine and one with dynamic table name in which table name is passed as a parameter but not working.





***Working Code with static table name***

ALTER PROCEDURE [dbo].[ReadFromTextFile]
@FileName VARCHAR (1024)

AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
PRINT @Message
INSERT INTO dbo.xxxx_yyyy (Output) Values (@Message)
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS



***Code Not working with dynamic table name:***

ALTER PROCEDURE [dbo].[Read_Pump_Stats]

@FileName VARCHAR (1024),
@TableName VARCHAR(100)

AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)
DECLARE @SQL NVARCHAR(max)

 

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
PRINT @Message
SET @SQL=N'INSERT INTO '+@TableName+'(Data) VALUES ('+@Message+')'
EXEC sp_executesql @SQL
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Open in new window

0
Comment
Question by:parpaa
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26297437
it work fine for me ...
make sure that you pass the arguments in the proper order
0
 

Author Comment

by:parpaa
ID: 26298341
Following is the error message i am getting for each line...I didn't get why it is causing...
Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GoldenGate Command Interpreter for ODBC

Msg 128, Level 15, State 1, Line 1

The name "GoldenGate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Version 10.0.0.22 Build 001

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '10.0'.

Windows x64 (optimized), Microsoft SQL Server on May 8 2009 11:28:00

Msg 128, Level 15, State 1, Line 1

The name "Windows" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

Copyright GoldenGate Software, Inc. 1995-2009.

Msg 128, Level 15, State 1, Line 1

The name "Copyright" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

This software includes code written by third parties, including

Msg 128, Level 15, State 1, Line 1

The name "This" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Pthreads-win32(Copyright (C) 2001, 2006 Ross P. Johnson)

Msg 128, Level 15, State 1, Line 1

The name "Pthreads" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Blowfish encryption library (Copyright (C) 1997 by Paul Kocher)

Msg 128, Level 15, State 1, Line 1

The name "Blowfish" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

and other code as specified at http://support.goldengate.com. Additional

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'and'.

details regarding such third party code, including applicable copyright,

Msg 128, Level 15, State 1, Line 1

The name "details" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

legal and licensing notices, are available at the above referenced URL.

Msg 128, Level 15, State 1, Line 1

The name "legal" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GGSCI (<instance name>) 1>

Msg 128, Level 15, State 1, Line 1

The name "<Instance Name>" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Sending GETTCPSTATS request to EXTRACT PUMP2 ...

Msg 128, Level 15, State 1, Line 1

The name "Sending" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 128, Level 15, State 1, Line 1

The name "RMTTRAIL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

OK

Msg 128, Level 15, State 1, Line 1

The name "OK" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Session Index 0

Msg 1018, Level 15, State 1, Line 1

Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Stats started 2009/11/02 16:58:59.196000 20:01:02.004000

Msg 128, Level 15, State 1, Line 1

The name "Stats" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 128, Level 15, State 1, Line 1

The name "Local" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Inbound Msgs 4542393 Bytes 61036633, 9 bytes/second

Msg 128, Level 15, State 1, Line 1

The name "Inbound" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Outbound Msgs 4542394 Bytes 30665380022, 5010 bytes/second

Msg 128, Level 15, State 1, Line 1

The name "Outbound" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Recvs 9084786

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '9084786'.

Sends 4542394

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '4542394'.

Avg bytes per recv 6, per msg 13

Msg 128, Level 15, State 1, Line 1

The name "Avg" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Avg bytes per send 6750, per msg 6750

Msg 128, Level 15, State 1, Line 1

The name "Avg" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Recv Wait Time 55230593000, per msg 12158, per recv 6079

Msg 128, Level 15, State 1, Line 1

The name "Recv" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Send Wait Time 44968000, per msg 9, per send 9

Msg 128, Level 15, State 1, Line 1

The name "Send" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GGSCI (<Instance name>) 2>

Msg 128, Level 15, State 1, Line 1

The name "Instance Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Open in new window

0
 
LVL 15

Expert Comment

by:MohammedU
ID: 26298500
I don't see any issue with the code...
But you can also use openrowset command...

declare @v varchar(500)
declare @s varchar(500)
declare @t varchar(500)
select @v ='select  * from '  + 'TestTextFileImport.txt'
select @v

select @s ='insert into '+@t+'
 select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;'', ''' +@v +' '')'

print @s
exec (@s)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:parpaa
ID: 26305390
I got some thing to do with this line...
Could anyone let me know how to pass the @Message as string variable..
I think from the following statement it is taking @Message as scalar variable.
How can i format the following query to pass @Message with in quotes.

SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES ('+@Message+')'

Thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26305648
Do you mean:
SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES (' + REPLACE(@Message, '''', '''''''') + ')'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26305673
Let's try that again:
SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES (' + REPLACE(@Message, '''', '''''') + ')'
0
 

Accepted Solution

by:
parpaa earned 0 total points
ID: 26306393
I found solution for this...

SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES ('+QuoteName(@Message,'''')+')'

Thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26306835
QUOTENAME() will add single quotes around the text, since you already have them it is not necessary to add them again.  See for yourself:

DECLARE @Message varchar(100)

SET @Message = 'asadsadsa''dadad'
PRINT @Message
PRINT REPLACE(@Message, '''', '''''')
PRINT QUOTENAME(@Message,'''')

OUTPUT:
asadsadsa'dadad
asadsadsa''dadad
'asadsadsa''dadad'
0
 

Author Comment

by:parpaa
ID: 26306924
It adds quotes so that the command is taking it as String ...
It is not inserting any quotes into the table to which i  am loding because it takes the @Message as '@Message' i.e, exactly the way we pass the string variable.

FYI i am getting the data what i am  looking for into the table.

Thanks acperkins.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26310569
>>It is not inserting any quotes into the table to which i  am loding<<
Sure it is.  Look at the example I posted.  If the text has a single quote it will double them.

But whatever works for you...
0
 

Author Comment

by:parpaa
ID: 26315815
It is becoz you had set the variable with in quotes during intialization and finally you are printing it using QOTENAME ...So it is keeping additional quotes...
In my case i am passing input from text file which doesn't have any kind of quotes.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question