Urgent: Handling [& , ' etc] in TSQL

Question

Urgent: Handling [& , ' etc] in TSQL

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would it be an option to get the file name list and rename it using xp_cmdshell?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sridoc2020 SSC Enthusiast Points: 117 More actions · Answer 2

No. Because files names already exists in the database, I guess they were inserted by Front end Application. Basically it holds names and paths to the files on the disk.

Now we decided to load into the database. Hence File names I can't change. It should match in the database.

LutzM SSC Guru Points: 107049 More actions · Answer 3

I thought about renaming or copy the files (with a more useful file name) just for the load process...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sridoc2020 SSC Enthusiast Points: 117 More actions · Answer 4

Do you know how I can force to accept following name in above query which contains comma

18765_McHaney,Packet,DI,4.2.10_1

LutzM SSC Guru Points: 107049 More actions · Answer 5

Sridoc2020 (7/24/2011)
Do you know how I can force to accept following name in above query which contains comma
18765_McHaney,Packet,DI,4.2.10_1

No. Hence my recommendation to rename the files before the import.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Sridoc2020 (7/24/2011)
Do you know how I can force to accept following name in above query which contains comma
18765_McHaney,Packet,DI,4.2.10_1

At the risk of offending you, you have to start trying what I told you. The following example works just fine...

SELECT '07/14/2011 11:20 AM 31,232 The, Shadow.jpg',

'The, Shadow.jpg',

'Jul 14 2011 12:00AM',

*

FROM OPENROWSET(BULK 'C:\Documents and Settings\Admin\My Documents\My Pictures\The, Shadow.jpg' , SINGLE_BLOB) AS imagesource

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sridoc2020 SSC Enthusiast Points: 117 More actions · Answer 7

Jeff

Yes they are working fine if you run following individually as you said. But When I am executing using EXEC ( @SQLstr) I am facing problem.

INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)

SELECT '07/14/2011 10:56 AM 559,790 11838_send,Ortho.App.01.20.10&2.17.10_1.pdf',

'11838_SeanTownsend,Ortho.App.01.20.10&2.17.10_1.pdf',

'Jul 14 2011 12:00AM',

*

FROM OPENROWSET(BULK 'D:\DOCS\DOCS2010\11838_send,Ortho.App.01.20.10&2.17.10_1.pdf' , SINGLE_BLOB) AS imagesource

output

--------------------------------------------------------------------------------------------------------------------

The system cannot find the file specified.

'2.17.10_1.pdf' is not recognized as an internal or external command,operable program or batch file.

same above If I run from query window from SSMS, it just runs fine

INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)

SELECT '07/14/2011 10:56 AM 559,790 11838_send,Ortho.App.01.20.10&2.17.10_1.pdf',

'11838_SeanTownsend,Ortho.App.01.20.10&2.17.10_1.pdf',

'Jul 14 2011 12:00AM',

*

FROM OPENROWSET(BULK 'D:\DOCS\DOCS2010\11838_send,Ortho.App.01.20.10&2.17.10_1.pdf' , SINGLE_BLOB) AS imagesource

Another Error, which says incorrect syntax, but runs fine from query window

INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)

SELECT '07/14/2011 10:56 AM 103,495 12276_Fruge,Patricia1.06.10_1.pdf',

'12276_Fruge,Patricia1.06.10_1.pdf',

'Jul 14 2011 12:00AM',

*

FROM OPENROWSET(BULK 'D:\DOCS\DOCS2010\12276_Fruge,Patricia1.06.10_1.pdf' , SINGLE_BLOB) AS imagesource

output

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The syntax of the command is incorrect.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

That would be a problem with your dynamic SQL. How are you doing the EXEC???

If you're using EXEC @somevariable, that's the wrong way to do it. You need to use EXEC (@somevariable).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sridoc2020 SSC Enthusiast Points: 117 More actions · Answer 9

Sridoc2020

SSC Enthusiast

Points: 117

July 26, 2011 at 4:06 pm

#1358880

EXEC ( @SQLstr)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

I guess I'm out of tricks, then. I can't troubleshoot any further from here. My apologies.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 11

What might help is a list of 3 or 4 INSERT statements for the #CmdShell table that will show the file names you're trying to import.

We could then create files with the identical names and try to replicate your issue.

I tested your original code with

INSERT INTO #CmdShell

SELECT '12276_Fruge&Patricia1.06,10_1.pdf'

and I had no issues to bulk load that file.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

Try double &&

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]