Nulls In Concatenated String
Sep 23, 2005
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
View 1 Replies
ADVERTISEMENT
Oct 5, 2007
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
View 6 Replies
View Related
Mar 31, 2008
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
View 5 Replies
View Related
Jul 24, 2004
Hello,
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
Thanks for your help.
View 2 Replies
View Related
Aug 10, 2000
I have:
<<Select @SQL = 'Select Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like ''%' + @KeywordOrSIC + '%''' + ' order by Keyword'
exec(@SQL)>>
which works fine, but I want to modify it to do this
<<Select Replace(Keyword,',',' ') AS Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like 'real%' order by Keyword >>
which works from the query window but I can not get the right combination around the 'replace section' to make up a string for the exec.
All help greatly appreciated
Judith
View 1 Replies
View Related
Jun 20, 2008
I have a table with multiple rows for a single reference, e.g.
Col1 Col2
1 John
1 Mary
1 Tom
2 Dick
2 Anne
How do I create this view:
Col1 Col2
1 John, Mary, Tom
2 Dick, Anne
View 5 Replies
View Related
Oct 23, 2005
Hello,
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?
Thanks in advance
'Wale
View 10 Replies
View Related
Oct 17, 2007
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.
Thanks in advance.
View 2 Replies
View Related
Nov 8, 2007
Hi,
I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e.
-- Proc
CREATE PROCEDURE #proc_param_test
@p_param1 varchar(40) = NULL
, @p_param2 varchar(40) = NULL
AS
BEGIN
SELECT @p_param1, @p_param2
END
EXEC #proc_param_test 'test', 'test 2'
returns
---------------------------------------- ----------------------------------------
test test 2
but EXEC #proc_param_test 'test', 'test 2' + ' - the rest'
gives a Incorrect syntax near '+'. error
The solution must be a real doddle but it's a 'mare to find anywhere.
Cheers,
John
View 4 Replies
View Related
Nov 21, 2006
I have following 2 queries which return different results.
declare @accountIdListTemp varchar(max)
SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id)
FROM (SELECT Distinct acct_id
FROM SomeTable) Result
print @accountIdListTemp
The above query return the values without concatenating it.
declare @pot_commaSeperatedList varchar(max)
SELECT DISTINCT acct_id
into #accountIdListTemp
FROM SomeTable
SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id)
FROM #accountIdListTemp
print @pot_commaSeperatedList
drop table #accountIdListTemp
This query returns result as concatenated string of comma separated values.
If i want to get similar result in a single query how can i get it?
View 4 Replies
View Related
Feb 24, 2008
i defined 3 pkg scope user variables of type string in the ssis variables window, typed null as their value, and tried passing them (thru exec sql task) to an sp who expects 3 varchar(23) params.
The sp is blowing up because their values arent null.
The sql task command reads exec sp_name ?,?,?. In the sql task editor's param mapping window, I have each param listed with direction "input", data type varchar and the individual sp params in the parameter name column. I think the plumbing is set up correctly because I'm fine when I send the System Variable StartTime as a 4th param to the sp with data type DATE on the ssis end and datetime on the sp end.
Does anyone know if ssis string and engine varchar are incompatible or perhaps if null in the variables window doesnt initialize variables?
View 1 Replies
View Related
May 16, 2006
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
View 4 Replies
View Related
Feb 17, 2008
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies
View Related
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
May 5, 2008
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.
Example.
Table 1.
Column1 (Modem) Column2 (Highspeed) Column3 (Black)
Concatenated to be MODEM HIGHSPEED BLACK.
The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.
Column1 (MODEM HIGHSPEED BLACK) Column2 (44)
View 7 Replies
View Related
Aug 20, 2007
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.
Thanks.
View 7 Replies
View Related
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)
View 1 Replies
View Related
Jan 26, 2006
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
View 4 Replies
View Related
Sep 28, 2006
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
View 3 Replies
View Related
Mar 7, 2008
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()
View 14 Replies
View Related
Jan 28, 2014
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'
View 2 Replies
View Related
Mar 23, 2008
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:
LTRIM(STR(ar_billtrans_payadj.amount)+ ' ' + '$$$' + ' ' + ut_journalcode.[description]) AS PPmtTypeDesc
ar_billtrans_payadj.amount datatype is decimal
ut_journalcod.[description] datatype is varchar
View 5 Replies
View Related
Sep 21, 2005
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)
View 6 Replies
View Related
Mar 28, 2006
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.
Is there a way around this?
Thanks.
View 1 Replies
View Related
Mar 10, 2008
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:
CSV SQL
Product -> Product
M0 -> M0
M1 -> M1
M2 -> M2
This works fine, but if my CSV file looks like this:
Product
M0
M1
M2
M3
M4
The values in fields M3 and M4 are concatenated with M2 and all 3 are imported into field M2 in my sql table. Any ideas?
View 5 Replies
View Related
Oct 18, 2006
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)
View 2 Replies
View Related
Oct 13, 2006
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:
MSMerge_Genhistory:
Column nicknames:
0x677112FA
0x677112FAEAEB0704677112FA
MSMerge_Replinfo:
replnickname cast(replnickname as varbinary(1000))
17354636740x677112FA
-3536959960xEAEB0704
* Note that EAEB0704 is in the second Varbinary value from MSMerge_Genhistory, but not in the first.
If there's a simpler way to do this (ie, a stored proc/sp_help*) that I have overlooked, please do enlighten me.
Regards,
Hugh
View 1 Replies
View Related
Mar 30, 2004
(I'm using MS SQL 2000)
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.
Am I missing something obvious here?
View 2 Replies
View Related
Jul 4, 2015
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?
View 4 Replies
View Related
Jul 20, 2005
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
View 7 Replies
View Related
Jan 13, 2015
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
[Code] .....
View 6 Replies
View Related
Jun 13, 2012
I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?
View 2 Replies
View Related
Sep 16, 2004
Do NULLs take up space on the Database? I mean is room allocated for them?
View 9 Replies
View Related
