Solved

SQL server problems scripting database

Posted on 2009-07-02
30
664 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:thomasmutton
  • 14
  • 8
  • 6
  • +1
30 Comments
 
LVL 5

Expert Comment

by:mallcore
ID: 24764688
Try 'N''Canon'' if that doesn't work escape the ' like this 'N\'Canon\''
0
 

Author Comment

by:thomasmutton
ID: 24764698
How can i tell SQL to do this?

There is surely a deeper problem than this?
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24764716
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
 
LVL 5

Expert Comment

by:mallcore
ID: 24764723
Edit the generated query.
0
 

Author Comment

by:thomasmutton
ID: 24764744
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
 

Author Comment

by:thomasmutton
ID: 24764763
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24764865
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24765251
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
 

Author Comment

by:thomasmutton
ID: 24765551
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24765601
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
 

Author Comment

by:thomasmutton
ID: 24765648
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
 

Author Comment

by:thomasmutton
ID: 24765709
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24765723
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
 

Author Comment

by:thomasmutton
ID: 24765770
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24765821
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24765830
On the column I mean as mentioned by 8080_Diver
0
 

Author Comment

by:thomasmutton
ID: 24765851

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

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
 

Author Comment

by:thomasmutton
ID: 24765874
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24765979
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
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24766333
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24766351
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
 

Author Comment

by:thomasmutton
ID: 24766357
Inserting from and to SQL2008
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24766444
Why not use SSIS?  (You can still do the RegEx thing to clean up the data.)
 
0
 

Author Comment

by:thomasmutton
ID: 24766450
what is SSIS?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24766490
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
 

Author Comment

by:thomasmutton
ID: 24766502
Yeah, I have used import and export before but never heard of integration services. im using express on both sides
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24766849
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
 

Author Comment

by:thomasmutton
ID: 24766884
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24766913
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
 

Author Comment

by:thomasmutton
ID: 24766941
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query 4 31
Numeric sequence in SQL 14 38
SQL JOIN 6 39
SQL Server issue connecting to named instance 6 45
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now