PDF rendering problem if it is called from a modal page (fdf)

Hi,
I am having this problem with rendering PDF. My modal is calling this report
page which renders the PDF by posting the parameter.
From a modal page.
//I have the parameters in the Form
var strReportPage = "frmReportModal.aspx";
formReport.action = strReportPage;
formReport.method = "post";
formReport.target = "blank";
formReport.submit();
The reportModal.aspx comes up with this error:
Adobe Reader could not open 'pa02528XX.fdf' because it is either not a
supported file type ... or because the file has been corrupted.
It will work fine if I try from a non-modal pagestrutt,
Unfortunately not at the moment. I don't if it is adobe problem.
"strutt" <strutt@.discussions.microsoft.com> wrote in message
news:6326B311-9604-434F-A92F-51618D7C38F8@.microsoft.com...
> Did you ever figure this out? I am having the same problem. It appears to
be
> something in Adobe, but I still don't have it fixed. Thanks for any help.
> "Hara" wrote:
> > Hi,
> >
> > I am having this problem with rendering PDF. My modal is calling this
report
> > page which renders the PDF by posting the parameter.
> >
> > From a modal page.
> > //I have the parameters in the Form
> > var strReportPage = "frmReportModal.aspx";
> > formReport.action = strReportPage;
> > formReport.method = "post";
> > formReport.target = "blank";
> > formReport.submit();
> >
> > The reportModal.aspx comes up with this error:
> >
> > Adobe Reader could not open 'pa02528XX.fdf' because it is either not a
> > supported file type ... or because the file has been corrupted.
> >
> > It will work fine if I try from a non-modal page
> >
> >|||Hara,
I did not figure out what was causing the problem either. I did come up with
a workaround though, If you change the content-disposition to
'attachment;filename=xxxxxx.pdf', then IE will prompt with a file open/save
dialog. If the user selects 'open'. the file opens properly in adobe reader.
Dave
"Hara Vilaythong" wrote:
> strutt,
> Unfortunately not at the moment. I don't if it is adobe problem.
>
> "strutt" <strutt@.discussions.microsoft.com> wrote in message
> news:6326B311-9604-434F-A92F-51618D7C38F8@.microsoft.com...
> > Did you ever figure this out? I am having the same problem. It appears to
> be
> > something in Adobe, but I still don't have it fixed. Thanks for any help.
> >
> > "Hara" wrote:
> >
> > > Hi,
> > >
> > > I am having this problem with rendering PDF. My modal is calling this
> report
> > > page which renders the PDF by posting the parameter.
> > >
> > > From a modal page.
> > > //I have the parameters in the Form
> > > var strReportPage = "frmReportModal.aspx";
> > > formReport.action = strReportPage;
> > > formReport.method = "post";
> > > formReport.target = "blank";
> > > formReport.submit();
> > >
> > > The reportModal.aspx comes up with this error:
> > >
> > > Adobe Reader could not open 'pa02528XX.fdf' because it is either not a
> > > supported file type ... or because the file has been corrupted.
> > >
> > > It will work fine if I try from a non-modal page
> > >
> > >
>
>

PDF Language/Font Problem

Anyone come across this one:
I have a report that has to print out its figures in Euros or Dollars - I
pass in the locale code as a parameter (DE-DE or EN-US) respectively and
change the language of the report to one of those values via an expression.
The report runs, looks fine on any PC over the web. US countries have their
figures preceded by a $, European countries figures are preceded by a Euro
symbol. When I come to export the results to PDF, again its fine on my PC,
but the same report exported on seemingly arbitrary PC's elsewhere in the
building have the numerical figures garbled into some unrecognisable font.
Any ideas?
MickIf anyone is interested, this was an Adobe issue - v5 doesn't support the
Euro symbol (upgrading to V6 solved the problem)
"Mick Horne" <mick.horne@.conchango.com> wrote in message
news:%23cgQs2eXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Anyone come across this one:
> I have a report that has to print out its figures in Euros or Dollars - I
> pass in the locale code as a parameter (DE-DE or EN-US) respectively and
> change the language of the report to one of those values via an
expression.
> The report runs, looks fine on any PC over the web. US countries have
their
> figures preceded by a $, European countries figures are preceded by a Euro
> symbol. When I come to export the results to PDF, again its fine on my
PC,
> but the same report exported on seemingly arbitrary PC's elsewhere in the
> building have the numerical figures garbled into some unrecognisable font.
> Any ideas?
> Mick
>

PDF export and invisible columns

I have a report with some columns that are visible\invisible based on a
parameter value. The report renders fine in HTML and when exported to Excel.
But when its exported to PDF the report shows blank white space for some of
the columns that are invisible, e.g. the report is 3 pages wide when the
collumns are all visible, it is 1 page wide when some are invisible, but if
you export the 1 page wide report to PDF it still comes out as 3 pages wide,
with the last 2 pages just alll blank space.
ANything I can do?Did you find any solutions? I had teh same problem.
I am setting the visibility for the Rows through the expression. They are
not showing in HTML mode. However, If I export to PDF it shows some white
spaces.
"NH" wrote:
> I have a report with some columns that are visible\invisible based on a
> parameter value. The report renders fine in HTML and when exported to Excel.
> But when its exported to PDF the report shows blank white space for some of
> the columns that are invisible, e.g. the report is 3 pages wide when the
> collumns are all visible, it is 1 page wide when some are invisible, but if
> you export the 1 page wide report to PDF it still comes out as 3 pages wide,
> with the last 2 pages just alll blank space.
> ANything I can do?|||Hi,
No, never found a solution. I am afraid that you dont get many replies to
your queries in this forum.
"GC" wrote:
> Did you find any solutions? I had teh same problem.
> I am setting the visibility for the Rows through the expression. They are
> not showing in HTML mode. However, If I export to PDF it shows some white
> spaces.
>
> "NH" wrote:
> > I have a report with some columns that are visible\invisible based on a
> > parameter value. The report renders fine in HTML and when exported to Excel.
> > But when its exported to PDF the report shows blank white space for some of
> > the columns that are invisible, e.g. the report is 3 pages wide when the
> > collumns are all visible, it is 1 page wide when some are invisible, but if
> > you export the 1 page wide report to PDF it still comes out as 3 pages wide,
> > with the last 2 pages just alll blank space.
> >
> > ANything I can do?|||I have experienced something like this before...the problem is that in
order to shrink up the hidden columns then you will need to set the
visibility property on each column, not just the tablerow. Very
tedious but I believe that this may be what your report needs...
Matt A|||i have a similar problem. html & xls work fine, but when you export to
csv, the columns are hidden no matter what you do...
reportdude wrote:
> I have experienced something like this before...the problem is that in
> order to shrink up the hidden columns then you will need to set the
> visibility property on each column, not just the tablerow. Very
> tedious but I believe that this may be what your report needs...
> Matt A|||Thanks for the Update. I have tried the option of applying the Expression for
the Visibiliy of every cell in that Row. Still it doesn't make any difference.
This is creating space at the end of the Report. ( I have few Sub Reports).
If I set the hidden false then the white spaces are disappearing. However
this works well in HTML Viewer.
"reportdude" wrote:
> I have experienced something like this before...the problem is that in
> order to shrink up the hidden columns then you will need to set the
> visibility property on each column, not just the tablerow. Very
> tedious but I believe that this may be what your report needs...
> Matt A
>

PDF error after applying SP1

After applying SP1 i got following error during export to PDF
Export Error:
"Index and lenght must refer to a location within a string.
Parameter name: lenght"Workaround solution with following post:
"SP1 - Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown" by Rahul
U¿ytkownik "kamel" <ddd@.ddd.com> napisa³ w wiadomo¶ci
news:e9gFBIQXEHA.3016@.tk2msftngp13.phx.gbl...
> After applying SP1 i got following error during export to PDF
> Export Error:
> "Index and lenght must refer to a location within a string.
> Parameter name: lenght"
>|||I have a straight forward workaround: Always use a Lang-Country code pair.
For example, in report designer, the language options for Norwegian are
Name Lang-County Code
* Norwegian no
* Norwegian (Bokmal) nb-NO
* Norwegian (Nynorsk) nn-NO
To avoid the error when exporting to PDF always use nb-NO or nn-NO.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"kamel" <ddd@.ddd.com> wrote in message
news:e1TnW%23cXEHA.2364@.TK2MSFTNGP12.phx.gbl...
> Workaround solution with following post:
> "SP1 - Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown" by Rahul
> U¿ytkownik "kamel" <ddd@.ddd.com> napisa³ w wiadomo¶ci
> news:e9gFBIQXEHA.3016@.tk2msftngp13.phx.gbl...
> > After applying SP1 i got following error during export to PDF
> >
> > Export Error:
> > "Index and lenght must refer to a location within a string.
> > Parameter name: lenght"
> >
> >
>

Password Parameter?

Hello.
I've got an odd requirement to password a particular report.Basically
the same authenticated user will have access to all reports, but this
particular report should only be available to select users(all which
share that username).
Any way to mask an ssrs parameter input so that it only shows *******?
Any way to persist that entry for 5 minutes, then have it no long be
valid. I've not tested or developed this yet, but I do suspect if I
call the report again (from asp.net reportviewer) and the default for
the parameter is null, that the previously entered value will not be
available right?
I currently have another post where i can't seem to figure out why my
report is defaulting some fields that no longer have fields, but only
when I test the report through reportviewer, and not my vs.net on my
client.
Thanks for any help or information!it would probably be better to put the report into a separate folder and
secure it via the folder. this would allow to have multiple reports with
this security requirement in the future. Parameters are not really intended
for passwords.
"jobs" <jobs@.webdos.com> wrote in message
news:1193496978.830252.227650@.v23g2000prn.googlegroups.com...
> Hello.
> I've got an odd requirement to password a particular report.Basically
> the same authenticated user will have access to all reports, but this
> particular report should only be available to select users(all which
> share that username).
> Any way to mask an ssrs parameter input so that it only shows *******?
> Any way to persist that entry for 5 minutes, then have it no long be
> valid. I've not tested or developed this yet, but I do suspect if I
> call the report again (from asp.net reportviewer) and the default for
> the parameter is null, that the previously entered value will not be
> available right?
> I currently have another post where i can't seem to figure out why my
> report is defaulting some fields that no longer have fields, but only
> when I test the report through reportviewer, and not my vs.net on my
> client.
> Thanks for any help or information!
>

passing the value of the Name property from a textbox to a parameter ?

Hi All,
I'd like to pass the name or label of a textbox to a parameter but I
am not sure of how to get it done.
I have a table (the PK is on Language and Field)
Language(PK)
Field (PK)
FieldName
and a data set query:
"SELECT * from table
WHERE Language = @.Language AND Field = @.Field"
The parameter @.LanguageCode is based on user input and I want the
parameter @.Field to be based on the Name of a textbox (or eq. object
in the report)
(@.Field=Fields!Field.Name, "table")
The Value of the textbox should be set to the appropriate fieldname
based on the users choice of language and the identifier of the
textbox...
...something like:
=IIF(Parameters!Language.Value = 1, Fields!FieldName.Value, "table",
IIF(Parameters!Language.Value = 2, Fields!FieldName.Value, "table",
'Not Translated!'))
Anyone got any ideas?
Thanks a million in advance!
Regards NickWell...
I dynamically transposed the data in the table in order to use the
First() function in RS, problem solved.
Still I wonder how do you use labels and such from the report to
filter data in the data set?
/n|||You'd have to use parameters or fields from a dataset. Report items cannot
be used in filter expressions.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niklas W" <niklas.westerholm@.intellibis.se> wrote in message
news:655bff8b.0408130442.754cd47f@.posting.google.com...
> Well...
> I dynamically transposed the data in the table in order to use the
> First() function in RS, problem solved.
> Still I wonder how do you use labels and such from the report to
> filter data in the data set?
> /n

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben

Passing Text in parameter dropdown

I have a month drop down list.

In my dataset I have the code which generates month name for all months.

In my parameter list I want the functionality of the user able to select 'All' as Month name.

How can I achieve this in the query and parameter.

Code for month generation is as.

SELECT DISTINCTSUBSTRING(DATENAME(mm, T1.OPEN_TIME), 0,4) AS Month,
DATENAME(mm, T1.OPEN_TIME) AS MONTH_NAME_FULL,
DATEPART(mm, T1.OPEN_TIME) AS MONTH_NUM
FROM APPS237.Cats.dbo.Pd T1
Order By
DATEPART(mm, T1.OPEN_TIME)

Thanks,

Kiran.

You can use the UNION ALL clause to add the 'All' entry to the beginning of the table generated by your query. For example,

SELECT DISTINCT SUBSTRING(DATENAME(mm, T1.OPEN_TIME), 0,4) AS Month,
DATENAME(mm, T1.OPEN_TIME) AS MONTH_NAME_FULL,
DATEPART(mm, T1.OPEN_TIME) AS MONTH_NUM
FROM APPS237.Cats.dbo.Pd T1
UNION ALL
SELECT 'All', 'All', 0
Order By MONTH_NUM

Ian

Passing Temp Table Values into a UDF

This one is interesting...

Is there any way to pass a joined parameter into a UDF as I'm attempting below?

I have a temp table that I'm trying to create:

create table #t3
(bmkPerson int primary key,
LangCode nchar(5),
SName varchar(1000)
)
insert into #t3
select t2.bmkPerson, t2.LangCode,
select SName from SName_trans_udf(t2.LangCode)
from #t2 t2

Thanks in advance,

Renae
hilary321@.yahoo.comRenae (hilary321@.yahoo.com) writes:
> Is there any way to pass a joined parameter into a UDF as I'm attempting
> below?
> I have a temp table that I'm trying to create:
> create table #t3
> ( bmkPerson int primary key,
> LangCode nchar(5),
> SName varchar(1000)
> )
> insert into #t3
> select t2.bmkPerson, t2.LangCode,
> select SName from SName_trans_udf(t2.LangCode)
> from #t2 t2

No, you cannot pass a column value to a table-valued UDF. And when you
think of it it is logical: what result would you get? For each column
you get a whole table back. A scalar UDF works fine, because it's a
scalar value you need here.

SQL 2005 however does permit you to pass a column to a table-valued
UDF, but there is a special syntax for this. (Which I have not explored
yet, so I cannot give any examples.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

passing tablename as parameter to function and to use it dynamically

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

Quote:

Originally Posted by satish@.entech.us

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

I don't think you can do this because DynamicSQL has it's own scope. Which means it won't return anything to the function. It will just run. So you can't do

SET @.Return = EXEC @.Command

As they are two different scopes.

What you could do is store the results of the output to a temp table and interrogate that when the function completes.

Cheers
C

passing table/column name as parameter

I know passing table/column name as parameter to a stored procedure is
not good practice, but sometimes I need to do that occasionally. I
know there's a way can do that but forget how. Can someone refresh my
memory?
Thanks.
Saiyouwangc@.alexian.net (Saiyou Anh) wrote in message news:<a51d3ca7.0407271253.91fae7@.posting.google.com>...
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
> Thanks.
> Saiyou

See here, which will also refresh your memory about why you shouldn't do this... :-)

http://www.sommarskog.se/dynamic_sql.html

Simon|||Saiyou Anh (wangc@.alexian.net) writes:
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?

All about it, including why shouldn't do it on
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 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 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 table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az alábbiakat írta a következõ
hírüzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table type variables to SP

I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>

Passing table name as parameter in stored procedure

Hey guys,
Im trying to create a store procedure that will perform certain duties on a
given table. The table name will be passed as a parameter.
The problem is I am not allowed to use the parameter as a tablename, so the
following queries are rejected:
SELECT #temp.[id] as SForceID, @.Table.[id] as OppID INTO #Temp2 FROM @.Table
LEFT OUTER JOIN #Temp ON #Temp.[id] = @.Table.[id]
DELETE FROM @.Table WHERE [id] IN (SELECT OppID FROM #Temp2 WHERE SForceID IS
NULL)
How do I achieve this? Is it doable? Is it perhaps a limitation?
Thank you in advance.Juan,
It is doable using dynamic sql but you may need to think about the
design.These are well discussed in the below article by Erland Sommarskog:
The Curse and Blessings of Dynamic SQL
http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_table
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Juan Romero" <juanja01@.optonline.net> wrote in message
news:%23M9Mi2iUDHA.1916@.TK2MSFTNGP12.phx.gbl...
> Hey guys,
> Im trying to create a store procedure that will perform certain duties on
a
> given table. The table name will be passed as a parameter.
> The problem is I am not allowed to use the parameter as a tablename, so
the
> following queries are rejected:
> SELECT #temp.[id] as SForceID, @.Table.[id] as OppID INTO #Temp2 FROM
@.Table
> LEFT OUTER JOIN #Temp ON #Temp.[id] = @.Table.[id]
> DELETE FROM @.Table WHERE [id] IN (SELECT OppID FROM #Temp2 WHERE SForceID
IS
> NULL)
> How do I achieve this? Is it doable? Is it perhaps a limitation?
> Thank you in advance.
>