Passing table variable as input parameter to stored proc

I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>

passing SELECT result to OS

In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
renamed only a daily basis, based the current datestamp, through T-SQL. So
far, I've come up the following T-SQL stmt:
SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
...How would I be able to implement the result of the above stament (in the
case, Feb232006) to be the new folder name, i.e., take the result of the SQL
select stmt. and pass it to the OS to rename a folder?
Thanks.DECLARE @.cmd VARCHAR(255);
SET @.cmd = 'ren "d:\abc\" "foo"';
EXEC master..xp_cmdshell @.cmd, NO_OUTPUT;
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.|||"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D2B570A3-A31A-4C80-A5E2-44ABD38E52BE@.microsoft.com...
> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in
> the
> case, Feb232006) to be the new folder name, i.e., take the result of the
> SQL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.
Lookup xp_cmdshell in Books Online. Why do it in SQL though?
Your naming convention for folders looks very inconvenient. The user will
see the months sorted alphabetically in Explorer instead of chronologically.
The years will be mixed up too! Better to do:
\2006_02_23
\2006_02_24
\... etc
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It's okay... I figured it out myself by using the following code:
declare @.date varchar(10)
declare @.cmd varchar(250)
set @.date = RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
getdate())+DATENAME(year, getdate()))
Print @.date
set @.cmd = 'REN X:\ABC ' + @.date
Print @.cmd
"Rob" wrote:

> In Windows, I have a folder, lets say 'ABC'. I'd like this folder to be
> renamed only a daily basis, based the current datestamp, through T-SQL. So
> far, I've come up the following T-SQL stmt:
> SELECT RTRIM(LEFT(DATENAME(month, getdate()),3)+DATENAME(day,
> getdate())+DATENAME(year, getdate()))
> ...How would I be able to implement the result of the above stament (in th
e
> case, Feb232006) to be the new folder name, i.e., take the result of the S
QL
> select stmt. and pass it to the OS to rename a folder?
> Thanks.

passing row to custom code

I wish to pass the current row to a custom function in reporting
services...does anyone have the syntax for this?
It would looking like: txtMybox.value = ParseInformation( current_row)
ThanksRoy.
Your value should be
=Code.ParseInformation(Fields!CurrentRow.Value)
Now, this is subject to what "current row" is refering to. If your
referencing just 1 field in the current row then the above is approriate;
however if you are trying to pass 2, or n number of fields, you need to
either
a) concatenate them together such as
Code.ParseInformation(FIelds!Field_1.Value+Fields!Field_2.Value+...Fields!Field_n.Value)
OR make in your custom code create a variable for each field to be passed,
and call the code like:
Code.ParseInformation(Fields!Field_1.Value,Fields!Field_2.Value,...,Fields!Field_n.Value)
Michael C
"roy@.mgk.com" wrote:
> I wish to pass the current row to a custom function in reporting
> services...does anyone have the syntax for this?
> It would looking like: txtMybox.value => ParseInformation( current_row)
>
> Thanks
>