SQL server problems scripting database

Hello 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.
thomasmuttonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mallcoreCommented:
Try 'N''Canon'' if that doesn't work escape the ' like this 'N\'Canon\''
0
thomasmuttonAuthor Commented:
How can i tell SQL to do this?

There is surely a deeper problem than this?
0
pssandhuCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mallcoreCommented:
Edit the generated query.
0
thomasmuttonAuthor Commented:
mallcore yes but there are hundreds of records.

pssandhu: sorry i forgot to say that it is an INSERT statement. This quotation mark is not an ordinary quotation mark. It cannot be found by the find function in SQL and also cannot be copied..
0
thomasmuttonAuthor Commented:
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-8185dc9a0983', CAST(0x00009BD300E6922C AS DateTime), N'Canon

Notice that it cuts off. There should be a quotation mark after Canon
0
pssandhuCommented:
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.NewTable
SELECT  * FROM SourceDatabaseName,Owner.SourceTable
-- If on a different server then
INSERT INTO Servername.NewDatabaseName.Owner.NewTable
SELECT  * FROM Servername.SourceDatabaseName,Owner.SourceTable
Hopefully this should fix your problem.
P.
0
8080_DiverCommented:
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. ;-)
0
thomasmuttonAuthor Commented:
Hello 8080 Diver:

Canon EOS 400D DIGITAL
This is an example of what is in image_cameramake. This is a varchar field aswell and there is no problem with this field.

I am using SQL Server 2008
0
pssandhuCommented:
Copy paste the value from the column in the query below and see what ASCII numbers you end up with.
Select ASCII(SUBSTRING('<column value here>',6,1))

Select ASCII(SUBSTRING('<column value here>',7,1))
P.
0
thomasmuttonAuthor Commented:
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_images]
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'
0
thomasmuttonAuthor Commented:
I just opened the script in Textpad and tried to copy the field in question and it said.

Cannot cut, copy drag or drop text containing null (code = 0) characters.
0
8080_DiverCommented:
thomasmutton,
This is a varchar field aswell and there is no problem with this field.
Well, that's not exactly true because it isn't scripting out very well.
 
If your source column name is image_cameramake, then you should have used the SQL Statments shown below instead of what you ran:

Select ASCII(SUBSTRING(image_cameramake,6,1)) 
FROM [Gower].[dbo].[Gower_tbl_images];
 
Select ASCII(SUBSTRING(image_cameramake,7,1)) 
FROM [Gower].[dbo].[Gower_tbl_images];

Open in new window

0
thomasmuttonAuthor Commented:
One thing to mention, the information stored in this database table is when i upload photo files and extract the EXIF data. So image_cameramake and model are both from EXIF. So it might be a problem with the camera make EXIF data.
0
pssandhuCommented:
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.
0
pssandhuCommented:
On the column I mean as mentioned by 8080_Diver
0
thomasmuttonAuthor Commented:

NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
85
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
110
110
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
85
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
0
0
0
0
0
0
0
0
0
0
0
0
NULL
NULL
NULL
NULL
NULL
NULL
NULL
69
0
0
0
0
0
0
0
0
NULL
 
AND
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
83
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
103
103
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
83
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
114
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

Open in new window

0
thomasmuttonAuthor Commented:
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("ý", string.Empty);
        }
        if (er["Equip Model"] != null)
        {
            strCameraModel = er["Equip Model"].ToString().Replace("ý", string.Empty);
        }

So I was getting rid of ý But there must be something else.
0
pssandhuCommented:
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.danshort.com/ASCIImap/
P.
0
8080_DiverCommented:
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/post/Regular-Expression-Transformation.aspx
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 ]
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
8080_DiverCommented:
Just out of curiosity, are you inserting the data into or extracting the data from SS2008?  If you are inserting it into SS2008, what is the source database?
0
thomasmuttonAuthor Commented:
Inserting from and to SQL2008
0
8080_DiverCommented:
Why not use SSIS?  (You can still do the RegEx thing to clean up the data.)
 
0
thomasmuttonAuthor Commented:
what is SSIS?
0
8080_DiverCommented:
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?
 
0
thomasmuttonAuthor Commented:
Yeah, I have used import and export before but never heard of integration services. im using express on both sides
0
8080_DiverCommented:
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.
0
thomasmuttonAuthor Commented:
I have solved this now thanks to your help. I used regular expressions on the EXIF data going into the database and its fine now!

Thanks for your help
0
8080_DiverCommented:
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.
0
thomasmuttonAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.