Configure a Child Package to use the configuration file of a Parent Package?

Hi guys,

Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.

However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.

When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.

How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?

Hope someone could help. Thanks.Still no idea on this guys?

Essentially I just want the child package to use and consume the same .dtsConfig file as the parent package. How do I do that?|||You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.|||

DarrenSQLIS wrote:

You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.

This is exactly what I did, really. And besides, when you try to pass a connectionstring through a parent package variable to a child, for some reason or another the password property value gets lost.

Any other ideas?|||

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

|||

DarrenSQLIS wrote:

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

I guess I'm compelled to use Windows Authentication for this case, just so I don't need to mess with the password.

Anyway, thanks |||If there is a chance that you can use Windows Auth, then I would strongly recomend it, for this any many other reasons, it just makes life easier. Good luck.

config file - read connection string

I have Custom.dll for retriving data from database. I don't want to store
connection string in class. Is it possible to store in some RS config file or
i have to use XML file and read from it?
Thx.Yes, create an appSettings element in the RSReportDesigner.config (design
time), and in the Report Server web.config (runtime). Your custom dll will
be loaded in the host process which will allow you to use
ConfigurationSettings.AppSettings to read its configuration settings.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
<AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> I have Custom.dll for retriving data from database. I don't want to store
> connection string in class. Is it possible to store in some RS config file
or
> i have to use XML file and read from it?
> Thx.|||I apologize for misleading you about the RSReportDesigner.comfit. It's been
a while since I tried this...so, here is the true story:
1. During runtime the report is generated under the Report Server host
process so the <appSetting> section in web.comfig file should work. Try
rendering a report from the Report which has a textbox with the following
expression:
= System.Configuration.Configuration.Upsetting(<your comfit value>)
. Please note that the web.config file already has a configuration section
so you need to add only the <appSettings> element.
2. During design time it is a bit trickier. Unfortunately, the current
configuration handler of the Report Designer doesn't seem to recognize
<appSettings>. However, you can render the report in debug mode by hitting
F5. This renders the report under ReportHost.exe. To get the config settings
working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
Server\80\Tools\Report Designer and place your configuration section there
(the <configuration> element should be spelled with small "c"), e.g.:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="serverUrl" value="http://localhost/reportserver" />
</appSettings>
</configuration>
b) To get the Preview tab working you could either check for Nothing and
replace that with a default value, or wrap the ConfigSettings.AppSettings
call to default to some default constant values. Once again, this is only
needed during design time. Your runtime report generation shouldn't need
this hack.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
<AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> Doesn't work..
> In put this
> <Configuration>
> <appSettings>
> <add key="KipConnectionString"
>
value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
/>
> </appSettings>
> <Add Key="SecureConnectionLevel" Value="0" />
> <..
> in RSReportDesigner.config and web.config and i still get an error
> The key 'KipConnectionString' does not exist in the appSettings
> configuration section.
> "Teo Lachev" wrote:
> > Yes, create an appSettings element in the RSReportDesigner.config
(design
> > time), and in the Report Server web.config (runtime). Your custom dll
will
> > be loaded in the host process which will allow you to use
> > ConfigurationSettings.AppSettings to read its configuration settings.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > I have Custom.dll for retriving data from database. I don't want to
store
> > > connection string in class. Is it possible to store in some RS config
file
> > or
> > > i have to use XML file and read from it?
> > >
> > > Thx.
> >
> >
> >|||Of course the my spell check has made a mess :-) Please replace comfit with
config and Upsetting with AppSettings.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:eAKIjUWgEHA.1644@.tk2msftngp13.phx.gbl...
> I apologize for misleading you about the RSReportDesigner.comfit. It's
been
> a while since I tried this...so, here is the true story:
> 1. During runtime the report is generated under the Report Server host
> process so the <appSetting> section in web.comfig file should work. Try
> rendering a report from the Report which has a textbox with the following
> expression:
> = System.Configuration.Configuration.Upsetting(<your comfit value>)
> . Please note that the web.config file already has a configuration section
> so you need to add only the <appSettings> element.
> 2. During design time it is a bit trickier. Unfortunately, the current
> configuration handler of the Report Designer doesn't seem to recognize
> <appSettings>. However, you can render the report in debug mode by
hitting
> F5. This renders the report under ReportHost.exe. To get the config
settings
> working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
> Server\80\Tools\Report Designer and place your configuration section there
> (the <configuration> element should be spelled with small "c"), e.g.:
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <appSettings>
> <add key="serverUrl" value="http://localhost/reportserver" />
> </appSettings>
> </configuration>
> b) To get the Preview tab working you could either check for Nothing and
> replace that with a default value, or wrap the ConfigSettings.AppSettings
> call to default to some default constant values. Once again, this is only
> needed during design time. Your runtime report generation shouldn't need
> this hack.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> > Doesn't work..
> >
> > In put this
> >
> > <Configuration>
> > <appSettings>
> > <add key="KipConnectionString"
> >
>
value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
>
XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
> />
> > </appSettings>
> > <Add Key="SecureConnectionLevel" Value="0" />
> > <..
> >
> > in RSReportDesigner.config and web.config and i still get an error
> >
> > The key 'KipConnectionString' does not exist in the appSettings
> > configuration section.
> >
> > "Teo Lachev" wrote:
> >
> > > Yes, create an appSettings element in the RSReportDesigner.config
> (design
> > > time), and in the Report Server web.config (runtime). Your custom dll
> will
> > > be loaded in the host process which will allow you to use
> > > ConfigurationSettings.AppSettings to read its configuration settings.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com
> > >
> > >
> > > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > > I have Custom.dll for retriving data from database. I don't want to
> store
> > > > connection string in class. Is it possible to store in some RS
config
> file
> > > or
> > > > i have to use XML file and read from it?
> > > >
> > > > Thx.
> > >
> > >
> > >
>|||Thank you. It's working.
"Teo Lachev" wrote:
> Of course the my spell check has made a mess :-) Please replace comfit with
> config and Upsetting with AppSettings.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:eAKIjUWgEHA.1644@.tk2msftngp13.phx.gbl...
> > I apologize for misleading you about the RSReportDesigner.comfit. It's
> been
> > a while since I tried this...so, here is the true story:
> >
> > 1. During runtime the report is generated under the Report Server host
> > process so the <appSetting> section in web.comfig file should work. Try
> > rendering a report from the Report which has a textbox with the following
> > expression:
> > = System.Configuration.Configuration.Upsetting(<your comfit value>)
> >
> > . Please note that the web.config file already has a configuration section
> > so you need to add only the <appSettings> element.
> >
> > 2. During design time it is a bit trickier. Unfortunately, the current
> > configuration handler of the Report Designer doesn't seem to recognize
> > <appSettings>. However, you can render the report in debug mode by
> hitting
> > F5. This renders the report under ReportHost.exe. To get the config
> settings
> > working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
> > Server\80\Tools\Report Designer and place your configuration section there
> > (the <configuration> element should be spelled with small "c"), e.g.:
> > <?xml version="1.0" encoding="utf-8" ?>
> > <configuration>
> > <appSettings>
> > <add key="serverUrl" value="http://localhost/reportserver" />
> > </appSettings>
> > </configuration>
> >
> > b) To get the Preview tab working you could either check for Nothing and
> > replace that with a default value, or wrap the ConfigSettings.AppSettings
> > call to default to some default constant values. Once again, this is only
> > needed during design time. Your runtime report generation shouldn't need
> > this hack.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> > > Doesn't work..
> > >
> > > In put this
> > >
> > > <Configuration>
> > > <appSettings>
> > > <add key="KipConnectionString"
> > >
> >
> value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
> >
> XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
> > />
> > > </appSettings>
> > > <Add Key="SecureConnectionLevel" Value="0" />
> > > <..
> > >
> > > in RSReportDesigner.config and web.config and i still get an error
> > >
> > > The key 'KipConnectionString' does not exist in the appSettings
> > > configuration section.
> > >
> > > "Teo Lachev" wrote:
> > >
> > > > Yes, create an appSettings element in the RSReportDesigner.config
> > (design
> > > > time), and in the Report Server web.config (runtime). Your custom dll
> > will
> > > > be loaded in the host process which will allow you to use
> > > > ConfigurationSettings.AppSettings to read its configuration settings.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
> > > >
> > > >
> > > > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > > > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > > > I have Custom.dll for retriving data from database. I don't want to
> > store
> > > > > connection string in class. Is it possible to store in some RS
> config
> > file
> > > > or
> > > > > i have to use XML file and read from it?
> > > > >
> > > > > Thx.
> > > >
> > > >
> > > >
> >
> >
>
>

Config and Connection String

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

That behavior is by design; BIDS will not store the password for you; you have to add it manually. Hopefully you will not need to do it very often

|||

dgrm44 wrote:

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

Nope. You've got it figured out. BIDS doesn't store passwords.|||

The other guys are quite right but it may help to explain why.

As you may or may not be aware, a few years ago Microsoft introduced a programme called the 'Trustworthy Computing Initiative'. An effort to try and make their products more secure. This is one of the main reasons it took them so long to get Vista out of the door.

Anyway, you can see the effect of this here. If you are going to store passwords in a text file (which is inherently a security risk) then Microsoft want YOU to be responsible for doing that - they will not do it for you.

-Jamie

|||

The what is the best practice for storing my ssis connection string passwords if not in the config file?

|||

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)|||

Phil Brammer wrote:

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

|||

Jamie Thomson wrote:

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

Well at present, it is the best practice as it's really the only practice that works, correct? |||YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?|||

dgrm44 wrote:

YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?

You enter the package password into that property. Then all sensitive data (passwords!) are encrypted with that password entered, using EncryptSensitiveWithPassword. Then, to open/execute the package, that password will be required to be passed into DTEXEC upon execution in order to decrypt the sensitive information. This is a good alternative.|||

More info here:

Storing Passwords

(http://blogs.conchango.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx)

-Jamie

coneverting oledb code to connect to remote sqlserver

i have some oledb code made in c#(vs 2005) it is for local msaccess file. i want to conevert the code for sql server where connection string placed in web.config file seperately. please help me.
here is code

private void buildGrid(){string conStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; conStr += Server.MapPath("~/common/db/demo.mdb"); System.Data.OleDb.OleDbConnection dbCon =new System.Data.OleDb.OleDbConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.OleDb.OleDbDataAdapter dbAdapter =new System.Data.OleDb.OleDbDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

please healp me..

Try this:

private void buildGrid(){string conStr ="Server=MyServer; Database=MyDatabase; User Id = MyUser; Password=MyPassword";System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

I hope this will help.

Good luck.

|||

thank you,

i got you but i placed my connection string in to the web.config file how to use connection string placed in the web.config file with code you posted

|||

Try this:

private void buildGrid(){string conStr =ConfigurationSettings.AppSettings("MyConnectionStringInWebConfig"); System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

Here is a good link:
How to: Read Connection Strings from the Web.config File
http://msdn2.microsoft.com/en-us/library/ms178411.aspx

Good luck.

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

conditional formatting - noob question

Hello,

I do not know vb. How can I turn the below idea into VB.NET and embed in a table object in RS?

IF Fields!someField.Value LIKE "%string%" THEN Fields!someField.Value

.. the goal is to only include values that contain the string, and exclude all other values from the table.

TYIA...You can try some filtering on the Table - right click on table - filters - add the field with a Like filter.|||

Yes, you should filter on the table.
Note: Like filters work with * instead of %. E.g. ="abc*"
-- Robert

Conditional compile the connection string in datasets

I need to find a way to useconditional compilation option to change the connection string in ado.net datasets 2.0.

Debug > connection 1 : test server
Release > connection 2 : real server

How is it possible ?This is often accomplished by storing the connection string in the web.config file, and using different web.config files on your test and real servers.|||

This is what I do in global.asax.vb:

Sub Application_Start(ByVal senderAsObject,ByVal eAs EventArgs)' Fires when the application is startedIf System.Environment.MachineName ="{Your production machine name here}"Then

Application(

"IsProduction") =TrueElse

Application(

"IsProduction") =FalseEndIfDim configAs Configuration

config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(

"~")Dim cssAs ConnectionStringsSection

css = config.ConnectionStrings

Dim settingAs ConnectionStringSettingsDim fUpdateAsBoolean =FalseDim cAsNew ConnectionStringSettingsCollectionForEach settingIn css.ConnectionStrings

c.Add(setting)

NextForEach settingIn cIf (Left(setting.Name, 5).ToUpper ="TEST-"AndNot Application("IsProduction"))Or _

(Left(setting.Name, 5).ToUpper =

"PROD-"And Application("IsProduction"))ThenIf css.ConnectionStrings(setting.Name.Substring(5))IsNothingThen

css.ConnectionStrings.Add(

New ConnectionStringSettings(setting.Name.Substring(5), setting.ConnectionString, setting.ProviderName))

fUpdate =

TrueElseIf css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString <> setting.ConnectionStringThen

css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString = setting.ConnectionString

fUpdate =

TrueEndIfEndIfEndIfNext' Make sure debug is turned off when running on the production serverDim xAs System.Web.Configuration.CompilationSection = config.GetSection("system.web/compilation")If x.DebugAnd Application("IsProduction")Then

x.Debug =

False

fUpdate =

TrueEndIfIf fUpdateThen

config.Save()

EndIfEndSub

Then I'll create connection strings like "TEST-MyConnection" and "PROD-MyConnection", etc. In code (or from data controls), I'll reference them as "MyConnection". When the application starts up, it'll update the web.config with the correct connection string to use on the machine it's running (and possibly restarting the application).

|||thanks a lot

Condition on a field date in a query data set

I need to extraxt from a table of my db the records that satisfy a condition
on a field date.
I use this query string for the data set:
="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
(DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
In my table Partitario exists a record with datascadenza < actual date
calculated by Globals!ExecutionTime: but this query doesn't extract nothing!
Is the syntax right?
How can I solve this issue?
Many thanksUse a datediff instead of a <= comparison.
Craig
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
>I need to extraxt from a table of my db the records that satisfy a
>condition
> on a field date.
> I use this query string for the data set:
> ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> In my table Partitario exists a record with datascadenza < actual date
> calculated by Globals!ExecutionTime: but this query doesn't extract
> nothing!
> Is the syntax right?
> How can I solve this issue?
> Many thanks|||Or use GETDATE() in SQL query string.
Many thanks
"Craig" wrote:
> Use a datediff instead of a <= comparison.
> Craig
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
> >I need to extraxt from a table of my db the records that satisfy a
> >condition
> > on a field date.
> > I use this query string for the data set:
> > ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> > DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> > ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> > Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> > Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> > (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> >
> > In my table Partitario exists a record with datascadenza < actual date
> > calculated by Globals!ExecutionTime: but this query doesn't extract
> > nothing!
> > Is the syntax right?
> >
> > How can I solve this issue?
> >
> > Many thanks
>
>

concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates

Concatination Query?

I have a need to query some data and string all my results by id. I am fairly close to the results but stuck on the final piece. Any help would be greatly appreciated.

Here's the scenario: My data looks as follows:

UserID Results 1095 ,,,,,,, 1095 ,,,,,8,, 1095 ,,,,,,, 1095 ,,,,,8,, 1247 ,2,3,,,6,,, 1247 ,2,3,,,6,,, 1247 ,2,3,,,6,,, 1247 ,2,3,,,6,,, 4069 ,,,,,,, 4069 ,,,,,,, 4069 ,,,,,,, 4069 ,,,,,,, 4070 ,,6,,,,, 4070 ,,6,,,,, 4070 ,,6,,,,, 4070 ,,6,,,,,

I want to query it and end up with the results all strung together under each UserID as follows:

1095 ,,,,,,,,,,,,8,,,,,,,,,,,,,,8,, 1247 ,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,, 4069 ,,,,,,,,,,,,,,,,,,,,,,,,,,,, 4070 ,,6,,,,,,,6,,,,,,,6,,,,,,,6,,,,,

I know if I use the following code I can string all the results together

select *, Cast((SELECT Results + ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Results from #temp1

But I can't figure out how to break it down by individual UserID. Any help is greatly appreciated. Thanks in advance

If you are using SQL 2005, you could do something like this:

-- Create Comma Delimited list from multiple rows
-- From Tony Roberson
-- SQL 2005

DECLARE @.MailingList table
( IndividualName nvarchar(100) not null,
ListName nvarchar(10) not null
)

INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List A' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List B' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List C' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List A' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List B' )
INSERT INTO @.MailingList VALUES( 'Hector Lopez', 'List A' )

SELECT DISTINCT
IndividualName,
List = substring( ( SELECT ', ' + ListName as [text()]
FROM @.MailingList m2
WHERE m2.IndividualName = m1.IndividualName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MailingList m1

/*
Gives this result:

Alex R List A
Joe R List A, List B
Tony R List A, List B, List C

*/

|||

Outstanding thanks!!!

Final question, in the query

...FOR XML path(''), elements
), 3, 1000
)

what does the 3, 1000 do

|||

They are the second and third arguments for the substring function. They remove the first two characters from the final string (up to 1000 characters starting at the third).

The list is generated with ', ' in front of each member (i.e. ', a, b, c, d, e') whereas you do not want a comma/space in front of the first item so this removes the first ', ' from the front of the list.

|||Once again thanks I really appreciate the help|||

As Dhericean noted, those are parameters for the substring() function.

You could eliminate the space in [ SELECT ', ' ] and change the value 3 to 2. And you can increase or reduce the value 1000 to more closely represent the lenght of the final concatenated string.

|||Arnie -

A while back you provided me this solution to a need I had. Many thanks, I have use it often and with many tweeks. Now I am trying to do something similar and I am stuck, but I can't figure out why....

Here is our process and what is happening:

This is our preliminary query... it gets the group of raw data we will be analyzing (and it works)

SELECT SITEDATA.SITEID
, SITEDATA.ANOMALYT1 + ','
+ SITEDATA.ANOMALYT2 + ','
+ SITEDATA.ANOMALYT3 + ','
+ SITEDATA.ANOMALYT4 + ','
+ SITEDATA.ANOMALYT5 + ','
+ SITEDATA.ANOMALYT6 + ','
+ SITEDATA.ANOMALYT7 + ','
+ SITEDATA.ANOMALYT8 AS ANOMALIES
, SITEDATA.CALC_DATE, SITE.PROPID, SITE.LOCATION
INTO [#TEMP1]
FROM SITEDATA INNER JOIN SITE ON SITEDATA.SITEID = SITE.SITEID
WHERE (SITEDATA.SITEID = 907) AND (SITEDATA.CALC_DATE BETWEEN '1/1/06' AND '12/31/06')

Here is a sample of results from the above Query (Note, there are values in the Anomalies column):

SITEID Anomalies Calc_Date PropID Location
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5

Now we want to string all the data in the Anomalies column together by Distinct Calc_Date. To do this we execute the following Proc:

SELECT DISTINCT CALC_DATE, SITEID, PROPID, LOCATION, ANOMALIES = SUBSTRING( (
SELECT ',' + ANOMALIES AS [text()] FROM #TEMP1 T1 WHERE T1.SITEID = T2.SITEID FOR XML PATH(''), ELEMENTS ), 3, 1000 )
INTO #TEMP2
FROM #TEMP1 T2

This procedure strings the Anomalies together, but the data appears to be lost, i.e. we should get ,2,3,,,6,,,2,3,6,,,,2,3,,,6, etc. but instead all we get is ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

I thought the issue might be caused by date and time not being distinct, so I tried converting the date to varchar and rerunning, but no difference.... Any thoughts?|||

Jim,

Your query worked perfectly for me. (minor formatting alterations...)

SET NOCOUNT ON

DECLARE @.Temp1 table
( RowID int IDENTITY,
SiteID int,
Anomalies varchar(100),
Calc_Date datetime,
PropID int,
Location int
)

INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )

SELECT DISTINCT
Calc_Date,
SiteID,
PropID,
LOCATION,
Anomalies = substring( ( SELECT ',' + Anomalies AS [text()]
FROM @.Temp1 T2
WHERE T1.SITEID = T2.SITEID
FOR XML PATH(''), ELEMENTS
), 3, 1000
)
INTO #Temp2
FROM @.Temp1 T1

SELECT *
FROM #Temp2

Calc_Date SiteID PropID LOCATION Anomalies

-- -- --
2006-03-26 00:00:00.000 907 40 5 2,3,,,6,,,,,2,3,,,6,,,,,2,3,,,6,,,,,

(Anomalies repetition removed for display purposes...)

|||Thanks again Arnie.

I couldn't replicate your success, however I figured out a different query to and up with the same results. Because the DS is small, I just searched the Anomalies field without combining them, then I looked for distinct date. I think it ended up being faster anyway

Concatination Query?

I have a need to query some data and string all my results by id. I am fairly close to the results but stuck on the final piece. Any help would be greatly appreciated.

Here's the scenario: My data looks as follows:

UserID

Results

1095

,,,,,,,

1095

,,,,,8,,

1095

,,,,,,,

1095

,,,,,8,,

1247

,2,3,,,6,,,

1247

,2,3,,,6,,,

1247

,2,3,,,6,,,

1247

,2,3,,,6,,,

4069

,,,,,,,

4069

,,,,,,,

4069

,,,,,,,

4069

,,,,,,,

4070

,,6,,,,,

4070

,,6,,,,,

4070

,,6,,,,,

4070

,,6,,,,,

I want to query it and end up with the results all strung together under each UserID as follows:

1095

,,,,,,,,,,,,8,,,,,,,,,,,,,,8,,

1247

,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,

4069

,,,,,,,,,,,,,,,,,,,,,,,,,,,,

4070

,,6,,,,,,,6,,,,,,,6,,,,,,,6,,,,,

I know if I use the following code I can string all the results together

select *, Cast((SELECT Results + ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Results from #temp1

But I can't figure out how to break it down by individual UserID. Any help is greatly appreciated. Thanks in advance

If you are using SQL 2005, you could do something like this:

-- Create Comma Delimited list from multiple rows
-- From Tony Roberson
-- SQL 2005

DECLARE @.MailingList table
( IndividualName nvarchar(100) not null,
ListName nvarchar(10) not null
)

INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List A' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List B' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List C' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List A' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List B' )
INSERT INTO @.MailingList VALUES( 'Hector Lopez', 'List A' )

SELECT DISTINCT
IndividualName,
List = substring( ( SELECT ', ' + ListName as [text()]
FROM @.MailingList m2
WHERE m2.IndividualName = m1.IndividualName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MailingList m1

/*
Gives this result:

Alex R List A
Joe R List A, List B
Tony R List A, List B, List C

*/

|||

Outstanding thanks!!!

Final question, in the query

...FOR XML path(''), elements
), 3, 1000
)

what does the 3, 1000 do

|||

They are the second and third arguments for the substring function. They remove the first two characters from the final string (up to 1000 characters starting at the third).

The list is generated with ', ' in front of each member (i.e. ', a, b, c, d, e') whereas you do not want a comma/space in front of the first item so this removes the first ', ' from the front of the list.

|||Once again thanks I really appreciate the help|||

As Dhericean noted, those are parameters for the substring() function.

You could eliminate the space in [ SELECT ', ' ] and change the value 3 to 2. And you can increase or reduce the value 1000 to more closely represent the lenght of the final concatenated string.

|||Arnie -

A while back you provided me this solution to a need I had. Many thanks, I have use it often and with many tweeks. Now I am trying to do something similar and I am stuck, but I can't figure out why....

Here is our process and what is happening:

This is our preliminary query... it gets the group of raw data we will be analyzing (and it works)

SELECT SITEDATA.SITEID
, SITEDATA.ANOMALYT1 + ','
+ SITEDATA.ANOMALYT2 + ','
+ SITEDATA.ANOMALYT3 + ','
+ SITEDATA.ANOMALYT4 + ','
+ SITEDATA.ANOMALYT5 + ','
+ SITEDATA.ANOMALYT6 + ','
+ SITEDATA.ANOMALYT7 + ','
+ SITEDATA.ANOMALYT8 AS ANOMALIES
, SITEDATA.CALC_DATE, SITE.PROPID, SITE.LOCATION
INTO [#TEMP1]
FROM SITEDATA INNER JOIN SITE ON SITEDATA.SITEID = SITE.SITEID
WHERE (SITEDATA.SITEID = 907) AND (SITEDATA.CALC_DATE BETWEEN '1/1/06' AND '12/31/06')

Here is a sample of results from the above Query (Note, there are values in the Anomalies column):

SITEID Anomalies Calc_Date PropID Location
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5

Now we want to string all the data in the Anomalies column together by Distinct Calc_Date. To do this we execute the following Proc:

SELECT DISTINCT CALC_DATE, SITEID, PROPID, LOCATION, ANOMALIES = SUBSTRING( (
SELECT ',' + ANOMALIES AS [text()] FROM #TEMP1 T1 WHERE T1.SITEID = T2.SITEID FOR XML PATH(''), ELEMENTS ), 3, 1000 )
INTO #TEMP2
FROM #TEMP1 T2

This procedure strings the Anomalies together, but the data appears to be lost, i.e. we should get ,2,3,,,6,,,2,3,6,,,,2,3,,,6, etc. but instead all we get is ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

I thought the issue might be caused by date and time not being distinct, so I tried converting the date to varchar and rerunning, but no difference.... Any thoughts?|||

Jim,

Your query worked perfectly for me. (minor formatting alterations...)

SET NOCOUNT ON

DECLARE @.Temp1 table
( RowID int IDENTITY,
SiteID int,
Anomalies varchar(100),
Calc_Date datetime,
PropID int,
Location int
)

INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )

SELECT DISTINCT
Calc_Date,
SiteID,
PropID,
LOCATION,
Anomalies = substring( ( SELECT ',' + Anomalies AS [text()]
FROM @.Temp1 T2
WHERE T1.SITEID = T2.SITEID
FOR XML PATH(''), ELEMENTS
), 3, 1000
)
INTO #Temp2
FROM @.Temp1 T1

SELECT *
FROM #Temp2

Calc_Date SiteID PropID LOCATION Anomalies

-- -- --
2006-03-26 00:00:00.000 907 40 5 2,3,,,6,,,,,2,3,,,6,,,,,2,3,,,6,,,,,

(Anomalies repetition removed for display purposes...)

|||Thanks again Arnie.

I couldn't replicate your success, however I figured out a different query to and up with the same results. Because the DS is small, I just searched the Anomalies field without combining them, then I looked for distinct date. I think it ended up being faster anyway

concatinating string values from multiple rows

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:

CREATE TABLE [dbo].[TransactionTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MaskValue] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[HDResponse](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[LoggedDateTime] [datetime] NULL,
[ResponseTypeTripID] [int] NULL,
[ResponseTypeID] [int] NULL,
[ResponseTypeObjectID] [int] NULL,
[ObjectID] [int] NULL,
[IDHolderID] [int] NULL,
[TransportCode] [int] NULL,
[CardID] [int] NULL,
[IssueCode] [smallint] NULL,
[EventDateTime] [datetime] NULL,
[Response] [bit] NULL,
[TranTypeID] [int] NULL)
ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

What version of SQL Server are you using? SET CONCAT_NULL_YEILDS_NULL is ON by default unless you have changed the settings after connecting to SQL Server. This is true for any connections made via ODBC/OLEDB. You can use the PIVOT operator in SQL Server 2005 to solve this problem. You can also do this using a standard SQL technique like below:

select max(case p.trantype when 1 then p.description else '' end) +
max(case p.trantype when 2 then ' ' + p.description else '' end) +
max(case p.trantype when 4 then ' ' + p.description else '' end) +
max(case p.trantype when 8 then ' ' + p.description else '' end) +
max(case p.trantype when 16 then ' ' + p.description else '' end) as "Transaction Type"
from (
select t.description, (t.MaskValue & h.TranTypeID) as trantype
from HDResponse as h
join transactiontypes as t
on (t.MaskValue & h.TranTypeID) in (1, 2, 4, 8, 16)
) as p

concatenation of '0' + converted interger value into a string?

I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts the
integer, my results remain unchanged. I would appreciate any assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not nullHi Jeff,
You can strip the monthpart out of string representation of a date, that
will always include a leading zero when necessary, so you don't have to
worry about that, for example:
SELECT CONVERT(CHAR(2), trandate, 1)
FROM Offtable where trandate is not null
Style 1 with convert returns mm/dd/yy, and we are only interested in the
leftmost two characters, so a CHAR(2) will do.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am unsure as to why I cannot concatenate '0' with the when '1' case
> statement below. Even thought the convert statement explicitly converts
the
> integer, my results remain unchanged. I would appreciate any
assistance...
>
> SELECT
> case LEN(datepart(m,trandate))
> when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
> when '2' then DATEPART(M,trandate)
> end
> FROM Offtable where trandate is not null
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
There I go again:
select
replace (str (datepart (mm, TranDate), 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OEKO955dDHA.1152@.TK2MSFTNGP11.phx.gbl...
You can rewrite the statement:
select
replace (str (TranDate, 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message =news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts =the
integer, my results remain unchanged. I would appreciate any =assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not null
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

There I go again:
select
replace (str =(datepart (mm, TranDate), 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau" = wrote in message news:OEKO955dDHA.1152=@.TK2MSFTNGP11.phx.gbl...
You can rewrite the =statement:
select
replace (str =(TranDate, 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Humphrey" wrote in message news:uYhK335dDHA.1636=@.TK2MSFTNGP12.phx.gbl...I am unsure as to why I cannot concatenate '0' with the when '1' =casestatement below. Even thought the convert statement explicitly converts theinteger, my results remain unchanged. I would appreciate =any assistance...SELECT case LEN(datepart(m,trandate)) when '1' then '0' =+ CONVERT(varchar(1),DATEPART(M, trandate)) =when '2' then DATEPART(M,trandate) end FROM Offtable where =trandate is not null

---

--=_NextPart_000_0181_01C37783.62DF5350--

Concatenation in stored procedure

hi everyone
i am writing a stored procedure and i am new in this field
i want to concatenate a string, i got an error
hope someone could help.
my stored procedure is:
CREATE PROCEDURE dbo.ProdCatComp
(
@.Product nvarchar(40),
)
AS
DECLARE @.str nvarchar(100)
SET @.str='Products.ProductName like '%' + @.Product + '%''

SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.HomePage
FROM Products INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName<>'' AND @.str
GO

The error is :
Error 403: Invalid operator for data type. Operator equals modulo, type equals varchar.you have to make the whole query dynamic
and then use exec/sp_executesql|||luber is correct in most cases you will need to build a sql string and then execute it, but in this particular case the code below should work - not tested.

CREATE PROCEDURE dbo.ProdCatComp
 @.Product nvarchar(40)
AS
 DECLARE @.str nvarchar(100)
 SET @.str = '%' + @.Product + '%'
 SELECT Products.ProductName,
 Products.UnitPrice,
 Categories.CategoryName,
 Suppliers.CompanyName,
 Suppliers.ContactName,
 Suppliers.HomePage
 FROM Products
 INNER JOIN Suppliers
 ON Products.SupplierID = Suppliers.SupplierID
 INNER JOIN Categories
 ON Products.CategoryID = Categories.CategoryID
 WHERE Products.ProductName <> ''
 AND Products.ProductName LIKE @.str
GO

concatenation but with a format...

Hi Consider a table with a decimal and a string varibale.
Dec String
3 fred
23 bill
I need to concatenate them but with leading zeros such as
03Fred
23Bill
Ideas please ??
GerryAs long as you don't need to cope with negative numbers, you can get by with the simple solution of:SELECT Replace(Str([Dec], 2), ' ', '0')
FROM dbo.myTable-PatP|||drop table #tmp
create table #tmp(id int,col1 varchar(10))
go
insert #tmp(id,col1) values(1,'a')
insert #tmp(id,col1) values(11,'b')
insert #tmp(id,col1) values(111,'c')
insert #tmp(id,col1) values(1111,'d')
insert #tmp(id,col1) values(11111,'e')
insert #tmp(id,col1) values(23,'f')
go
select replicate('0',5-len(cast(id as varchar)))+cast(id as varchar)+' '+col1 from #tmp|||Both worked nicely. Thanks a lot

Gerry

concatenation

I am trying to concatenate a long string together ad it keeps truncating how
can I extend the length that is allowed to truncate?
TIAHow do you it is being truncated?
Can you post the code?
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||maybe truncated wasn't the correct term. The string is being cut off at the
end...
What I want to see is something like "this<>is<> a<>long<> string"
What is get is "this<> is<> a<> lon"
and that's it
"Alejandro Mesa" wrote:
> How do you it is being truncated?
> Can you post the code?
>
> AMB
> "JMNUSS" wrote:
>|||How long a string?
Where are you doing the concatenation?
Show us some code.
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:061DB1FF-1529-45B7-B51E-8627A6162936@.microsoft.com...
> I am trying to concatenate a long string together ad it keeps truncating
how
> can I extend the length that is allowed to truncate?
> TIA|||No, you can not do that. You can insert the result of the sp into a table an
d
then process the table, or you can rewrite the sp as a table-valued user
defined function, or you can call the sp using rowset function.
Example:
use northwind
go
create table #t (
Shippeddate datetime,
OrderID int,
Subtotal money,
col_Year int
)
insert into #t
execute dbo.[Sales by Year] '19960101', '19961231'
select
*
from
#t
where
Subtotal between 1200.00 and 1500.00
drop table #t
go
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||Are you viewing this in Query Analyser?
If so: Tools/Options/Results and change the "Maximum characters per column"
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:B1036631-B165-4251-AF5B-A6C3777C0705@.microsoft.com...
> maybe truncated wasn't the correct term. The string is being cut off at
the
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>
truncating how|||Sorry.
AMB
"Alejandro Mesa" wrote:
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>|||> What is get is "this<> is<> a<> lon"
Where do you get this, in your client app?
If you are using SQL Query Analyzer and the result is being truncated, go to
tools - Options - Results and change "Maximum characters per column:" (max
number is 8192).
AMB
"JMNUSS" wrote:
> maybe truncated wasn't the correct term. The string is being cut off at t
he
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>|||Heh Alejandro, get in the right thread...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:58BD3B7D-A23E-44BC-9008-4276A8B7DE58@.microsoft.com...
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>
how

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 string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
--
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
> > When executing the following statements:
> > declare @.x char(10), @.y char(10)
> > set @.x = 'abc'
> > set @.y = @.x + 'def'
> > select @.x
> > select @.y
> > the results are:
> > abc
> > abc
> > I expect @.y should be equal to 'abcdef'. If I change the var types to
> > int for example, then @.y is summed correctly. Can anyone tell me why,
> > or what I'm doing wrong? Thanks.
> > Dan
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
Dan
On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.
|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegro ups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>
|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan
|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.