How to change the connection string in a web.config file

I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?

Jeffrey Way

<connectionStrings>
<add name="<CONNECTIONSTRINGNAME>" connectionString="Data Source=<SERVERAME or IPADRESSPROVIDED BY HOST>;Initial Catalog=<DATABASENAME>;User ID=<USERNAME>;Password=<PASSWORD>" providerName="System.Data.SqlClient"/>
</connectionStrings>|||

<

connectionStrings>

<

clear/>

<

addname="LocalSQLServer"connectionString="Data Source=serverName;Initial Catalog=DatanaseName;Persist Security Info=True;User ID=sa;Password=sa"/></connectionStrings>

Hope this is what ur looking for

How to change ConnectionString programmaticaly for report

Hi!
Is there any way to change the connecting string of a report data source
programmatically at run time. Actually my problem is that I have an ASP.NET
application and connection string is stored in Web.Config file. I have
multiple copies of databases hosted on different SQL Server and I update my
web.config connection string to switch between these databases. I just want
to use this connection string for my reports also. I am using SQL Server
authentication and user name and passwords are different for different
servers.
Please help me as this is becoming a show stopper for my application.
Regards,
NamwarOn Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an ASP.NET
> application and connection string is stored in Web.Config file. I have
> multiple copies of databases hosted on different SQL Server and I update my
> web.config connection string to switch between these databases. I just want
> to use this connection string for my reports also. I am using SQL Server
> authentication and user name and passwords are different for different
> servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
This link should be helpful.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/53e96ed5cde45213/bbe61adc20aeeb87?lnk=st&q=dynamic+datasource+reporting+services&rnum=1#bbe61adc20aeeb87
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 9, 3:50 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> > Hi!
> > Is there any way tochangethe connecting string of areportdata source
> > programmatically at run time. Actually my problem is that I have an ASP.NET
> > application and connection string is stored in Web.Config file. I have
> > multiple copies of databases hosted on different SQL Server and I update my
> > web.config connection string to switch between these databases. I just want
> > to use this connection string for my reports also. I am using SQL Server
> > authentication and user name and passwords are different for different
> > servers.
> > Please help me as this is becoming a show stopper for my application.
> > Regards,
> > Namwar
> This link should be helpful.http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I received your email. The only other thing I can think of is to
create the RDL file and/or the datasource file for the report
programmatically via a custom ASP.NET application.|||Have you tried using an expression as a datasource, as described here
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
... look for the section on "dynamic datasources" or "expressions" or
something like that.
I do understand that you want to read your stuff out of the web config file.
But there are several ways you probably could handle this -- without
programmatically altering the RDL file -- assuming the basic idea of a
datasource based on an expression will work for you. To start with, how are
the reports actually invoked (in the asp.net application? or elsewhere?) and
what access does reporting code have to the web.config file and its
contents?
>L<
"Namwar Rizvi" <namwar@.hotmail.com> wrote in message
news:efkKU0hqHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an
> ASP.NET application and connection string is stored in Web.Config file. I
> have multiple copies of databases hosted on different SQL Server and I
> update my web.config connection string to switch between these databases.
> I just want to use this connection string for my reports also. I am using
> SQL Server authentication and user name and passwords are different for
> different servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
>

How to change Connectionstring in SQLCLR project?

Hi,

I have created a SQLCLR (database) using C#.. named SQLCLRtest

The Connection string is stored in SQLCLRtest.csproj file

here is code of csproj file

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<AssemblyOwner>
</AssemblyOwner>
</PropertyGroup>
<ProjectExtensions>
<VisualStudio>
<FlavorProperties GUID="{c252feb5-a946-4202-b1d4-9916a0590387}">
<DatabaseConnection Name="Data Source=DBserver;Initial Catalog=TestDB;Integrated Security=True" Provider="{91510608-8809-4020-8897-FBA057E22D54}" ConnectionString="01000000D08C9DDF0115D1118C7A00C04FC297EB01000000A2744997FFD51E459D0421E51E830EF30000000002000000000003660000A8000000100000005B59ACF96DA2A587CBFA27595B0F245E0000000004800000A000000010000000BB1D5F562AC3FE7F56F8E57C36E0E7A4B0000000CE828F399233A389D95E2D99B2CAA64DE5F5A19EF0CBB716D195DF60EE38B58B0C07674E2F80538C02ED27200C79A71B0F6F9177E598089CDA95B8DDEEF966A958C6EDE4E72CABBC39941FEED534E3384EF3A4B4A51704726BF5D43F2C3C9BD674885B9675FECD86E54498ED9E1957FCD7DCF0CE8ED99C8529FD9234EB4E760FDD6819E3E42A7771E0A5B18452C01C13976C0DDDF1B5B87D75F0490762C6A004AD093A3DF9210F7D03371D67E4901EB51400000005557E36590040C06F796463ABFEC165D2E60750" />
</FlavorProperties>
</VisualStudio>
</ProjectExtensions>
</Project>

Problem:

I have an sp which start an external process, It is working at my local machine but does not work at remote server.

FACTS:

Local machine has SQLExpress (SQL version) while remote machine has SQL Enterprise ..

SQL service is running under the System Account at both machines..

I have enabled the Sql server Service to interact with desptop, so that it can start a process in GUI mode.

I thin there is something in connectionstring which does not allow the application to connect to server with appropriate rights.

How I can chage this setting .. is it possible to write this info manually..

Please, comments

Thanks

You should be able to change the connection string from the database tab in project properties. When you click on browse, It will allow you to choose from different existing connections or add a new reference.

Thanks,

-Vineet

|||

Muna,

Did Vineet's post solve your problem? The reply is correct to my knowledge.

Derek

How to change a string

Hi,

I have a string like (Mike,David,John...). How can I change this string into a proper syntax of

('Mike','David','John'...) so that I can use it in an "IN" statement some where else.

Select * from TableName where Customer_Name In ('Mike','david','john',...)

Any function? Any Idea?

Thanks

An easier method might be to create a parameter called CustomerName and then use the following code:

Select * from TableName where Customer_Name IN(@.CustomerName)

|||

won't work, he needs to put the single quote on them...

Search for a user on this forum called Lisa Nicholls. she posted a solution for someone with a similiar issue, She even posted the code to use.

|||

With my example he would not have to worry about the quotes as the SQL operation addresses internally using the parameter. I have several of my reports built this way which allows me to avoid the quote issue.

|||

Thanks every one for the feed back. I have created the below function and it work fine.

CreateFUNCTION [dbo].[fn_StringWithQuotes] (@.InputString Varchar(8000))

RETURNSnvarchar(4000)

AS

BEGIN

DECLARE @.IDTable TABLE(Item Varchar(255))

DECLARE @.item VARCHAR(255)

WHILE(DATALENGTH(@.InputString)> 0)ANDRIGHT(SUBSTRING(@.InputString,1,CHARINDEX(',',@.InputString)),1)=','

BEGIN

SET @.item =SUBSTRING(@.InputString,1,(CHARINDEX(',', @.InputString)-1))

INSERTINTO @.IDTable(Item)VALUES(@.Item)

SET @.InputString =SUBSTRING(@.InputString,(CHARINDEX(',', @.InputString)+1),DATALENGTH(@.InputString))

END

INSERTINTO @.IDTable(Item)VALUES(@.InputString)

DECLARE @.NAME VARCHAR(255)

DECLARE @.NEW_NAME VARCHAR(255)

SET @.NEW_NAME=''

SELECT @.NEW_NAME = @.NEW_NAME +''','''+A.ITEM FROM(SELECT ITEM FROM @.IDTable)A

SET @.NEW_NAME=RIGHT(@.NEW_NAME,LEN(@.NEW_NAME)-2)+''''

RETURN @.NEW_NAME

END

How to cast empty string

I want to replace a column value with a null if the string is empty. I would have thought this simple expression would do it:

RTRIM([FromContractSymbol]) == "" ? NULL(DT_STR, 0, 1252) : (DT_STR, 6, 1252)FromContractSymbol

Yet, I get the following error:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.

The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")

The error does explain how to solve it. Although you have specified the type for the NULL you have to cast it.

So if you change your line to

RTRIM([FromContractSymbol]) == "" ? (DT_STR, 6, 1252)NULL(DT_STR, 6, 1252) : (DT_STR, 6, 1252)FromContractSymbol

It should work

How to cast a numeric database field to character

Hello.
I have a report and need to concatenate two numeric database fields, a month
and a year, into a string with a slash (/) between them and put it on the
report header.
How can I do this?
Thanks in advance,
MikeUse CStr(Month) & "/" & CStr(Year)
"MikeL" wrote:
> Hello.
> I have a report and need to concatenate two numeric database fields, a month
> and a year, into a string with a slash (/) between them and put it on the
> report header.
> How can I do this?
> Thanks in advance,
> Mike
>
>

How to calculate number of occurances of a character in a string

Is there a string function in T-sql which tells us that the number of occurances of a character in a string?
eg. abracadabra
no. of a's in the string : 5
Any Help is appreciated.select len(@.YourString)- len(replace(@.YourString, 'a', ''))

blindman

How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.

How to build an string alias in T-SQL

I have the following stored procedure:

SELECT

SERVER_NAME,

SERVICE_PORT,

SERVER_NAME + ',' + SERVICE_PORT

ASSERVER_AND_PORT

FROM

DEF_SERVICE_SETTINGS

I want the 3rd column to be in format: SERVER_NAME + "," + SERVICE_PORT

But SQL gives error. Sees "," as column.

How can I fix this?

Which database and language you are using?|||

SQL-server

Column SERVER_NAME is varchar type
Column SERVICE_PORT is int type

|||

My code works when both type of columns are of varchar, but the SERVICE_PORT column is of int type.

Can I cast the int type to a varchar type in some way?

|||

Found it:

SERVER_NAME +','+CAST(SERVICE_TCP_PORTAs varchar(1000))ASSERVER_AND_PORT

|||

Yes you have to Cast.

SERVER_NAME + ',' + Cast(SERVICE_PORT as varchar) AS SERVER_AND_PORT

|||convert(varchar(50), SERVICE_PORT)

HTH

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> > Pieter,
> >
> > Do you now need Bella because you was yesterday to much involved with
> > Stella.
> > I count 4 newsgroups, not 2.
> >
> > However your problem sounds not difficult for me, what I not direct see,
> is
> > if the tblMycows is a datatable or that it is a table in a database?
> >
> > Cor
> >
> >
>|||Pieter,
> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
http://groups-beta.google.com/group/microsoft.public.dotnet.languages.vb/msg/470d93378c5467f8?hl=en
Cor

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> is
>|||Pieter,

> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
3378c5467f8?hl=en" target="_blank">http://groups-beta.google.com/group...78c5467f8?hl=en
Cor

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
Pieter
Pieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor
|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>
|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>
|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> is
>
|||Pieter,

> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor
|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
http://groups-beta.google.com/group/...8c5467f8?hl=en
Cor

How to build a query into string variable and run it

Hi,

I need to to run an sql query that is stored in string variable, like this:

DECLARE @.wherestr nvarchar

DECLARE @.sqlstring nvarchar

SET @.wherestr = .... some more code to fill @.wherestr

SET @.sqlstring =

'SELECT @.tgid = tgid '+

' FROM target_groups '+

' WHERE '+@.wherestr

But when I call this command using sp_executesql:

EXEC sp_executesql @.SQLString

I recieve following error: "Only functions and extended stored procedures can be executed from within a function."

Is there any possibility to run the command that is stored in some string (nvarchar) variable inside the function?

Note: I'm using SQL Server 2005 Express, Management Studio

thanks for any help

Jiri Matejka

Jiri,

I don't see where you are trying to declare any function.

You can use

EXEC( @.sqlstring)

I am also not sure what you get when you declare something as "nvarchar". Perhaps just "nvarchar(1)". So maybe you want "nvarchar(8000)" or something like that. I have seen others use "nvarchar(max)", which I think is related to some "max" value used when setting up the SQL Server software.

Here is what MS documentation says about "nvarchar" without the size declaration:

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

When n is not specified in a data definition or variable declaration statement, the default length is 1.

The MS terminology for this is DYNAMIC SQL. You can search this forum for it, as well as Microsoft documentation. There are some excellent internet articles with caveats concerning the use of DYNAMIC SQL. You will find links to these in various posts in this forum.

HTH.

Dan

|||

The function I wrote about is something like this

CREATE FUNCTION [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
RETURNS int
AS
BEGIN
DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr

DECLARE @.sqlstring nvarchar(255)

some more code here ...

SET @.sqlstring = 'SELECT @.tgid = tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr

EXEC sp_executesql @.SQLString

IF @.tgid IS NULL
SET @.tgid=-1

RETURN @.tgid
END

There is no problem with nvarchar, I use it like "nvarchar(255)", I've just shortened the code to be more readable.

Thanks for your help, but when I call EXEC @.SQLstring, that it causes error of "

Could not find stored procedure 'SELECT @.tgid = tgid FROM target_groups WHERE sex=2 AND agecat=32'". So it looks like if EXEC wants to run stored procedure, not given command. But your reference to "DYNAMIC SQL" is a good hint. I'll check it out.

Jiri Matejka

|||

You may not be able to use EXEC(@.SQLString) inside of a function. (But I seem to recall instances where I do just that.)

I think you also need some "N" in front of your strings you are placing in your NVARCHAR variables, as shown in http://msdn2.microsoft.com/en-us/library/ms188001.aspx .

I hope that will work for you. I don't see anything wrong with the SQL statement.

Dan

|||

You cant use dynamic SQL (sp_executesql or Exec ()) on function.

Change your logic to SP.

Possible Alternative,

Create Temp Table on calling proc

Insert data on Callable Proc on the Created Temp table

After the calling use the temp table on your query.

Create Temp table on calling proc

Insert the callable Proc output in Temp table

Use it on your rest of code.

|||i think its not possible cannot use sp_executesql inside a function

what you can do is try to convert your function into a procedure

Code Snippet

CREATE PROCEDURE [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
AS

DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr

DECLARE @.sqlstring nvarchar(255)
some more code here ...

SET @.sqlstring = 'SELECT ISNULL(tgid,-1) AS tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr
EXEC sp_executesql @.SQLString
GO

you can get your result by

Code Snippet

INSERT

INTO #TGIDResult

EXEC GetTargetGroup @.theSex, @.theAge

SELECT @.tgid = tgid

FROM #TGIDResult

DROP TABLE #TGIDResult

|||

The issue with this technique is that the @.SQLString executes in a separate scope. Here's how you can pass results from the @.SQLString back to your code:

DECLARE @.wherestr varchar(8000) --in SQL 2005, you may use varchar(max)

DECLARE @.sqlstring varchar(8000)

declare @.tgid bigint

--Create a temp table to hold results: in 2005, you can use @.Table rather than #Table if you prefer

select @.tgid as TG_ID into #TGID where 1 = 2

SET @.wherestr = .... some more code to fill @.wherestr

SET @.sqlstring =

'SELECT @.tgid = tgid '+

' FROM target_groups '+

' WHERE ' + @.wherestr

insert into #TGID

exec(@.SQLString)

select * from #TGID

drop table #TGID