Link to home
Start Free TrialLog in
Avatar of thomasmutton
thomasmuttonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of mallcore
mallcore
Flag of Slovenia image
Try 'N''Canon'' if that doesn't work escape the ' like this 'N\'Canon\''
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

How can i tell SQL to do this?

There is surely a deeper problem than this?
Avatar of pssandhu
pssandhu
Flag of Canada image
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.
Avatar of mallcore
mallcore
Flag of Slovenia image
Edit the generated query.
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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..
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of pssandhu
pssandhu
Flag of Canada image
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.
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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. ;-)
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of pssandhu
pssandhu
Flag of Canada image
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.
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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'
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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

Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of pssandhu
pssandhu
Flag of Canada image
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.
Avatar of pssandhu
pssandhu
Flag of Canada image
On the column I mean as mentioned by 8080_Diver
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER


NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
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

Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of pssandhu
pssandhu
Flag of Canada image
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.
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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?
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Inserting from and to SQL2008
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
Why not use SSIS?  (You can still do the RegEx thing to clean up the data.)
 
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

what is SSIS?
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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?
 
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Yeah, I have used import and export before but never heard of integration services. im using express on both sides
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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.
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image
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.
Avatar of thomasmutton
thomasmutton
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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!