connect to another server from a stored proc

I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?

thanks
D.found my answer..used OPENDATASOURCE.

thanks for anyone that tried.

Confusing layout in SSIS with regard to "Execute SQL Task".

I hope someone can help.

I'm trying to read rows from a SQL Server Table and for each row use a few columns as parameters into a query to be run against oracle which will delete oracle rows.

I add OLDEB connections for Oracle and SQL and then I try to add a "Execute SQL Task". I've also tried a "OLE Command" but I can't get the mapping of the columns to the parameters to work.

There is lots of articles on the web that talk in general around parameterized queries but no clear examples.

I also find the difference between the Control Flow and Data Flow tabs confusing as its not intuitive where to place things. It also appears to force me to re-define things that it should already know (this is no doubt because I'm interpreting what I've done / acheived wrongly).

I have my source and destination on the "Data Flow" tab along with a "Execute SQL Task" object in the middle.

I'm setting its "connection manager" the Oracle (i.e. the destination where I want the deletes to be executed). I don't follow why this also has a "connection property, surely this it set when I drag the output of the SQL Server OLEDB Source to the input of the "Execute SQL Task".

Perhaps I'm expected too much from the wizards / dialogs and I have to create "variables" and "parameters" myself?

Any help or suggestions would be very much appreciated.

Thanks in advance

Craig

Scotland

Craig,

In general, you can think in the control flow as the one responsible for the workflow of the package; it could be also use to perform batch operations against a table. In you case, for example you could issue a delete statement using an execute sql task against the Oracle table to delete all rows at once. In order to do so, you would need to have a staging table that holds the rows that need to be delete.

The DataFlow; is deemed to move data and perform operations in a row-by-row basis. You could solve your problem by using an OLE DB command transformation to perform the delete. The drawback with this approach, it is that the delete statement would be performed for every row in the dataflow pipeline; so the performance is affected considerably.

If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction.

In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement.

I hope this helps you

|||

Thanks,

Some comments.

"If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction."

One table exists in SQL Server, then other in Oracle, does you comment still apply?

"In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement."

MUST I do this. This does not appear to me to be in the nature of a parameterized query? Would this method still use "Prepared Statements" ?

Lastly, I'm still stuck in that my real issue is on how to map input data (from SQL) to parameters (to Oracle), I think if I followed your example, I'd still have that same problem only now I would be trying to map to a variable that was my entire query instead of just a parameter?

Thanks for the help so far and best regards

Craig

|||

Can you create and populate a staging table in the Oracle side? If so, you could load all the data in the SQL server table into a staging table in the Oracle side and then use an execute sql task to perform a 1 time update. This is the way I do this kind of things because it's more efficient performance wise.

If that is not possible; I guess you have to stick with the data flow/OLE DB command approach. But I cannot help with that as I don't have an Oracle instance to test how the parameters get mapped.

Confused By Error Msg

I am using a VB aspx page to pass a parameter to a stored procedure and to
get two parameters returned. The input parameter is an integer, one of the
output parameters is also and integer and the other output parameter is an
nvarchar. When I run the execute command I get an error:
Implicit conversion from data type sql_variant to nvarchar is not allowed.
Use the CONVERT function to run this query.
I have no idea where the sql_variant type comes from? Any thoughts?
Here is the portion of my code that makes the call followed by the SP code.
========================================
=========
Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
cmd4.CommandType = CommandType.StoredProcedure
cmd4.Parameters.Add("@.id", SqlDbType.Int)
cmd4.Parameters("@.id").Value = Request.Params("ID")
cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
cmd4.Parameters("@.url").Direction = ParameterDirection.Output
cmd4.ExecuteNonQuery()
=========== SP Code ====================
ALTER Procedure GetAdClicks
(
@.id int,
@.clickcount int OUTPUT,
@.url nvarchar(100) OUTPUT
)
AS
SELECT @.clickcount = clickcount, @.url=url
FROM adserver
WHERE id=@.idWayne,
What is the data type for the URL column in the adserver table?
EXEC SP_COLUMNS AUTHORS
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>I am using a VB aspx page to pass a parameter to a stored procedure and to
>get two parameters returned. The input parameter is an integer, one of the
>output parameters is also and integer and the other output parameter is an
>nvarchar. When I run the execute command I get an error:
> Implicit conversion from data type sql_variant to nvarchar is not allowed.
> Use the CONVERT function to run this query.
> I have no idea where the sql_variant type comes from? Any thoughts?
> Here is the portion of my code that makes the call followed by the SP
> code.
> ========================================
=========
> Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
> cmd4.CommandType = CommandType.StoredProcedure
> cmd4.Parameters.Add("@.id", SqlDbType.Int)
> cmd4.Parameters("@.id").Value = Request.Params("ID")
> cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
> cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
> cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
> cmd4.Parameters("@.url").Direction = ParameterDirection.Output
> cmd4.ExecuteNonQuery()
>
> =========== SP Code ====================
> ALTER Procedure GetAdClicks
> (
> @.id int,
> @.clickcount int OUTPUT,
> @.url nvarchar(100) OUTPUT
> )
> AS
> SELECT @.clickcount = clickcount, @.url=url
> FROM adserver
> WHERE id=@.id
>
>|||It is nvarchar
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
> Wayne,
> What is the data type for the URL column in the adserver table?
> EXEC SP_COLUMNS AUTHORS
> HTH
> Jerry
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
What happens if you run the sproc from Query Analyzer?
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
> It is nvarchar
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
>|||Jerry;
When I run it from QA everything seems fine, I get the count as a number and
the url is a string
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
> Wayne,
> What happens if you run the sproc from Query Analyzer?
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
Ok...that probably removes SQL as the cause. Might want to follow this up
with a programming NG for the programming language you're using.
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
> Jerry;
> When I run it from QA everything seems fine, I get the count as a number
> and the url is a string
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
>|||OK - will do.
Thanks for the help.
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:upfzonY2FHA.3416@.tk2msftngp13.phx.gbl...
> Wayne,
> Ok...that probably removes SQL as the cause. Might want to follow this up
> with a programming NG for the programming language you're using.
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
>

Confirming the right hyperlink

Hi,
We create rdl files which contains <Hyperlink> elements. The
value for hyperlink is an expression build using report parameters,
field parameters etc and also contains calls to custom assemblies
eg : <Hyperlink>=Parameters!Param1.Value + CustomAssemblyCall + Fields!
someField.Value<Hyperlink>
When this report is rendered by HTML Rendering extension(by specifying
HTML4.0 in the format string passed to render() method) this
<Hyperlink> element is converted to href attribute of the <a> tag.
We have a scenario where we want to test whether this href url
generated is correct or no without deploying the rdl file on the
report server.
Is there any way to pass the xml file and find out what would be href
value when the <Hyperlink>
element is parsed by the report server ?
Any report server dll's which can directly be used ?
Thanks in advance,Can anyone guide me on this

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
Zeke
It depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.co...n_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resources
>for example.
>Zeke
>
|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.co...n_settings.asp[vbcol=seagreen]
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
> <ezekiel@.lycos.nl> wrote:
resources
>
|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com.. .
>http://www.sql-server-performance.co...n_settings.asp
>resources
>
|||Ezekil,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/d...displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekil wrote:

> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resources
> for example.
> Zeke
>

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
ZekeIt depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.c...on_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resource
s
>for example.
>Zeke
>|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.
4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.c...on_settings.asp
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekil"
> <ezekiel@.lycos.nl> wrote:
>
resources[vbcol=seagreen]
>|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekil"
<ezekiel@.lycos.nl> wrote:

>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.
4ax.com...
>http://www.sql-server-performance.c...on_settings.asp
>resources
>|||Ezekil,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/...&displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekil wrote:

> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resourc
es
> for example.
> Zeke
>

configuring sql server (best practice)

Hello,
I was wondering how i could optimize sql server for best practices.
I have looked at the parameters, but what can i do with the memory resources
for example.
ZekeIt depends on what version of SQL Server and what specific
issues you are seeing or are trying to address. For the most
part, you'd want to leave the server configuration settings
alone on SQL Server 7 and above. There are some situations
where modifying the default settings can help but these are
generally implemented to address specific issues and should
be thoroughly tested before implementing on a production
server. You can find some information on the server
configuration settings in the following articles:
HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
Tips for Performance Tuning
SQL Server's Configuration Settings
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
-Sue
On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
<ezekiel@.lycos.nl> wrote:
>Hello,
>I was wondering how i could optimize sql server for best practices.
>I have looked at the parameters, but what can i do with the memory resources
>for example.
>Zeke
>|||Hi Sue,
I'm working with sql server 2000 and it runs with other large dbms's like
oracle. What i would like to achieve is finetune the server so that both
databases runs fully optimized.
Greetings,
Zeke
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
> It depends on what version of SQL Server and what specific
> issues you are seeing or are trying to address. For the most
> part, you'd want to leave the server configuration settings
> alone on SQL Server 7 and above. There are some situations
> where modifying the default settings can help but these are
> generally implemented to address specific issues and should
> be thoroughly tested before implementing on a production
> server. You can find some information on the server
> configuration settings in the following articles:
> HOW TO: Determine Proper SQL Server Configuration Settings
> http://support.microsoft.com/?id=319942
> Tips for Performance Tuning
> SQL Server's Configuration Settings
>
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
> -Sue
> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
> <ezekiel@.lycos.nl> wrote:
> >Hello,
> >
> >I was wondering how i could optimize sql server for best practices.
> >
> >I have looked at the parameters, but what can i do with the memory
resources
> >for example.
> >
> >Zeke
> >
>|||You wouldn't necessarily use the server configurations to
fine tune things. The default settings work fine in most
cases, changing the settings just to try tweaking things
generally causes more problems. Server settings generally
isn't the first place to go in trying to tuning things up.
You'd want to look at the applications using the databases,
run profiler, pay to indexing/index usage, do overall
monitoring to check for potential server bottlenecks using
PerfMon, etc.
You may want to go through some of the articles at:
www.sql-server-performance.com
-Sue
On Tue, 1 Jun 2004 14:27:12 +0200, "Ezekiël"
<ezekiel@.lycos.nl> wrote:
>Hi Sue,
>I'm working with sql server 2000 and it runs with other large dbms's like
>oracle. What i would like to achieve is finetune the server so that both
>databases runs fully optimized.
>Greetings,
>Zeke
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4osob0t3i2t96uguuv72feb95e2l1n7k29@.4ax.com...
>> It depends on what version of SQL Server and what specific
>> issues you are seeing or are trying to address. For the most
>> part, you'd want to leave the server configuration settings
>> alone on SQL Server 7 and above. There are some situations
>> where modifying the default settings can help but these are
>> generally implemented to address specific issues and should
>> be thoroughly tested before implementing on a production
>> server. You can find some information on the server
>> configuration settings in the following articles:
>> HOW TO: Determine Proper SQL Server Configuration Settings
>> http://support.microsoft.com/?id=319942
>> Tips for Performance Tuning
>> SQL Server's Configuration Settings
>http://www.sql-server-performance.com/sql_server_configuration_settings.asp
>> -Sue
>> On Tue, 1 Jun 2004 13:59:53 +0200, "Ezekiël"
>> <ezekiel@.lycos.nl> wrote:
>> >Hello,
>> >
>> >I was wondering how i could optimize sql server for best practices.
>> >
>> >I have looked at the parameters, but what can i do with the memory
>resources
>> >for example.
>> >
>> >Zeke
>> >
>|||Ezekiël,
Have you looked into Best Practices Analyzer? It has just been released:
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Tinyurl:
http://tinyurl.com/35cb4
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Ezekiël wrote:
> Hello,
> I was wondering how i could optimize sql server for best practices.
> I have looked at the parameters, but what can i do with the memory resources
> for example.
> Zeke
>

configuring dataset dynamically

HI ,

I am using SQL Server 2005 Reporting Services. I have many parameters to pass to the dataset. Is there a way to change the dataset dynamically based on the parameters selected?

Suppose If param1 is selected, I want to use dataset1 and if param 2 is selected. I want to use dataset2 and so on... in my reports.

Any help is greately appreicated!

Thanks in advance!

Have you tried to use an expression in your dataset "Query String" window?

Example:

iif(param1 <> nothing,exec proc1,exec proc2)

|||

It is better to handle it in your stored procedure which takes all necessary parameters.

Shyam

|||

Hi Simone,

I have handled this problem in my stored procedure on SQL Server. I have created separate procedures and created a master procedure and called other sub procedures inside this master procedure based on the parameters selected on the UI. I couldn't think of this approach until you suggested to handle it in dataset expression. Since I have many parametes to handle, dataset expression didn't work for me but the idea helped me to figure out other solution.

Thank you so much for your help!

|||

Hi Shyam Sundar,

I have tried it in dataset expression as Simone mentioned and didn't work and tried in stored procedure. I agree it was better to handle it in stored procedure.

Thank you very much for suggestion.

|||Can you pls mark my post as answer?|||I did. Thanks again for your help.

configuring dataset dynamically

HI ,

I am using SQL Server 2005 Reporting Services. I have many parameters to pass to the dataset. Is there a way to change the dataset dynamically based on the parameters selected?

Suppose If param1 is selected, I want to use dataset1 and if param 2 is selected. I want to use dataset2 and so on... in my reports.

Any help is greately appreicated!

Thanks in advance!

Have you tried to use an expression in your dataset "Query String" window?

Example:

iif(param1 <> nothing,exec proc1,exec proc2)

|||

It is better to handle it in your stored procedure which takes all necessary parameters.

Shyam

|||

Hi Simone,

I have handled this problem in my stored procedure on SQL Server. I have created separate procedures and created a master procedure and called other sub procedures inside this master procedure based on the parameters selected on the UI. I couldn't think of this approach until you suggested to handle it in dataset expression. Since I have many parametes to handle, dataset expression didn't work for me but the idea helped me to figure out other solution.

Thank you so much for your help!

|||

Hi Shyam Sundar,

I have tried it in dataset expression as Simone mentioned and didn't work and tried in stored procedure. I agree it was better to handle it in stored procedure.

Thank you very much for suggestion.

|||Can you pls mark my post as answer?|||I did. Thanks again for your help.