Concatenating fields: strings

Showing posts with label strings. Show all posts

Thursday, March 29, 2012

Configure connection strings for deployment to different servers

Hi,

We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?

-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/

Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?

Thanks in advance

Hi, it almost sounds like you have 1 configuration file...that all pacakges look at regardless what server they were deployed to. Thats the only way I could explain why making a change for the live server deployment affects packages running in test environment. If you look at the where things are deployed in each environment, do you see a config file in each location and does it contain the appropriate information? I think you can accoplish the dynamic connection string. Are you using property expressions to dynamically build/set your connections. There is a system variable available in SSIS named 'MachineName'. I must admit I have not played with it in a while and you need to be aware load order of 'stuff' is important. So, as a package spins up, it loads configurations and then applies property expressions as things are executed. So, I cannot recall if the system variable is set in time a property expression to use it. Another route is use an environment variable configuration. so the package loads and pulls in the servername frrom an environment variable on the machine and then that can be use in a property expression.

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

|||Will give it a try. Thanks for your help.

Configure connection strings for deployment to different servers

Hi,

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

Thanks in advance

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

|||Will give it a try. Thanks for your help.

Tuesday, March 27, 2012

Configuration File Locations - Likes to say it can't load them in designer

I have a package and added a configuration file to it to hold settings such as connection strings etc.

When I open the package up in the designer and check where the configuration file is from, it likes to specify it with a full path like this:

\\myserver\projects\packages\blah.xml

Where blah.xml is the configuration file and the package file is in the same exact directory.

This creates an issue when I use build and then try to install the package on a server. When installing, the server tries to read the config from \\myserver\projects\packages\blah.xml which it can not.

Conversely I've tried editing the location in the designer changing it to just be "blah.xml". Then after using the build option I am able to install the package successfully on the server (it can not find the xml file).

However when I then load the package later in the designer to try to edit or chagne it, the designer complains that the "configuration file could not be loaded" and it can't find it even though it is in the same directory that the package definition file is in.

What's going on with this and how do i solve it?

Unless you replicate your file and folder structure accross machines I think it is reasonable to expect a few moans when it cannot find stuff. One option is to use indirect configurations, which basically means you store the file location in an environment variable Plenty in Books Online, and Jamie talks about them here as well-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

Sunday, March 25, 2012

Configuration Approach

Background

I use four database connections strings.

I have about 30 packages that will use one or more of the connection stings.

I store the connection strings in XML configuration files.

I know that I can share configuration files across packages. Should I ?

Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package

Take another approach

Check this thread, It discusses the similar kind of issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1853477&SiteID=1

Thanks

|||For me, I just create all of the connection managers that are in the config file across all packages for a given project. It's a pain to setup, but it works for me.|||

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

|||

Danny Crowell wrote:

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

If it's such a pain, then use a configuration file for each connection manager object. Then each package only uses what it needs.

config file question

In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.

I just want to be sure I don't need both for my package to run successfully.

You can add only 1 or as many configuration values as you want in a single file. As a good practice you should have both values set via configurations; just to cover a possible change in the flat file location. You could also create a separate config file for each property.

Tuesday, February 14, 2012

Concetenate variable in sp_executesql

I am trying to cinstruct two strings/variables and conetenate it in sp_executsql

Isnt this the right way to do it ?

CREATE PROCEDURE dbo.XML_Final

(

@.Filepath varchar(50),

@.DBName varchar(20))

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

--DECLARE @.SQL nvarchar(200)

DECLARE @.INSERT nvarchar(max)

DECLARE @.WITH nvarchar(max)

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc OUTPUT

EXECsp_xml_preparedocument @.hdoc OUTPUT, @.doc

SELECT @.INSERT = N' INSERT INTO'+ @.DBName + N'.dbo.Student( Name,Age,Address)'

--SET @.WITH = N @.TEMP1

SELECT @.WITH = N' SELECT * FROM OPENXML (@.hdoc,'+N'''/Name/Age'/Address+N''')' +N'

WITH

(

Name text'+ N' ' + '''http://Name' + N ''',

Age int' + ' ' + N'''http://Age+ N''',

Address int'+ ' ' + N'''http://Address'+ N'''

exec sp_executesql @.INSERT+@.WITH

-- Remove the internal representation.

EXEC sp_xml_removedocument @.hdoc

Gives me a error at

exec sp_executesql @.INSERT+@.WITH :

Incorrect syntax near '+'

Thanks

You cannot concatenate the input variables like that. Try:

CREATE PROCEDURE dbo.XML_Final

(

@.Filepath varchar(50),

@.DBName varchar(20))

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

--DECLARE @.SQL nvarchar(200)

DECLARE @.INSERT nvarchar(max)

DECLARE @.WITH nvarchar(max)

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc OUTPUT

EXECsp_xml_preparedocument @.hdoc OUTPUT, @.doc

SELECT @.INSERT = N' INSERT INTO'+ @.DBName + N'.dbo.Student( Name,Age,Address)'

--SET @.WITH = N @.TEMP1

SELECT @.WITH = N' SELECT * FROM OPENXML (@.hdoc,'+N'''/Name/Age'/Address+N''')' +N'

WITH

(

Name text'+ N' ' + '''http://Name' + N ''',

Age int' + ' ' + N'''http://Age+ N''',

Address int'+ ' ' + N'''http://Address'+ N'''

set @.SQL = @.INSERT + @.WITH

exec sp_executesql @.SQL, N'@.hdoc int', @.hdoc

-- Remove the internal representation.

EXEC sp_xml_removedocument @.hdoc

Sunday, February 12, 2012

Concatentate sorted strings

Hi All,

I'm new to SQL Server.
I have the following table in a database of SQL Server 2005 Express.

ColA ColB
-- --
A 12
A 10
B 50
B 13

What I want to achieve is the following result :

Col A Aggr
--
A 2,12
B 13, 50

I tried the following query :

SELECT ColA, dbo.Concatenate(ColB) as Aggr
FROM
(SELECT TOP(100) PERCENT ColA, ColB
FROM TABLE_A
ORDER BY ColB) AS Derived_A
GROUP BY ColA

where Concatenate is the CLR User-defined aggreate function written in C#.

What I want to do is to first sort the rows on ColB and then to execute aggregate function over the sorted rows so as to get the above-mentioned results. However, what I got is the following

Col A Aggr

A 2,12

B 50,13 -- not sorted

I learnt from the SQL Server documentation that even though the ORDER BY clause is presented in the subquery, the query result is not guaranteed to be sorted. Only ORDER BY clause used in the outer query should work. So, how should this problem be solved? One way of which I'm thinking is to do the sorting in the aggregate function, but I'm worrying if this is harmful to the performance. Could anyone help me to solve the problem?

Thanks!

Regards,
Nathan

I think, it's dbo.Concatenate function which should perform this sorting.|||

You can't do this using SQLCLR UDFs right now. The ORDER BY in the derived table only applies to that scope. And there is no guarantee that rows/values will be sent in sorted order to the UDF within each group. You could do the sorted concatenation using a SQL technique like below:

select pt.ColA, pt.[1] + coalesce(',' + pt.[2], '') + coalesce(',' + pt.[3], '') as Aggr

from (

select ColA, ColB, ROW_NUMBER() OVER(PARTITION BY Col1A ORDER BY ColB) as seq

from tbl

) as t

pivot (min(ColB) for seq in ([1], [2], [3] /* as many items per group that you want to support */)) as pt

Concatenation and NTEXT

Hi everyone,
I have a problem trying to update an NTEXT column by enclosing it
between two strings, as shown in the SQL below:
UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
The error I get back is this:
Invalid operator for data type. Operator equals add, type equals ntext.
Please help! I'm using SQL Server 2000.
Thanks,
JonoWhy would you want to update every column in the table with the exact same
enclosure? You can't concatenate to an NTEXT column, and I don't see any
value in doing it the exact same way for every row anyway.
Anyway, since we are talking about HTML, this is something you have the
presentation layer do. For example, it is easier in ASP to say:
<pre><%=rs("NTextField")%></pre>
...than to do what you are proposing. If you really want to do this, see
the UPDATETEXT function in Books Online, but I still recommend against the
approach.
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>|||u need to use UPDATETEXT for this . Use the following example
-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text,
text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998),
NULL
DECLARE @.txtPtr1 Varbinary(16)
DECLARE @.txtPtr2 Varbinary(16)
DECLARE @.txtPtr3 Varbinary(16)
SELECT @.txtPtr1 = TEXTPTR(text1)
FROM TextExample
SELECT @.txtPtr2 = TEXTPTR(text2)
FROM TextExample
UPDATE TextExample
SET Text3 = Text1
WHERE i = 1
SELECT @.txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 ' '
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 TextExample.Text2 @.txtPtr2
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>

Friday, February 10, 2012

concatenating strings like summing numbers

I'd like to join string within a group, preferable with a separator, the same
way I'd sum values.
So, I'd like to write somethign like
=Join( Fields!Email.Value, ";" )
to obtain a string of semicolon separated substrings, the same way I'd say
=Sum( Fields!Number.Value) to get the sum of numeric field.
Does anyone know a way to do this?Having exactly the same problem. Thought of writing custom code, but not sure
what arguments to pass. The code had an array of strings as an argument, but
when i called the function the same way I would call an aggregate, got an
error message saying that aggregates can take only numeric values as
arguments..
"Max" wrote:
> I'd like to join string within a group, preferable with a separator, the same
> way I'd sum values.
> So, I'd like to write somethign like
> =Join( Fields!Email.Value, ";" )
> to obtain a string of semicolon separated substrings, the same way I'd say
> =Sum( Fields!Number.Value) to get the sum of numeric field.
> Does anyone know a way to do this?
>
>

Concatenating strings in a Select statment

I have this code:
declare @.var varchar(3000)
select @.var = @.var + column1 + ', '
from table1
select @.var
This statements give as a result all the values in column1 followed each by
a coma. My problem is that in a particular sever that doesn't work ok. If I
run the statements inside a proc it only returs the last value in the table,
but if I run it outside the proc (same query) it gives me the correct
results. Does anyone know why could that be?..
THanks>> Does anyone know why could that be?..
The SELECT statement you have is not a valid or supported in t-SQL. It is
simply a hack which seems to work in some cases, but breaks in a variety of
scenarios. Avoid such make-shift constructs due to its undocumented & risky
nature.
Anith

Concatenating strings in a CTE

Hi,

I am trying to create a concatenation of strings in a CTE, to show a "breadcrumb" path for a parent-child hierarchy. I get the error message,

Msg 240, Level 16, State 1, Line 3

Types don't match between the anchor and the recursive part in column "path_name" of recursive query "pc_hier_cte1".

Here are some SQL statements to study the situation. The first CTE merely computes the length of the "breadcrumb" string -- and it works. In the second CTE I try to perform the string concatenation, and that's where I get the error message.

Thanks for your help with this.

Dan

drop table #temp

drop table #pc_hier

create table #pc_hier

(

id int,

parent_id int,

name varchar(100)

)

insert into #pc_hier

select 1, 0, 'top level node' union all

select 2, 1, 'second level node 2' union all

select 3, 1, 'second level node 3' union all

select 4, 1, 'second level node 4' union all

select 20, 2, 'third level node 20' union all

select 21, 2, 'third level node 21' union all

select 22, 2, 'third level node 22' union all

select 30, 3, 'third level node 30' union all

select 31, 3, 'third level node 31' union all

select 32, 3, 'third level node 32' union all

select 40, 4, 'third level node 40' union all

select 41, 4, 'third level node 41' union all

select 42, 4, 'third level node 42'

;

with pc_hier_cte (id, parent_id, name, dist_from_parent, path_length) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length -- do not include top-level parent name in the path string

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length

from #pc_hier ph

inner join pc_hier_cte phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length

into #temp

from pc_hier_cte

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp

order by 4,3,1,2

;

-- Here is the part that does not work, where I am trying to concatenate strings.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

'' as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

(phc.path_name + ' > ' + ph.name) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

Hmmmm, by reading a post from today I solved this problem: I just needed to use CAST to define the value as a large VARCHAR. (I thank Kent Waldrop My07 for his post.)

Here is the fix.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

cast('' as varchar(1000)) as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

cast((phc.path_name + ' > ' + ph.name) as varchar(1000)) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp1

order by 4,3,1,2

;

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

Concatenating strings

I have a table with seven different descriptor fields, and at times it's
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:

> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.

Thursday, March 29, 2012

Configure connection strings for deployment to different servers

Configure connection strings for deployment to different servers

Tuesday, March 27, 2012

Configuration File Locations - Likes to say it can't load them in designer

Sunday, March 25, 2012

Configuration Approach

config file question

Tuesday, February 14, 2012

Concetenate variable in sp_executesql

Sunday, February 12, 2012

Concatentate sorted strings

Concatenation and NTEXT

Friday, February 10, 2012

concatenating strings like summing numbers

Concatenating strings in a Select statment

Concatenating strings in a CTE

Concatenating Strings

Concatenating strings

Concatenating fields

Blog Archive

About Me