How can i tell SQL to do this?
There is surely a deeper problem than this?
Main Topics
Browse All TopicsHello experts,
I am trying to script a database by using the Microsoft SQL Server Management Studio.
I right click on the database > Tasks > Generate scripts.
I then select "Script Data" to true so it scripts all of the database.
I then put that Generated SQL statement into the SQL query window and execute. Everything is fine apart from 1 field in 1 table.
It's throwing an error.
Msg 105, Level 15, State 1, Line 92
Unclosed quotation mark after the character string 'Canon
'.
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near 'Canon
'.
This field is of varchar type and stores 25 characters. I think there is a problem with the quotation mark, when i cursor left or right over the data it always gets stuck on the quotation mark and i have to press the cursor on the keyboard twice to get over it. The find and replace also cannot pick up the quotation mark.
for instance
N'Canon'
if i coped and pasted that into here it would only copy N'Canon
Does anyone know anything about this? I have never come accross this before :~
Thanks in advance.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Not exactly sure if you are dealing with creating a column name with a quotation or a value. You cannot create a column name with a quotaion mark in it, or not that I know off. But if you are inserting values here is an example if how your deal with it.
Let's say you wanted to indert value O'CONNOR
INSERT INTO TABLE VALUES ('O''CONNOR')
The above statement will inder the value with a single quotation mark between O and C. To accompalish this I type the sigle quote twice.
Hope this helps .
P.
This is an example of a record being inserted.
INSERT [dbo].[Gower_tbl_images] ([image_Id], [image_title], [image_filename], [image_dateadded], [UserId], [image_datetaken], [image_cameramake], [image_cameramodel], [image_exposuretime], [image_aperture], [image_focallength], [image_isospeed], [image_exposurebias], [image_orientation], [image_xresolution], [image_yresolution], [image_shutterspeed]) VALUES (216, N'Pwlldu Bay', N'216.jpg', CAST(0x00009BD70097E96B AS DateTime), N'c92adece-ef95-4c72-8057-
Notice that it cuts off. There should be a quotation mark after Canon
Well since you have hunder of records and you cannot use FIND/REPLACE I gues you will have to look at other avenues to insert data then. Are you copying the data to new database on the same server or different serever. If on the same server then make sure you read permissions on the source database and if diofferent server then create a linked server to the source server.
Once done, then do sometime like this:
-- If on the same server then
INSERT INTO NewDatabaseName.Owner.NewT
SELECT
-- If on a different server then
INSERT INTO Servername.NewDatabaseName
SELEC
Hopefully
P.
For the row that you used to create the sample, wat are the contents of the
image_cameramake column?
when i cursor left or right over the data it always gets stuck on the quotation mark and i have to press the cursor on the keyboard twice to get over it.
This sounds like there is a non-displayable character in that column and it is causing the INSERT script to fail. This is a data-scrubing issue that, whil;e not terribly common, is not terribly uncommon. ;-) The trick is to figure out a SQL statement that you can execute agisnt the source data that will a) remove any troublesome characters and b) not remove any needed data.
Which version of SQL Server are you using? (It makes a difference as to what options may be available. ;-)
I just did this query in SQL server
Select ASCII(SUBSTRING(N'Canon ',6,1))
Select ASCII(SUBSTRING(N'Canon ',7,1))
FROM [Gower].[dbo].[Gower_tbl_i
GO
It came back with 32 with the first select and alot of NULLs for the 2nd
So i opened the script in notepad and it is now showing a N'Canon ' instead of N'Canon'
I am not sure what you mean by EXIF but I am guessing it is a text file may be. Yes, so it could be how the data is being stored in the text file and there could be a funny character being saved. To get rid of it, we need to identify it first.
Did you tried running the sql statement above to see what ascii's you are getting?
P.
EXIF data is like information taken from the camera and put into the photo file.
This is the kind of logic i was using in my asp.net code before the information was put into the db
if (er["Equip Make"] != null)
{
strCameraMake = er["Equip Make"].ToString().Replace(
}
if (er["Equip Model"] != null)
{
strCameraModel = er["Equip Model"].ToString().Replace
}
So I was getting rid of ý But there must be something else.
Alright so you have bit of work on your hands. Here is the ASCII character may. Narrow your query down to one record where you know you are having problems. Run the above sql on the column in that record and get ascii characters for each position for the length of the column.
Map those acsii values to the map provided in the link. If you find any weired characters, that is your coulprit.
http://www.dansho
P.
Since you are using SS2008, you can use regular expressions to clean up your data.
Here is a link to some information on that:
http://www.sqlis.com/
Also, here is a regular expression that can be used to remove any characters except a-z, A-Z, 0-9, and spaces. You should be able to figure out how to add other characters to the expression to allow additional "acceptable" characters:
[^a-zA-Z0-9 ]
Oh, boy . . . okay, in SS2008 there is a menu selection that will let you Import or Export data. Let's say you choose to Import into the new SS2008 database from the old one. After selecting Import, you will be walked through the steps of selecting the source database, the target database, and all other aspects of the process for you to select what you want to import. Once you have completed all the steps, you can even save the process as an SSIS (SQL Server Integration Services) "package" that can be run later.
Are you using SS2008 Express on both sides of this process?
SSIS Packages can be crated, I think, in Visual Studio Express. They're sort of like DTS (Data Transformation Services) packages that were used with SS2000.
I have done SSIS packages in non-Express versions of SS2005/2008 and will shortly be looking into doing them in the Express editions. They laet you , in effect, program the entire data transfer/trasnformation process (including the table/index/trigger/etc. creations if you want). (The only thing I haven't done with them is create USD's and stored procs. ;-)
While the import/export functionality should address getting the data from one DB to the other, it may not address the spurious character issue. However, once the data is in the database (or before you move it) you can doa data scrub on it either within the database or, if you can do C#/VB.net coding, with a quick and dirty C#/VB.net application. One way or another, you probably will need to brush up on the data scrubbing because I would also recommend that, in the future, your importing/inserting of data either be done into a staging table or through a process that scrubs the data as it receives and inserts it.
That would be the place to do it.
Now, out of curiosity, is this a database/application you are creating yourself? (I figure these are your photos that you are working with. ;-)
The reason I ask is that I have a few thousand digital and several thousand non-digital photos that I need to catalogue somehow and I've been considering creating my own little app and DB to do it.
Yeah this is my own project. The web site is GowerUK [dot] com and i am currently in the process of migrating it to a new server (which is why the membership is not working right now).
I have tried to copy flickr and panaramio where you can upload photos and it captures specific details about the camera and its settings when the photo was taken. It is called EXIF data.
if you would like to talk to me further then send me a contact email on my web site.
Thanks!
Business Accounts
Answer for Membership
by: mallcorePosted on 2009-07-02 at 08:52:23ID: 24764688
Try 'N''Canon'' if that doesn't work escape the ' like this 'N\'Canon\''