Importing CSV - Extra Fields Concatenated
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
ADVERTISEMENT
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 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
Nov 20, 2007
Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?
Jesus saves. But Gretzky slaps in the rebound.
View 4 Replies
View Related
Mar 17, 2014
I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.
SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))
[Code] .....
View 2 Replies
View Related
Nov 27, 2006
I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
View 4 Replies
View Related
Jul 23, 2007
Hi All,
I have a file with several columns in Comp-3.
I have downloaded the UnPack Decimal component and, as it needs byte stream (dt_bytes) as input, so I set up an appropriate Flat File Source columns.
But I get this error:
[Flat File Source [2201]] Error: Data conversion failed. The data conversion for column "DTCDC" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2201]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "DTCDC" (2250)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "DTCDC" (2250)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DTCDC is first of the columns packed. These are mostly date columns packed into 5 bytes - should be unpacked to normal SQL date.
I've tried different locale , as suggested in other threads, but it didn't help.
Can anybody help me with this issue how can I do it in (VB.NET script ,importing as String??).
Thanks in advance
Michal
View 4 Replies
View Related
May 12, 2004
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
View 4 Replies
View Related
Sep 12, 2001
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
View 2 Replies
View Related
May 16, 2007
Hi;We just migrated to SqlServer 2005. When I import tables from fromSqlServer database into another the identity fields get switched offfrom being identities.How can I prevent that from happening?Thanks in advance for any informationSteve-------------------------------------------------------------------Microsoft SQL Server Management Studio9.00.1399.00Microsoft Analysis Services Client Tools2005.090.1399.00Microsoft Data Access Components (MDAC)2000.085.1117.00(xpsp_sp2_rtm.040803-2158)Microsoft MSXML2.6 3.0 4.0 6.0Microsoft Internet Explorer7.0.5730.11Microsoft .NET Framework2.0.50727.42Operating System5.1.2600
View 3 Replies
View Related
Nov 30, 2007
Hello,
I am using the SSIS import/export wizard to import an Access table into a sql server 2005 database. The table has fields in Arabic (name, last name, etc.) and non-Arabic fields (gender, phone number, category, etc.).
The destination table has nvarchar columns.
After the import, I can see the Arabic characters in the destination table, but they appear in inverse order (from left to right). In Access (or Excel), Arabic fields appear as they should (from right to left) and non-Arabic fields are OK as well (from left to right).
If I do a simple copy-and-paste of a "correct" Arabic text into the table, the result is still wrong (inversed letters)...
Please help, I can't see what else to do.
Thank you.
View 4 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
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
View Related
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
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
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
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
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
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
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
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
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
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 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 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
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
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
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
