Outer table inside stored procedure

Hi,
I am using SQL Server 2000
I need to select values from a table that belongs to other database from
within a stored procedure. Hardcoding table's fully qualified name is not
appropriate. Table's database name must be a parameter of a stored procedure
.
Is there any better way than using dynamic query string generation? If query
is a dynamic string it is difficult to mainain it.
Thanks in advance.You could create a view which includes the database name - then when you nee
d
to change the database just change the view and all the SPs will reference
tthe correct database.
"Alexander Korol" wrote:

> Hi,
> I am using SQL Server 2000
> I need to select values from a table that belongs to other database from
> within a stored procedure. Hardcoding table's fully qualified name is not
> appropriate. Table's database name must be a parameter of a stored procedu
re.
> Is there any better way than using dynamic query string generation? If que
ry
> is a dynamic string it is difficult to mainain it.
> Thanks in advance.|||Sorry I made a multipost. Was not intended to just missed the window :) .
From now on let's refer to "How to refer from a stored procedure to a table
in another database"
Thanks for your reply.
I can not do anything beforehand. In the runtime my procedure can get only
the name of a database of guaranteed structure. Thereis a way to change the
database name in the view from stored procedure in the runtime ALTER VIEW. I
s
that what you ment? I will try.|||Dynamic altering the view helped. Thanks.
A very simple example:
exec('ALTER VIEW ViewName AS SELECT * FROM ' + @.NM_DATABASE +
'.dbo.TableName')

Out of memory error when running large report

I have built a large report but when I try to execute inside of the Report
Designer, I get the following error and then an abort:
[An error has occurred during report processing
Exception of type System.OutOfMemoryException was thrown]
I have applied SRS SP1 and here is the MSD info:
Microsoft Visual Basic .NET 69457-005-1826837-18410
Microsoft SQL Server Report Designer Version 8.00.878.00
Running on XP SP2 with 1.25 gig RAM
Is there a work-around for this?
Thanks for any help!http://support.microsoft.com/?kbid=840709
This article may help you.
| From: "Mike Harbinger" <MikeH@.Cybervillage.net>
| Subject: Out of memory error when running large report
| Date: Thu, 6 Jan 2005 18:47:55 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69.106.50.114
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10|||Thanks for the reponse Brad, but I am not getting the error from the report
server but from inside of the .Net Visual Studio Reporting Services
designer.
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:g7L9ZjO9EHA.3200@.cpmsftngxa10.phx.gbl...
> http://support.microsoft.com/?kbid=840709
> This article may help you.
>
> --
> | From: "Mike Harbinger" <MikeH@.Cybervillage.net>
> | Subject: Out of memory error when running large report
> | Date: Thu, 6 Jan 2005 18:47:55 -0800
> | Lines: 18
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 69.106.50.114
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38909
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have built a large report but when I try to execute inside of the
> Report
> | Designer, I get the following error and then an abort:
> |
> | [An error has occurred during report processing
> | Exception of type System.OutOfMemoryException was thrown]
> |
> |
> | I have applied SRS SP1 and here is the MSD info:
> | Microsoft Visual Basic .NET 69457-005-1826837-18410
> | Microsoft SQL Server Report Designer Version 8.00.878.00
> |
> | Running on XP SP2 with 1.25 gig RAM
> |
> | Is there a work-around for this?
> |
> | Thanks for any help!
> |
> |
> |
>

OTP: Jamie Thomson, ssis man?

are you sleeping inside a Data Flow? Where exists SSIS you are

LOL... he's already a SSIS legend

|||

There are no social forums here, this is not a question and therefore off topic. It's OK to make a social comment within a thread, but creating non-question threads pollutes the unanswered question area, and potentially the search.

|||you're right cgraus. I'll keep in mind

OSQL output

> Is there a way to get the output of the OSQL as tabular values so I can passn">
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
The output of osql.exe is text. You typically need an intermediate
program/script to parse it and reformat it before feeding the result into a
table(s).
Linchi
"Alex" wrote:

> Hi,
> I run a script on all of our SQL Servers (+300 servers) on a regular basis
> to collect information about different instances, version, port and so on
> for each SQL Server using a loop than runs OSQL and passes a query to it f
or
> each member server. The passed query then collects this information on eac
h
> server and saves the results in main server using OPENROWSET.
> Due to different SQL Server and MDAC versions on each member SQL server,
> sometimes OPENROWSET fails to saves its results.
> Is there a way to get the output of the OSQL as tabular values so I can pa
ss
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
> This way, I will avoid using costly OPENROWSET.
> My main server is SQL Server 2000 SP4 and we have different range of SQL
> Servers and SPs.
> Thanks
>
>Hi,
I run a script on all of our SQL Servers (+300 servers) on a regular basis
to collect information about different instances, version, port and so on
for each SQL Server using a loop than runs OSQL and passes a query to it for
each member server. The passed query then collects this information on each
server and saves the results in main server using OPENROWSET.
Due to different SQL Server and MDAC versions on each member SQL server,
sometimes OPENROWSET fails to saves its results.
Is there a way to get the output of the OSQL as tabular values so I can pass
the query (without OPENROWSET inside the query) using OSQL, get the results
in tabular format (like a table) and then save the results in main table.
This way, I will avoid using costly OPENROWSET.
My main server is SQL Server 2000 SP4 and we have different range of SQL
Servers and SPs.
Thanks|||> Is there a way to get the output of the OSQL as tabular values so I can passn">
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
The output of osql.exe is text. You typically need an intermediate
program/script to parse it and reformat it before feeding the result into a
table(s).
Linchi
"Alex" wrote:

> Hi,
> I run a script on all of our SQL Servers (+300 servers) on a regular basis
> to collect information about different instances, version, port and so on
> for each SQL Server using a loop than runs OSQL and passes a query to it f
or
> each member server. The passed query then collects this information on eac
h
> server and saves the results in main server using OPENROWSET.
> Due to different SQL Server and MDAC versions on each member SQL server,
> sometimes OPENROWSET fails to saves its results.
> Is there a way to get the output of the OSQL as tabular values so I can pa
ss
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
> This way, I will avoid using costly OPENROWSET.
> My main server is SQL Server 2000 SP4 and we have different range of SQL
> Servers and SPs.
> Thanks
>
>