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
1216 Views

Similar Articles

[PageSpeed] 36

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:

Money 2007 portfolio problem with showing totals for stocks.
I upgraded from MSMoney 2005 to 2007. All appear to have gone well except I can't get my portfolio to show me any totals. It just shows me Symbol, Chart, Name, Last Price and Change. It won't show me Market value, Gain, Purchases or Realized gains. I open up the stock (using plus sign) and see that all of my buy/sell transactions are still there. HELP! NOTE: these stocks were showing fine in MSM 2005 and were transferred during the install into MSM2007. In microsoft.public.money, wurth51 wrote: >I upgraded from MSMoney 2005 to 2007. All appear to have gone well except I...

Strange problem in Microsoft Office 2003 PLEASE HELP!
Hello, When I installed the new Office 2003, I had no problems with Outlook 2003. I received and send mails whenever I want. A few days later, I get a message from outlook that Outlook could not make a connection with the server of my provider to get my mail on my computerb (I didn't change any settings!). I checked all my settings and there are good (I checked the mailserversettings, ports, etc) but the result is still the same: Outlook cannot make a connection with the mailserver. After that, I called my provider to ask them what to do. They told me that I must remove all the...

query help 02-23-08
I have a table that I created in Access and I also have created a report to display the information I want from the query, but the report is showing me more than what I want. I have grouping specified on my report to groups records based on the same date and city. However, I only want to report on those records where the date and city match but only on more than 3 occurances and no greater than 12 occurances. Do you recommend I change my query or change something in my report to accomplish this? Any help would be appreciated. create a new query Put the date field, city field, and ...

Convert Query field display from text to hyperlink
I have a query that draws data from a table that has been imported into Access 2003 from an SEC file of summary company filings. The fields in that imported table are: CIK - and ID field alphanumeric stored as text, e.g. "1375195" Company Name - stored as text FileName - stored as text, e.g. "edgar/data/1375195/0001144204-07-060584.txt" In my query, I'd like to add a field that inserts in front of the FileName the base URL to the Filename so that it becames a clickable hyperlink, e.g. FullFileName "ftp://ftp.sec.gov/edgar/" & FileName gives the res...

Exchange 2000 restore problem
Hi! I am unable to restore and activate a public folder file in exchange server 2000. with backup, the restore seems to work as I read in the help file, the pub.edb and pub.stm are restored, but I cannot activate the folder in the system manager. Ther error message I get is: ID: c1041724, internal error; the program tells me to close and reopen the system manager and restart the information store service, but that doesn't work. Any advices?? Thanks in advance, Peter Is your Pub store mounting?? I assume not. After trying to mount the store look in your app log it should tell you why...

IRM Problems -- please read
Several people have reported that they're unable to open IRM-protected documents that were protected prior to 18 November. While we're waiting to see if MS has a fix for this, here are some other troubleshooting tips for IRM. It's directed toward Outlook users but some of the suggestions will apply to Office in general, Methods 7 through 10 in particular. http://support.microsoft.com/kb/831401 If any of this helps, please let us know. Thanks! ...

format problem #4
I have a file of 50000 rows and in column A in just 2 or three cells i have a cell which show #NAME? but if u click that cell it will sho =- vikram, how to get rid of this cell...i mean to say that how t format that cell to a eadable format thank -- Message posted from http://www.ExcelForum.com Nothing wrong with the format (at least I don't think so), you just don't have a named range or value of vikram. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------------------------...

Tab delimited text problems within Excel
When opening a tab delimited file within excel I am experiencing problems. The file's last column is blank, which is needed, but when I open it within excel it recognises that the column exists for the first 15 rows then it doesn't recognises the column when saving back as a text file.. Any ideas? A lot of programs don't need the tab if there is nothing in the values. But if your program does, how about putting something in that last column that you want. Just put a single apostrophe (') in that last column of each row and excel will understand what you want. Andrew...

Problem with printing greeting cards in Publisher
Hi! I am trying to print a greeting card that has one side as glossy paper and the other matte. I have spent ages trying to figure out how to get it to print just page 1 and page 4 in one print format for the glossy paper and print pages 2 and 3 in matte paper but cannot seem to get it right :( I have wasted a lot of paper and card and I'm sure there must be a simple answer but it has me stumped. I don't want to have to print one page at a time as it never seems to work right and I end up getting confused as to which side should be where etc. Is it possible? Thank you. I ...

Threading problem
After converting to VC8 from VC6 I am experiencing some issues with a CFormView page that is multi-threaded. The form view has several lists. I launch a thread for each list. The thread has a pointer back to the CFormView (parent). The thread calls a method of the parent to fill the list. It first makes a database call then it processes the recordset and posts messages using the this pointer to insert each row. I have an array of thread pointers in the CFormView class. These get initialized to NULL and then when the CFormView is destroyed the member variable, m_pwndParent, ...

Calculate Field In Query
I am trying to create a third calculated field that will add two values: Referral To Consult: Nz((DateDiff("d",[MO Referral],[C1S])),0) and Consult To Treat: Nz((DateDiff("d",[C1S],[MO Tx])),0) I do get values for the above two fields but when I attempt to add them using: Total Wait: [Referral To Consult]+[Consult To Treat] criteria <100 I get prompted to enter parameter values for [Referral to Consult] and [Consult To Treat]. Christine Unfortunately, you need to repeat the entire calculation for each item TotalWait: Nz((DateDiff("d",[MO Referral],[C1S]...

New Win7 computer problems
Also posted in Lsoft group I bot a new HP p6240f computer with win 7. Loaded excel 95, then xl97 then xl2003. Then tried it. There must be something wrong in Windows or the video card cuz I get narrow columns and wide rows and misshapen shapes and small fonts in the formula bar. I can send a screen shot to anyone who may be able to assist. Normally, when I migrate from one computer to another all I have to do is adjust the zoom. All programs run fine on my Vista computers. I have tried the new LG2486L monitor on my notebook and it worked just fine and I tried an old dell 17"...

Font problems #3
I have had a sudden glitch in Publisher 07 that's never happened before: when I open an existing publication (also created in Pub. 07), I get a popup that tells me "One or more embedded fontd used in this publication are preview-only..." and then it goes on to tell me what that means. If I click "Cancel," I can't get into the file; if I click "OK" the file opens, but I can't edit it. Thing is, I didn't CHANGE A THING when I saved the file previously, and it was working just fine! I've tried everything I can think of, but nothing helps. ...

CRM problem #2
After installing CRM on SBS2k3 I get the following errors daily, this cuases my update services to stop working. Event 26 in the system log. Event Type: Information Event Source: Application Popup Event Category: None Event ID: 26 Date: 3/20/2006 Time: 6:30:10 AM User: N/A Computer: MYSERVER Description: Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun detected! Program: c:\windows\system32\inetsrv\w3wp.exe A buffer overrun has been detected which has corrupted the program's internal state. The program cannot safely continue execution and must now be termi...

Powerpoint 2007 Problem: Freeform Tool Snap To Grid
when I tried using ppt 2007 was disappointed the freeform tool lack function present in ppt 2003. It does not have snap to grid feature working. Any plans to fix this? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/commu...

Problems in Syncronization of Outlook Calendar with P900
All items except my Calendar items are getting synchronized. I have a problem cause that is not getting synchronized. Tried to select the Calendar etc etc... No error message but items do not reflect in phone or in PC. Please help You will need to apply to the maker of your synchronization software. If Microsoft Activesync, then post in microsoft.public.pocketpc.activesync. Not an Outlook problem. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious...

Crm Exchange Problem
Hi, i have installed crm exchange e-mail router on exchange server machine, everything ok. Crm server is on other machine. When I try to send and email from the crm cliente it shows me: "Unexpected error..." and in the event log puts "A soap error ocurred, access denied". I added crm server in pre-windows 2000 security group and the user i put when i installed email router is an administrador, the same that i used in crm server installation. I use windows 2003 server on each server, ¿some idea, please? ------=_NextPart_0001_3DD5C776 Content-Type: text/plain Cont...

problem opening with pub 2003 a Publisher brochure made in 2000
A brochure created in Pub 2000 does not open properly in Pub 2003. To avoid recreating this brochure in Pub 2003. is there another way Please explain "doesn't open properly". -- JoAnn Paules MVP Microsoft [Publisher] "MickR" <MickR@discussions.microsoft.com> wrote in message news:4BFB54AB-BCD0-4934-BA26-D2695B505127@microsoft.com... >A brochure created in Pub 2000 does not open properly in Pub 2003. To avoid > recreating this brochure in Pub 2003. is there another way After 2003 converts the file the publication is scrambled slightly, ie text b...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Workflow duplication problem (CRM 3.0)
I have the following pretty simple rule When account is assigned email to: [Owner] Subject: You have been assigned a new account (Create Task)Phone Call: Follow up call for new Account Stop The problem is that it is running multiple times....like 3-4 times. Can anyone explain why and what to do about it? ...

LOOKUP problem
Could some one have a solution to my problem? This is the sample: I have MY_RANGE defined which is 3 rows and 3 columns. I DON'T WANT to name a header separately (that would be {desc,TOM,MARRY}). desc TOM MARRY AGE 10 20 CASH 50 100 In other worksheet I have "CASH" in one cell and "MARRY" in the other. I need a formula to find 100 as Marry's cash. Vlookup requires 3 as the column number. How to find that Marry is third in row? I expected Index(MY_RANGE,1) to return {desc, TOM, MARRY} but it doesn't work. Thanks in advance Tomek ...

Combo Box to return all queries in database.
I developed a query that returns all queries in my database.- eg. SELECT Name, Type FROM MSysObjects WHERE (Type = 5 = -32764) ORDER BY Name What I want to do is create a combobox in a form that populates all queries in my database based on above query. I created code in After Update Events of Combo69 as noted below. Everything compiles OK; but when going to the combo box on the form, it doesn't work. The combo field remains blank. What am I doing wrong? Any assistance is most appreciated. Private Sub Combo69_AfterUpdate() On Error GoTo Err_Combo69_Click Select Case Me.C...

Query Question 03-05-08
I am working with queries pulling from an SQL linked table. I'm pulling orders within a given time period with the below fields. Each order line item appears on a separate line. Customer Name Order Number Date Item Type New Start Joe Smith 12345 3/3/08 Core Y Joe Smith 12345 3/3/08 Comp Y Pete Smith 33224 3/4/08 Comp Y I need to do a query to pull customers who only have Comp items only within the time period I pull, so ...

Problem with Exchange HTTP virtual server..pls help
Hi, I created Exchange Virtual Server using ESM & the same is not getting reflected under IIS.. What could be the problem here? pls guide... At present, i have 2 Exchange Virtual Servers (http servers) under Protocols > HTTP section of ESM. However, when i open IIS i only see Default Website? Also, i am unable to start/stop newly created Virtual Server using ESM as these options are grayed out. Any help appreciated. I would re-write my query in more detail as follows : Hi, I have my scenario as follows : 1 Front end server 1 Back end server Front end server has 2 ip ad...

strange problem
I am running exchange 5.5 on windows 2000. There are three users I deleted their mailboxes. I dont see the mailboxes in the Recepients container but I see the mailboxes in the mailboxes resource containter under server. When I try to delete those it says that object doesnt exist in the directory. Even in the veritas backup it picks up those mailboxes but cannot take the backup of those mailboxes and eventually says that the backup set failed. I dont know what is the reason and how to correct it. Thanks for your help. Khan ...