Problem with Query

SQL2008

I am trying to get the following query to work 

Declare @FileName varchar(max)
Declare @FullPath varchar(max)
Declare @Licence varchar(6)
Declare @LedgerKey uniqueidentifier 
Declare @ReportType varchar(25) 
Declare @Date varchar(10) 
Declare @Count Int
Declare @FileValue Money 
Declare @TEST Varchar(max)

SET @TEST = '
INSERT INTO [Table1]
           ([Licence]
           ,[LedgerKey]
           ,[ReportType]
           ,[CreatedDate]
           ,[ViewedDate]
           ,[Viewer]
           ,[ReportFileName]
           ,[TransCount]
           ,[FileValue]
           ,[xmlData])
     VALUES
           (''' + @Licence + '''
           ,''' + @LedgerKey +'''
           ,''' + @ReportType + '''
           ,CONVERT(datetime, ''' + @Date +''', 103)
           ,NULL
           ,NULL
           ,''' + @FileName + '''
           ,''' + @Count +'''
           ,''' + @FileVlue +'''
           ,SELECT * FROM OPENROWSET(BULK  ''' + @FullPath + ''' , 
SINGLE_BLOB) AS x)'
           
EXEC @TEST           


But I am getting the following error

The data types varchar(max) and uniqueidentifier are incompatible in the add 
operator. The LedgerKey field is a uniqueidentifier.

Any help would be welcome
0
Utf
9/10/2010 7:10:06 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
1348 Views

Similar Articles

[PageSpeed] 41

looks like that you are trying to concatenate a string value and a numeric 
value. The nurmeric value would need to bee converted to a string prior to 
concatenation.

"Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
news:FC66AF52-1AEC-4571-9810-520C1615E199@microsoft.com...
> SQL2008
>
> I am trying to get the following query to work
>
> Declare @FileName varchar(max)
> Declare @FullPath varchar(max)
> Declare @Licence varchar(6)
> Declare @LedgerKey uniqueidentifier
> Declare @ReportType varchar(25)
> Declare @Date varchar(10)
> Declare @Count Int
> Declare @FileValue Money
> Declare @TEST Varchar(max)
>
> SET @TEST = '
> INSERT INTO [Table1]
>           ([Licence]
>           ,[LedgerKey]
>           ,[ReportType]
>           ,[CreatedDate]
>           ,[ViewedDate]
>           ,[Viewer]
>           ,[ReportFileName]
>           ,[TransCount]
>           ,[FileValue]
>           ,[xmlData])
>     VALUES
>           (''' + @Licence + '''
>           ,''' + @LedgerKey +'''
>           ,''' + @ReportType + '''
>           ,CONVERT(datetime, ''' + @Date +''', 103)
>           ,NULL
>           ,NULL
>           ,''' + @FileName + '''
>           ,''' + @Count +'''
>           ,''' + @FileVlue +'''
>           ,SELECT * FROM OPENROWSET(BULK  ''' + @FullPath + ''' ,
> SINGLE_BLOB) AS x)'
>
> EXEC @TEST
>
>
> But I am getting the following error
>
> The data types varchar(max) and uniqueidentifier are incompatible in the 
> add
> operator. The LedgerKey field is a uniqueidentifier.
>
> Any help would be welcome 


0
CharlesL
9/10/2010 8:39:06 PM
On Fri, 10 Sep 2010 12:10:06 -0700, Peter Newman
<PeterNewman@discussions.microsoft.com> wrote:

>SQL2008
>
>I am trying to get the following query to work 
>
>Declare @FileName varchar(max)
>Declare @FullPath varchar(max)
>Declare @Licence varchar(6)
>Declare @LedgerKey uniqueidentifier 
>Declare @ReportType varchar(25) 
>Declare @Date varchar(10) 
>Declare @Count Int
>Declare @FileValue Money 
>Declare @TEST Varchar(max)
>
>SET @TEST = '
>INSERT INTO [Table1]
>           ([Licence]
>           ,[LedgerKey]
>           ,[ReportType]
>           ,[CreatedDate]
>           ,[ViewedDate]
>           ,[Viewer]
>           ,[ReportFileName]
>           ,[TransCount]
>           ,[FileValue]
>           ,[xmlData])
>     VALUES
>           (''' + @Licence + '''
>           ,''' + @LedgerKey +'''
>           ,''' + @ReportType + '''
>           ,CONVERT(datetime, ''' + @Date +''', 103)
>           ,NULL
>           ,NULL
>           ,''' + @FileName + '''
>           ,''' + @Count +'''
>           ,''' + @FileVlue +'''
>           ,SELECT * FROM OPENROWSET(BULK  ''' + @FullPath + ''' , 
>SINGLE_BLOB) AS x)'
>           
>EXEC @TEST           
>
>
>But I am getting the following error
>
>The data types varchar(max) and uniqueidentifier are incompatible in the add 
>operator. The LedgerKey field is a uniqueidentifier.
>
>Any help would be welcome

Try something like:

Declare @FileName varchar(max)
Declare @FullPath varchar(max)
Declare @Licence varchar(6)
Declare @LedgerKey uniqueidentifier 
Declare @ReportType varchar(25) 
Declare @Date varchar(10) 
Declare @Count Int
Declare @FileValue Money 
Declare @TEST Varchar(max)
DECLARE @blob varbinary(max) ;

SET @TEST = 'SET @blob = (SELECT * FROM OPENROWSET(BULK  ''' +
@FullPath + ''' , SINGLE_BLOB)' ;

EXECUTE sp_executesql @TEST, N'@blob varbinary(max) OUTPUT',
                      @blob OUTPUT;

INSERT INTO [Table1]
           ([Licence]
           ,[LedgerKey]
           ,[ReportType]
           ,[CreatedDate]
           ,[ViewedDate]
           ,[Viewer]
           ,[ReportFileName]
           ,[TransCount]
           ,[FileValue]
           ,[xmlData])
     VALUES
           ( @Licence 
           , @LedgerKey 
           , @ReportType 
           , @Date
           ,NULL
           ,NULL
           , + @FileName
           , @Count 
           , @FileVlue 
           , @blob )


John
0
John
9/10/2010 8:41:32 PM
> EXEC @TEST

To execute a string as dynamic sql, you must enclose the string in 
parentheses.


0
Scott
9/10/2010 8:43:31 PM
Peter Newman (PeterNewman@discussions.microsoft.com) writes:
> SET @TEST = '
> INSERT INTO [Table1]
>            ([Licence]
>            ,[LedgerKey]
>            ,[ReportType]
>            ,[CreatedDate]
>            ,[ViewedDate]
>            ,[Viewer]
>            ,[ReportFileName]
>            ,[TransCount]
>            ,[FileValue]
>            ,[xmlData])
>      VALUES
>            (''' + @Licence + '''
>            ,''' + @LedgerKey +'''
>            ,''' + @ReportType + '''
>            ,CONVERT(datetime, ''' + @Date +''', 103)
>            ,NULL
>            ,NULL
>            ,''' + @FileName + '''
>            ,''' + @Count +'''
>            ,''' + @FileVlue +'''
>            ,SELECT * FROM OPENROWSET(BULK  ''' + @FullPath + ''' , 
> SINGLE_BLOB) AS x)'
>            
> EXEC @TEST           
 
Don't use EXEC() to execute dynamic SQL like this (and even less
EXEC without parens because that means something else). Use parameterised
statements instaed. The only thing you need to expand directly 
into the query string is the file path.

I have this article on dynamic SQL on my web site which explains how
to use sp_executesql: http://www.sommarskog.se/dynamic_sql.html.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/10/2010 8:47:36 PM
Reply:

Similar Artilces:

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Problem with message routing between EXCH2K and EX2K3
We are running a ver simply Exchange topology, with an Exchange 2000 single server (ServerA) acting as "dumb" Information Store. Incoming and outbound SMTP email is relayed through a Linux based SMTP host relay on the same subnet, which handles all domain lookups for mail routing. ServerA is configured to use to the Linux SMTP server as it's smart host. The domain is a native Windows 2003 AD topology. My problem is the following: I have recently added a new Exchange 2003 Server (ServerB) into our Exchange site topology. If I create a user (UserA) on ServerA and then do a...

Run series of query -- I don't want any pop-up
I have generated 4 queries (1. empty all, 2. import data, 3. generate information, 4. Make a new table) I've created a button to run these 4 quries sequencially. ------------------------------------------------------------------------------------ Problem: MS ACCESS pops up "confirmation dialog" to ask me whether to delete/modify the table. ------------------------------------------------------------------------------------ Question: I don't want any question to be asked. I just want those queries to be executed once i press my button. -------------------------------...

Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location. The code looks like this: Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut Range("D5").Insert(xlShiftToRight) However, when it reaches this point, I get an error saying "Cut method of Range class failed". I've tried changing the destination range so that it reads similar to the origin range, but that didn't help. Can someone help me get the syntax on this line correct? I've got several similar lines further down in the code. If I can fix thi...

Visio Printing Problem
I am trying to print a flowchart. The print view displays the complet flowchart. However, when I print the flowchart, the top half of th flowchart is omitted and the bottom half prints. Any problem solvin recommendations would be appreciated -- jqsandma ----------------------------------------------------------------------- jqsandman's Profile: http://www.officehelp.in/member.php?userid=661 View this thread: http://www.officehelp.in/showthread.php?t=136719 Posted from - http://www.officehelp.i this usually ends up as being "apply available software patches to visio" or &quo...

Attachment problems
Hi, Is there anyway to unlock outlook 2002's block on ".exe" files. I know these are Level1 blocks, but can't find a way to configure it. I have recieved an important program and need to run it. Thanks in advance Scott Try here: Opening .exe Attachments with the Outlook E-mail Security Update and Outlook 2002 http://www.slipstick.com/outlook/esecup/getexe.htm -- Nikki Peterson [MVP - Outlook] "Scott White" <ddltrade@hotmail.com> wrote in message news:OmVN4JVnDHA.1672@TK2MSFTNGP09.phx.gbl... Hi, Is there anyway to unlock outlook 2002's block on...

A Strange Printing Problem
Our accounting department maintains an Excel file, updated in an onging process, converted to a PDF at month close, and distributed to our senior management. There are nine worksheets, seven behave normally, but two worksheets default to printing 2 copies. No macros, viruses, etc. Any suggestions on a fix will be appreciated. Paul I would try a different PDF converter. There are a # of free ones out there. It would be a simple and quick test to see if that was the problem. "PA" wrote: > Our accounting department maintains an Excel file, updated in an onging > process,...

Ipaq
A user where I work has had problems with his Ipaq serial connection 'going to sleep' over the last couple of weeks. Here are some further details: It's an Ipaq 3890 using ActiveSync 3.7 on a Windows NT4 workstation running Office 97. He synchronises mail of approx 1.5g , which includes Office attachments. The PDA has been working fine for over a year on ActiveSync 3.5, and there has been no software upgrades or changes on the users PC. There are no error reports in any way, and nothing is shown in the event viewer. I have already tried the following: Reinstalled ActiveSync 3.5 R...

MFC #define Problem
We've been using Visual C++ for at least two versions (VC++ 6.0, VC++ 7.0 .NET), but now we've upgraded to .NET 7.1.3088 and found that a macro define in shlobj.h includes the symbol NUM_POINTS. This generic name conflicts with a const declaration within the scope of a source file. Has anybody encountered this error? ...

Clusted stacked column chart
I'm posting this (after banging my head against a brick wall for several hours) in case anyone else should run into the following, and I would be very interested to know if anyone knows of potential reasons for the problem. I made use of Jon Peltier's technique for creating a clustered-stacked column chart (http://peltiertech.com/WordPress/clustered-stacked- column-charts/). This worked fine the first time I used it. However, thereafter at the step: "The month names are not centered correctly under the clustered columns. Format the scale of the secondary category axis (B throug...

Exchange 5.5 SMTP Relay problem
Hi, I currently had one Exchange 5.5 SP4 server that forward all email to Mailsweeper. Mailsweeper was configured to send outgoing mail to the following: domain1.com to xxx.xxx.xxx.xxx domain2.com to xxx.xxx.xxx.xxx all other mail to yyy.yyy.yyyy.yyyy. Due to company changes I have been asked to remove the mailsweeper box, now I am having problems doing the above with Exchange. I have already configured the IMS default "forward all messages to host" yyy.yyy.yyy.yyy and E-mail domains as specified domain1.com to xxx.xxx.xxx.xxx domain2.com to xxx.xxx.xxx.xxx This wa...