Merging two dbs: procedure

Showing posts with label procedure. Show all posts

Monday, March 26, 2012

Microsoft Reporting Services - Error - Expects Parameter - Stored Procedure

Solution:
This happens to me quite a bit...its because the designer removes the
code that defines what
values are passed to your stored procedure.
You have to select the report you're working on > Right-Click 'Code' >
Locate the <Query> XML tag...and you'll have to re-define the
<QueryParameters>.
<Query>
<DataSourceName>GPS</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>spLRGetLeadRotationSummary</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.RegionID">
<Value>=Parameters!RegionID.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>You can also do this by clicking on the ..., parameters tab. Modifying the
xml is dangerous. Although if you have a lot of query parameters and RS
keeps losing it then I have sometimes saved the section off so I could copy
it back in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<petejk@.gmail.com> wrote in message
news:1135124852.986963.63390@.f14g2000cwb.googlegroups.com...
> Solution:
> This happens to me quite a bit...its because the designer removes the
> code that defines what
> values are passed to your stored procedure.
> You have to select the report you're working on > Right-Click 'Code' >
> Locate the <Query> XML tag...and you'll have to re-define the
> <QueryParameters>.
>
> <Query>
> <DataSourceName>GPS</DataSourceName>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>spLRGetLeadRotationSummary</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.StartDate">
> <Value>=Parameters!StartDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.EndDate">
> <Value>=Parameters!EndDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.RegionID">
> <Value>=Parameters!RegionID.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
>

Friday, March 23, 2012

Microsoft OLE DB Provider for SQL Server error '80004005' - Unspecified error

Hi,
I have an ASP code that calls a stored procedure. I use a Command
object to execute the stored procedure. The Stored procedure returns
data when executed in Query analyser. But it takes around 1 minute for
this. The stored procedure involves querying a huge amount of data.
When I try to open the ASP page, I get the following error :
Microsoft OLE DB Provider for SQL Server error '80004005'
Unspecified error
Any suggestions?
TIA,
AnishyaHi
Have you checked with SQL Profiler that the procedure is being called? Check
that the login/user you are using has the correct permissions. If this has
timed out then I would expect that a TIMEOUT error message. You may also want
to check http://www.aspfaq.com/show.asp?id=2009
John
"Ani" wrote:
> Hi,
> I have an ASP code that calls a stored procedure. I use a Command
> object to execute the stored procedure. The Stored procedure returns
> data when executed in Query analyser. But it takes around 1 minute for
> this. The stored procedure involves querying a huge amount of data.
> When I try to open the ASP page, I get the following error :
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Unspecified error
> Any suggestions?
> TIA,
> Anishya
>|||Hi,
The procedure is being called in the Profiler. I tried executing the
same stored procedure call in query analyser and saw that it works
fine. The only problem is that it takes around 1 minute. So I am sure
this is why the ASP page gives the error. The stored proc makes the use
of a temporary table. I tried executing the stored proc by using a
permanent table instead of the temporary table. It doesnt take much
time. But doesn't permanent tables involve more performance overheads
than temporary tables? I am trying to find another alternative. Is
there any way that I could increase the timeout period for ASP pages?
Thanks,
Anishya|||Hi
You could be seeing contention on tempdb if a lot of people are
simulataneously creating temporary tables, see
http://support.microsoft.com/kb/328551. You should see if the temporary table
is really necessary or possibly use a table variable as an alternative.
You may also want to change the timeout on your command object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocommandtimeout.asp
John
"Ani" wrote:
> Hi,
> The procedure is being called in the Profiler. I tried executing the
> same stored procedure call in query analyser and saw that it works
> fine. The only problem is that it takes around 1 minute. So I am sure
> this is why the ASP page gives the error. The stored proc makes the use
> of a temporary table. I tried executing the stored proc by using a
> permanent table instead of the temporary table. It doesnt take much
> time. But doesn't permanent tables involve more performance overheads
> than temporary tables? I am trying to find another alternative. Is
> there any way that I could increase the timeout period for ASP pages?
> Thanks,
> Anishya
>

Friday, March 9, 2012

Meta-Information about Stored-Procedures

Hi ,

I am looking for meta-information about the return recordset of a
stored-procedure. The procedure returns a resultset that contains columns of
more tables joined together. In all tables, I use, there is a
Record-Creation-Timestamp-Attribute. When joining two or more tables these
attribute-names appear in ther resultset but
i found no way to distinguish them.

I there a way to retrieve meta-information about the result-recordset of
such a stored-procedure?

here some details:

the tables
=======
CREATE TABLE [dbo].[Table1] (
[Table1ID] [int] IDENTITY (1, 1) NOT NULL ,
[FK_Tab2ID] [int] NULL ,
[CreatedAt] [datetime] NULL )

CREATE TABLE [dbo].[Table2] (
[Table2ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (35) NULL ,
[CreatedAt] [datetime] NULL)

the stored-procedure:
===============

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

the resultset:
==========
Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
(the attribute CreatedAt appears twice.)

--= Posted via Newsfeeds.Com, Uncensored Usenet News =--
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
--== Over 100,000 Newsgroups - 19 Different Servers! =--KG wrote:
> CREATE PROCEDURE dbo.sp_Test_RetrieveData
> @.ID int
> AS
> SET NOCOUNT ON
> select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
> where table1.ID = @.ID
> GO
> the resultset:
> ==========
> Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
> (the attribute CreatedAt appears twice.)

Try this instead:

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select Table1ID, FK_Tab2ID, Table1.CreatedAt as Table1CreatedAt, Table2ID,
Description, Table2.CreatedAt as Table2CreatedAt
from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

you will get resultset:

Table1ID, FK_Tab2ID, Table1CreatedAt, Table2ID, Description, Table2CreatedAt

--
Steve Troxell
Krell Software - Database Tools for MS SQL Server
http://www.krell-software.com|||KG (kg@.greenmail.ch) writes:
> I am looking for meta-information about the return recordset of a
> stored-procedure. The procedure returns a resultset that contains
> columns of more tables joined together. In all tables, I use, there is a
> Record-Creation-Timestamp-Attribute. When joining two or more tables
> these attribute-names appear in ther resultset but i found no way to
> distinguish them.
> I there a way to retrieve meta-information about the result-recordset of
> such a stored-procedure?

It would have helped if you have told in which environment you are working.
Are you using ADO?

In ADO, there are some properties on the Fields on object which may have
this information.

But it is kind of obscure programming to access this data. Better is to
use column aliases. Generally, SELECT * should not be used in production
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Wednesday, March 7, 2012

Meta Data retrieval

Greetings -
I need to know how I can retrieve the Meta Data Information about a procedur
e.
I know I can use SET FMTONLY ON to retrieve it but I need to retrieve this
dynamically - I need a call that will return a resultset containing the
information
instead of just a header info.
TIA,
KOYI'm not clear on what exactly you're asking for... perhaps a look at
the INFORMATION_SCHEMAs in BOL will be of help?
"Kayode Yusuf" <Kayode Yusuf@.discussions.microsoft.com> wrote in message
news:3C1FD323-4CE5-4313-ACCE-A4D8EEE6B599@.microsoft.com...
> Greetings -
> I need to know how I can retrieve the Meta Data Information about a
procedure.
> I know I can use SET FMTONLY ON to retrieve it but I need to retrieve
this
> dynamically - I need a call that will return a resultset containing the
> information
> instead of just a header info.
> TIA,
> KOY|||This is what I want - I want to get the META data for a procedure - it is no
t
contained anywhere - you can get it through SET FMTONLY ON and then execute
the procedure - essentially, it returns a Zero Length resultset. The questio
n
is this :
I want to dynamically process the result set - I need to know dynamically,
the column names returned by the execution through TSQL.
TIA,
KOU
"Armando Prato" wrote:

> I'm not clear on what exactly you're asking for... perhaps a look at
> the INFORMATION_SCHEMAs in BOL will be of help?
> "Kayode Yusuf" <Kayode Yusuf@.discussions.microsoft.com> wrote in message
> news:3C1FD323-4CE5-4313-ACCE-A4D8EEE6B599@.microsoft.com...
> procedure.
> this
>
>|||How about something like this? Change <your server>, <your username>, and
<your password>
to the values appropriate for your server.
select *
into #temp
from OPENROWSET('SQLOLEDB','<your server>';'<your username>';'<your
password>',
'set fmtonly off; exec sp_helpuser;')
where 1=2
select name
from tempdb..syscolumns
where id = object_id('tempdb..#temp')
drop table #temp
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:334CE4E5-BC9F-41C7-BE14-DB39EAEAC0FA@.microsoft.com...
> This is what I want - I want to get the META data for a procedure - it is
not
> contained anywhere - you can get it through SET FMTONLY ON and then
execute
> the procedure - essentially, it returns a Zero Length resultset. The
question
> is this :
> I want to dynamically process the result set - I need to know dynamically,
> the column names returned by the execution through TSQL.
> TIA,
> KOU
> "Armando Prato" wrote:
>
retrieve
the

Meta Data

Greetings -
Can somebody please help me out with retrieving Meta Data dynamically for a
Stored Procedure.
I know I can use SET FRMONLY ON to retrieve it - but this return a Header
info. What I want is a resultset containing that META Data that can be
inserted or used in a SELECT statement fo further manipulations.
TIA.
KOY
See my response in microsoft.public.sqlserver.programming
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> Greetings -
> Can somebody please help me out with retrieving Meta Data dynamically for
a
> Stored Procedure.
> I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> info. What I want is a resultset containing that META Data that can be
> inserted or used in a SELECT statement fo further manipulations.
> TIA.
> KOY
|||For starters, Tanx for the post. WHat needs to be done is process the
ResultSet in TSQL as done through an API.
If you make call a Call to SQLServer throughan API with SET FMTON - it
executes
the procedure and returns a zero lenght resultselt - the META Data - you can
then through the API - iterate through th ResultSet Column names to find out
the META data for the procedure.
Essentially, I want to achive the Same in TSQL mode - I need to get the META
data of a Stored procedure and be able to identify the Columns returned by
the call using SET FMTONL directive.
TIA,
KOY
"Armando Prato" wrote:

> See my response in microsoft.public.sqlserver.programming
>
> "Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
> news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> a
>
>

Meta Data

Greetings -
Can somebody please help me out with retrieving Meta Data dynamically for a
Stored Procedure.
I know I can use SET FRMONLY ON to retrieve it - but this return a Header
info. What I want is a resultset containing that META Data that can be
inserted or used in a SELECT statement fo further manipulations.
TIA.
KOYSee my response in microsoft.public.sqlserver.programming
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> Greetings -
> Can somebody please help me out with retrieving Meta Data dynamically for
a
> Stored Procedure.
> I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> info. What I want is a resultset containing that META Data that can be
> inserted or used in a SELECT statement fo further manipulations.
> TIA.
> KOY|||For starters, Tanx for the post. WHat needs to be done is process the
ResultSet in TSQL as done through an API.
If you make call a Call to SQLServer throughan API with SET FMTON - it
executes
the procedure and returns a zero lenght resultselt - the META Data - you can
then through the API - iterate through th ResultSet Column names to find out
the META data for the procedure.
Essentially, I want to achive the Same in TSQL mode - I need to get the META
data of a Stored procedure and be able to identify the Columns returned by
the call using SET FMTONL directive.
TIA,
KOY
"Armando Prato" wrote:

> See my response in microsoft.public.sqlserver.programming
>
> "Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
> news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> a
>
>

Meta Data

Greetings -
Can somebody please help me out with retrieving Meta Data dynamically for a
Stored Procedure.
I know I can use SET FRMONLY ON to retrieve it - but this return a Header
info. What I want is a resultset containing that META Data that can be
inserted or used in a SELECT statement fo further manipulations.
TIA.
KOYSee my response in microsoft.public.sqlserver.programming
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> Greetings -
> Can somebody please help me out with retrieving Meta Data dynamically for
a
> Stored Procedure.
> I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> info. What I want is a resultset containing that META Data that can be
> inserted or used in a SELECT statement fo further manipulations.
> TIA.
> KOY|||For starters, Tanx for the post. WHat needs to be done is process the
ResultSet in TSQL as done through an API.
If you make call a Call to SQLServer throughan API with SET FMTON - it
executes
the procedure and returns a zero lenght resultselt - the META Data - you can
then through the API - iterate through th ResultSet Column names to find out
the META data for the procedure.
Essentially, I want to achive the Same in TSQL mode - I need to get the META
data of a Stored procedure and be able to identify the Columns returned by
the call using SET FMTONL directive.
TIA,
KOY
"Armando Prato" wrote:
> See my response in microsoft.public.sqlserver.programming
>
> "Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
> news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> > Greetings -
> >
> > Can somebody please help me out with retrieving Meta Data dynamically for
> a
> > Stored Procedure.
> >
> > I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> > info. What I want is a resultset containing that META Data that can be
> > inserted or used in a SELECT statement fo further manipulations.
> >
> > TIA.
> >
> > KOY
>
>

Monday, February 20, 2012

Message --"Configuration option 'show advanced options' changed from 0 to 1. Run the RECO

i am working on storedprocedures and after the Succesful parsing of the procedure .. on executing its giving me 3 msgs ....

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and not displaying me the output.......

The Procedure i am executing is as follows

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[uspRcopiaExport12] AS

DROP TABLE Skagway..RCOPIA_EXPORT

-- Get Patients' Home Addresses

SELECT pa.PersonID, pa.Street1, pa.Street2, pa.City, pa.State, Skagway.dbo.StripNonDigits(pa.ZipCode) AS Zip

INTO #ADDRESS

FROM Person..PHYSICAL_ADDRESS pa

INNER JOIN Person..PHYSICAL_ADDRESS_DESC pad ON (pa.PhysicalAddrDescID = pad.PhysicalAddrDescID)

WHERE pad.PhysicalAddrDesc = 'Home'

-- Get Patients' Home Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #HOME_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Home'

ORDER BY tdt.TelecomDevTypeID

-- Get Patients' Work Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #WORK_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Work'

ORDER BY tdt.TelecomDevTypeID

-- Get SCP patient demographic data

SELECT 'update_patient_4' AS DataType,

p.FirstName AS FIRST_NAME,

p.MiddleInit AS MIDDLE_INITIAL,

p.LastName AS LAST_NAME,

CASE p.Gender

WHEN 'm' THEN 'M'

WHEN 'f' THEN 'F'

WHEN 'M' THEN 'M'

WHEN 'F' THEN 'F'

ELSE 'U'

END AS SEX,

-- (SELECT TOP 1 Street1 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_1,

-- (SELECT TOP 1 Street2 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_2,

-- (SELECT TOP 1 City FROM #ADDRESS WHERE PersonID = p.PersonID) AS CITY,

-- (SELECT TOP 1 State FROM #ADDRESS WHERE PersonID = p.PersonID) AS STATE_CODE,

-- (SELECT TOP 1 Zip FROM #ADDRESS WHERE PersonID = p.PersonID) AS ZIP,

-- (SELECT TOP 1 TelecomNumber FROM #HOME_PHONE WHERE PersonID = p.PersonID) AS PHONE,

-- (SELECT TOP 1 TelecomNumber FROM #WORK_PHONE WHERE PersonID = p.PersonID) AS WORK_PHONE,

' 'AS ADDRESS_LINE_1,

' 'AS ADDRESS_LINE_2,

' 'AS CITY,

' 'AS STATE_CODE,

' 'AS ZIP,

' 'AS PHONE,

' 'AS WORK_PHONE,

' ' AS OTHER_PHONE,

-- ea.EmailAddress AS EMAIL,

' ' AS EMAIL,

p.BirthDate AS BIRTHDATE,

p.SocSecNum AS SOCIAL_SECURITY,

' ' AS INSURANCE,

' ' AS LAST_VISIT_DATE,

'PHMS-SCP-' + CAST(p.PersonID AS VARCHAR(8)) AS EXTERNAL_ID,

' ' AS PROVIDER_FIRST_NAME,

' ' AS PROVIDER_MIDDLE_NAME,

' ' AS PROVIDER_LAST_NAME

INTO Skagway..RCOPIA_EXPORT

FROM Skagway..PERSON p

INNER JOIN Skagway..EMAIL_ADDRESS ea ON (p.PersonID=ea.PersonID)

INNER JOIN Skagway..CARE_TEAM_ASSIGNMENT cta ON (p.PersonID=cta.PersonID)

INNER JOIN Skagway..SHARED_CARE_PLAN scp ON (cta.CarePlanID=scp.CarePlanID)

WHERE scp.SCPInactiveFlag <> 1

AND cta.CareTeamRoleID = 23

AND p.BirthDate IS NOT NULL

AND p.SocSecNum IS NOT NULL

AND NOT p.SocSecNum IN ('999999999', '')

-- Replace all NULLs with single spaces (required by Rcopia for import)

UPDATE Skagway..RCOPIA_EXPORT

SET MIDDLE_INITIAL = ' '

WHERE MIDDLE_INITIAL IS NULL OR MIDDLE_INITIAL = ''