tbl_Projects
-------------------------
ProjID | Description
-------------------------
1 | First
2 | Second
3 | Third
I want to write a SELECT statement that will output in this format:
1 - First
2 - Second
etc...
I've tried these two statements:
SELECT (ProjID + ' - ' + Description) AS Projects
FROM tbl_Projects
//Error: Conversion failed when converting the varchar value 'First' to data type int.
and
SELECT CONCAT(ProjID, CONCAT(' - ', Description)) AS Projects
FROM tbl_Projects
//Error: The data types int and varchar are incompatible in the concat operator.
Is there any way to concatenate an int value with a varchar value?
In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname from category where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
(same question is also posted at sql-server-performance.com forums)
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay
hi i want to insert the varchar value inter column, how can i convert the values and insert in to the record if i give direct column name i am getting a message insert into EMP(id) SELECT name from STU
i am getting a message like this Syntax error converting the varchar value '200.00' to a column of data type int.
declare @found int; set @found = 'select sid from StickyContent where StickyId='+0+' and UserId='+171 exec (@found) delete from StickyContent where sid = @found
I need to pass this statement from my UI i.e in a single query. I am getting this error.
"Conversion failed when converting the varchar value 'select sid from StickyContent where StickyId=' to data type int. "
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
Hi, I need help. Understatement of the year right now, too = So... I've created a stored procedure that partially pulls a SQL union query to give me a complete one-line result set of a bunch of data... four separate selects comprise the query. The tables are structured identically to each other, but the values being pulled are specific and distinct. All of the non-date values stored are varchar values in the tables, and yet when I execute the SQL union query, I get the error message: Syntax error converting the varchar value to a column of data type int. I know SQL does implicit conversions, however Im not sure why it's trying to do that with this query, I suppose. it should have been straightforward. Any suggestions? Here is the query logic to look at, in case I'm missing the obvious
exec(' SELECT ACCOUNT, LNAME, FNAME, SEX, DOB, cast(MA_ContentValue as decimal) as MA_ContentValue, cast(MA_DOS as smalldatetime) as MA_DOS,cast(LDL_ContentValue as decimal) as LDL_ContentValue, cast(LDL_DOS as smalldatetime) as LDL_DOS, cast(HgbA1C_ContentValue as decimal)as HgbA1C_ContentValue, HgbA1C_DOS, cast(Creatinine_ContentValue as decimal) as Creatinine_ContentValue, Creatinine_DOS FROM ( SELECT Account, Lname, FName, Sex, DOB,MA_ContentValue, MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_MicroalbuminTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, LDL_ContentValue, LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_LDLTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, HgbA1C_ContentValue, HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_HgbA1CTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, Creatinine_ContentValue, Creatinine_DOS FROM dbo.vwDiabetic_CreatinineTop3) tbl1
') Anything else that I can provide for someone to help me? I appreciate any/all feedback!
this is in form VB Private Sub orderidtxbx_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles orderidtxbx.Click sqlconnection = New SqlConnection("Data Source= xxxx;Initial Catalog = xxxx; user ID = sa; Password = xxxxx;") sqlcommand = New SqlCommand("select Customer.LastName, Customer.FirstName from customer where ticket.ticketid ='" & orderidtxbx.Text & "'", sqlconnection)
error msg The multi-part identifier "ticket.ticketid" could not be bound
query in sql 2005,I need to make store procedure.
SELECT Itinerary.FlightID, Itinerary.Class, Itinerary.Quantity, Ticket.Date, Flight.FlightNo, Flight.AirLine, Flight.DepartureDate, Flight.ArrivalDate, Flight.DepartureTime, Flight.ArrivalTime, Flight.Price, Departure.DepartureLocation, Arrival.ArrivalLocation,GST=convert(int,Itinerary. Quantity*0.06*Flight.Price),PST=convert(int,Itiner ary.Quantity*0.07*Flight.Price),TotalPrice=convert (int,(Itinerary.Quantity*0.06*Flight.Price)+(Itine rary.Quantity*0.07*Flight.Price)+Flight.Price) FROM Flight INNER JOIN Itinerary ON Flight.FlightID = Itinerary.FlightID INNER JOIN Ticket ON Itinerary.TicketID = Ticket.TicketID INNER JOIN Departure ON Flight.DepartureLocationID = Departure.DepartureID INNER JOIN Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID WHERE (Itinerary.ticketid = '@Itinerary.ticketid')
if i change WHERE (Itinerary.ticketid = 1) it will read the data in the table.
I need to refer that itinerary.ticketid into textbox in form that's why i make like this WHERE (Itinerary.ticketid = '@Itinerary.ticketid') but i don't know what is worng with this.
This error Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value '@Itinerary.ticketid' to data type int(but my ticketid value already integer not varchar).
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block CREATE PROCEDURE [dbo].[sqlOutlookSearch] -- Add the parameters for the stored procedure here @OLIssueID int = NULL, @searchString varchar(1000) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @OLIssueID <> 11111 SELECT * FROM [OLissue], [Outlook] WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' ELSE SELECT * FROM [Outlook] WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
I'm trying to use a variable (@sqlfields1) for building a select statement. Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":
If @showo1 = 1 select @sqlfields1 = 'o1,'
If @showo5 = 1 select @sqlfields1 = @sqlfields1 + 'o5'
Please help me with this if you can. I have one table with CustomerID and some other data. In other table i have CustomerID(the link with the first table) and Agent The relation of the first with the second one is ONE TO MANY. I want something like this: Customer,'Agent1,Agent2,Agent3'
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was: 1 "A" 1 "B" 1 "C" 2 "D" 2 "E"
I want to group by the first value and concatenate the second value: 1 "A,B,C" 2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:
SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL
But unfortunately I am getting the error: "Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."
Any idea how to get around this at all just using an SQL query statement?
-- Prepare sample data DECLARE@Sample TABLE (ID INT, Code VARCHAR(3))
INSERT@Sample SELECT290780, 'LT' UNION ALL SELECT290780, 'AY' UNION ALL SELECT290781, 'ILS' UNION ALL SELECT290780, 'AY'
-- Show the expected output SELECT DISTINCTs1.ID, STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
I am beggining to learn stored procedures and I am trying toconcatenate the where clause below but I keep getting an error:declare @sqlwhere varchar(15)set @sqlwhere = 'parentid=2'select * from categories where @sqlwhereThis is the error I am gettingServer: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '@sqlwhere'.What am I doing wrong?ThanksRod
