Two Strings To Be Concatenated Have Different Collation.
Mar 27, 2007
I'm using SQL Datatype SqlString, so I'm not sure what is going wrong...any help?
Here is a shortened version of the issue: Code and then Full Error Message.
Thanks in advance for any help
VB CODE
Code Snippet
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub MyProc(ByVal cAddress1 As SqlString, ByVal cCity As SqlString, ByRef sName As SqlString)
Dim output As SqlString
Dim space As New SqlString(" ")
output = System.Data.SqlTypes.SqlString.Concat(cAddress1, space)
sName = output
End Sub
End Class
Error Message
Msg 6522, Level 16, State 1, Procedure MyProc Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProc':
System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation.
System.Data.SqlTypes.SqlTypeException:
at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y)
at System.Data.SqlTypes.SqlString.Concat(SqlString x, SqlString y)
at SqlServerProject1.StoredProcedures.MyProc(SqlString cAddress1, SqlString cCity)
Hi all, I am having a little problem to use CLR Function inside SqlServer 2005. I do not see any problem with my code in C# as well as SqlServer sides. Please help if you know the solution.
/************************C# Codes***************************/ using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public class ClrUDF { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString GetString(SqlString str) { SqlString tmp = new SqlString("Hello "); tmp += str; return tmp; } };
/********************************************************/ CREATE ASSEMBLY [SqlClr] AUTHORIZATION [dbo] FROM 'C:ProjectsSqlClrSln..... SqlCrl.dll' WITH PERMISSION_SET = SAFE
Create FUNCTION dbo.GetString(@str as nvarchar(20)) RETURNS nvarchar(200) AS EXTERNAL NAME SqlClr.ClrUDF.GetString
Try to use the function:
Select dbo.GetString('Jame')
and the error is throw
Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'GetString': System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation. System.Data.SqlTypes.SqlTypeException: at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y) at ClrUDF.GetString(SqlString str)
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error:
The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it?
I am using SQL Server 2008. In ServerProperty function, there are two properties called “Collation” and “CollationID”. In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
OMG i'm so stupid, i edited my original post instead of replying!!
I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.
How do I prevent the following null 'Answer'?This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".I expect I'd have the same problem with nulls even if I wasn't using subqueries.SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS AnswerFROM table1WHERE condition=5GROUP BY fieldX
declare @filter varchar(100) set @filter = '10,''firststring''||10,''secondstring''' declare @tbl table (id decimal, name varchar(20))
insert into @tbl values (substring(@filter,0,patindex('%||%',@filter)))
hai in the above exmaple, i recieve input value (@filter) as concated string . pipeline(||) is my delimiter.. i want to split the string based on this delimater and need to insert into @tbl..
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
What is the error in this. i believe i can do this way to insert to concatinated values. Help pls
I've got a system that's grown like Topsy over the years and is now being asked to do things it was never designed for. As such I'm having to attempt ever more workarounds.
In a simple 3 column table
tbl1 (
SSN char(9), reason varchar(50), group char(1) )
I have a batched stored proc that selects rows based on the SSN
select reason, group from tbl where group = 'I' AND ssn = @ssn;
select reason, group from tbl where group = 'C' AND ssn = @ssn;
select reason, group from tbl where group = 'N' AND ssn = @ssn;
Now they want to group the rows and I'd really like to know beforehand how many and which groups to expect. So, I need to add a 4th batch select to the top of the statement that would return a up to 3 char string that indicates which groups are following. Something like IN or IC or CN, etc.
This should be a trivial query but I can't figure it out.
So I've run into another problem. I've figured out how to concatenate multiple rows into a single string my only problem is using that on another query with multiple rows...Basically what I'm trying to do is pull up information for each class a student has in his/her profile and while at it pull up any prerequisite classes that are associated with a certain class. So the final query would look something like this...
StudClassID Completed Class ID Name Description Credits Prereq... rest are insignificant... 0 0 CSC200 Cool prog... blah.... 3 CSC160, CSC180
I get the concept of the coalesce and cast just i'm not understanding how to get it to work with each return on the main select...anyways below are the tables and my current query call...
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Classes] Script Date: 03/31/2008 01:32:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student_Classes]( [StudClassID] [int] IDENTITY(0,1) NOT NULL, [StudentID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CreditID] [int] NULL, [Days] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Time] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Classroom] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Grade] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Semester] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Completed] [tinyint] NULL CONSTRAINT [DF_Student_Classes_Completed] DEFAULT ((0)), CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED ( [StudClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID]) REFERENCES [dbo].[Credits] ([CreditID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Prerequisites] Script Date: 03/31/2008 01:32:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Prerequisites]( [PrerequisiteID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Prerequisite] PRIMARY KEY CLUSTERED ( [PrerequisiteID] ASC, [ClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_ClassID] GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_Prereq] FOREIGN KEY([PrerequisiteID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_Prereq]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Credits] Script Date: 03/31/2008 01:32:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Credits]( [CreditID] [int] IDENTITY(0,1) NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Credits] [tinyint] NULL, CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED ( [CreditID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Credits] WITH CHECK ADD CONSTRAINT [FK_Credits_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Credits] CHECK CONSTRAINT [FK_Credits_ClassID]
SELECT sClass.StudClassID ,sClass.Completed ,sClass.ClassID AS 'Class ID' ,c.LongName AS 'Name' ,c.Description ,cred.Credits ,(SELECT COALESCE(@prerequisites + ', ', '') + CAST(PrerequisiteID AS varchar(7))) AS 'Prerequisites' ,sClass.Grade ,sClass.Days ,sClass.Time ,sClass.Classroom ,sClass.Semester ,sClass.Notes FROM Student_Classes sClass INNER JOIN Prerequisites preq ON preq.ClassID = sClass.ClassID INNER JOIN Classes c ON c.ClassID = sClass.ClassID INNER JOIN Credits cred ON cred.CreditID = sClass.CreditID WHERE sClass.StudentID = 0 ORDER BY sClass.ClassID ASC
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString
Here is the problem. I have table such as this PK selection VARCHARPK selection_choice VARCHAR show BIT When I use the sqlDataSource to create an update it creates this.UPDATE lu_selection_choices SET show = @show WHERE (selection = @selection) AND (selection_choice = @selection_choice) Now this is OK, but when you perform an update and in say a details control you change the selection_choice the value of the parameter @selection_choice is going to be equal to the old data. So it performs a successful update on show which isn't being changed selection_choice is. So the questions is. Is there anyway to tell the update function of the datasource to insert the a new parameter with the SET selection_choice = @SOME NEW PARAM slection_choice? Thanks, Darren King
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance.
FIELD ---------- 1234/4567 2345/6754 1234
I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3.
FIELD 1 FIELD 2 ------------------ 1234 4567 2345 6754 1234
Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()
I have a concated code from excel when I have to pick some values for db update, the problem is when one field is empty, this field is date type and I don't now how can I validate if UserDate_02 field is null or not, I get it from a cell in excel and later when I get all the code I update the db, this is the line :
UPDATE ITEMS SET UserField_01='AA',UserDate_01='01/28/2014',UserYesNo_01=1,UserDate_02=''WHERE LotNumber='134034143017297'and ItemCode='G22221'
Is there a way to have my output return this, "$-2,778.90 $$$ IN NETWORK DISCOUNT", instead of what I am currently getting which is this "-2779 $$$ IN NETWORK DISCOUNT" using:
Greetings,I used to do this kind of query in Ingres, where I concatenate(+)various fields as one field and search accordingly.Is there an equivalent method in SQL server ?SELECT a.rsrcid,a.rsrchqnumber,c.perslastname,c.persfirst name,b.asgtid,b.asgtactualstartdate,b.asgtactualenddate ,CASE b.enumstateWHEN '2' THEN 'Running'WHEN '3' THEN 'Cancelled'WHEN '4' THEN 'Closed'WHEN '6' THEN 'Open'END AS statusFROM pblocal.dbo.resources aINNER JOIN pblocal.dbo.assignments b ON b.asgtrsrcguid = a.rsrcguidINNER JOIN pblocal.dbo.persons c ON c.persguid = a.rsrcpersguidWHERE a.rsrcid+a.rsrchqnumber+c.perslastname+c.persfirst name not in(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirst nameFROM dtlocal.dbo.resources eINNER JOIN dtlocal.dbo.assignments f ON f.asgtrsrcguid = e.rsrcguidINNER JOIN dtlocal.dbo.users h ON h.userguid = e.rsrcuserguidINNER JOIN dtlocal.dbo.persons g ON g.persguid = h.userpersguid)
If I load up a local variable defined as varchar(MAX) with a 10,000-character continuous string, i.e. '123456789.....100000' the variable retains the full value as expected. However, if I break that string up into '123456789....5000' + '5001...10000', the variable is truncated at 8000 characters.
I have designed an SSIS package and in a data flow task I've defined my source and destination components and mapped all the fields. The task works fine as long as I have the same number of fields in my CSV file as what I have defined in the task.
The issue is that if my CSV contains more fields than what are declared in the mapping, then the extra fields at the end of each line are concatenated into the last column defined in my map. For instance:
I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).
So basically the SysInvNum column for this row should read '200500015'
When I run a basic query using the CAST or CONVERT functions like this:
SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform
OR
SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform
I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.
I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:
CREATE FUNCTION GetSysInvNum() RETURNS varchar AS BEGIN DECLARE @maxcaseid Int DECLARE @sysinvnum varchar
SELECT @maxcaseid = max (caseid) from caseform SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid RETURN @sysinvnum END
The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".
Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?
This may be one of those, "why the h3ll is he trying to do THAT?" type questions. Please bear with me (or ignore and move on)...
Background: I have a horizontally partitioned, dynamic merge publication that will be shared out with up to 60 subscribers. Things are going swimmingly, but I would like to get a more refined view of the merge update process; specifically, I am trying to query MSMerge_genhistory to see what updates have been made, which subscribers have received those updates, and what updates are pending.
MSMerge_genhistory contains a field nicknames (Varbinary(1000) which is a concatenated value of subscribers. The "official" definition (from the SQL 2000 system table map) is
A list of nicknames of other Subscribers that are known to already have this generation. Used to avoid sending a generation to a Subscriber that has already seen those changes. Nicknames in the nicknames list are maintained in sorted order to make searches more efficient. If there are more nicknames than can fit in this field, they will not benefit from this optimization.
The MSMerge_replinfo table contains a field replnickname (int) which contains a unique integer value that, when cast as a Varbinary, matches the values that are in the nicknames field in MSMerge_genhistory.
Compressed nickname for the replica.
So, my question is: how do I compare the varbinary(1000) (with concatenated values) with the int (cast as a varbinary). Examples:
I've two tables that I've made from some query subsets. Each table has a varchar field with notes/memos and I want to concatenate the fields into one long field.
The problem I'm running into is that when I run the query to check the concatenation, the field is truncated maybe 256 chars in.
I tried converting and casting the field as nvarchar 4000, and I've also done the same for the fields in the two tables, but that doesn't seem to help.
I can query for the fields from each table and none of them are truncated by themselves. It only happens after I concatenate them.
I've created a new table and inserted the results into it, but the field in it is also truncated.
SQL Server 2005.(SP2). MS SSRS; I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line. Example of current display: Customer Bill # ABC Company 123 ABC Company 456 ABC Company 789 etc
I want this to display as below: Cusotmer Bill # ABC Company 123, 456, 789, etc.
Is this possible in SSRS. Please help me with the syntax.
I'm having trouble defining a relationship in SQL Express with a concatenated foreign key.
I have 3 tables. Date, Company and Detail
Date has a PK1
Company has a PK2
Detail has a concatenated foreign Key of PK1, PK2
When I go to set the foreign key relationship in Management Studio 'Tables and Columns', it seems I can only select 1 primary key table on the left for my 2 foreign keys. How can I add 2 primary key tables to point to each FK in my detail table?
I am using SQL Server 2008. In ServerProperty function, there are two properties called “Collation” and “CollationID”. In some cases, I will only know the CollationID. Is it possible get the collation name from the CollationID? Is there a function called CollationNameFromID?
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address 101 E Commerce ST 120 E Commerce ST 2 Po Box 301 W. Bel Air Ave West Main Street, PO Box 1388
