SQL server problems scripting database

How can i tell SQL to do this?

There is surely a deeper problem than this?

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.

Edit the generated query.

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

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

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.

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. ;-)

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

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.

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'

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.

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];
                                              

1:
2:
3:
4:
5:

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.

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.

On the column I mean as mentioned by 8080_Diver

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.

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.

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?

Inserting from and to SQL2008

Why not use SSIS?  (You can still do the RegEx thing to clean up the data.)

what is SSIS?

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?

Yeah, I have used import and export before but never heard of integration services. im using express on both sides

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.

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

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.