The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.1. Create a temp table (not a table variable).
2. INSERT #table(column_list) EXEC procedure
3. Load the cursor.
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Wednesday, March 28, 2012
Putting @Parameters in Stored Procedures
I am creating a stored Procedure and I am getting an error which relates toDATENAME.
|||I tried,
Try it like this:
Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN
|||yes, thank you. That will work fine
SELECTCOUNT(*)AScalls,DATENAME(@.varDate, CALLSTARTTIME)AS 'Total Calls'
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS='1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <=@.EndDate
sql doesn't like: DATENAME( @.varDate, CallStartTime)
sql works fine if I change @.varDate into 'yy', 'mm', 'dd', or 'wk'
Since I do not want to make 5 unique Stored Proc just because of @.varDate....
Is there any way to work around this problem?
DECLARE @.varDate VARCHAR(10)
SELECT @.varDate = 'mm'
SELECT
OrderDate,
CASE @.varDate
WHEN 'mm' THEN DATENAME(mm,OrderDate)
WHEN 'yy' THEN DATENAME(yy,OrderDate)
WHEN 'dw' THEN DATENAME(dw,OrderDate)
END -- added this as an EDIT to my post
FROM Orders
|||I tried,
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.VarDate as char,
@.StartDate as char,
@.EndDate as char
)
AS
SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME) AS 'Year'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)
SQL wouldn't allow me to save. What did I do wrong?
Try it like this:
SELECTCOUNT(*) AS 'Calls',CASE @.VarDateWhen 'yy' Then DATEPART(yy, CALLSTARTTIME)ENDAS 'Year'FROM CALL_LOG_MASTERWHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)GROUP BY DATEPART(yy, CALLSTARTTIME)ORDER BY DATEPART(yy, CALLSTARTTIME)
Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.
SELECT
CASE @.varDate
when 'yy' Then DATEPART(yy, CALLSTARTTIME) END as 'Year',
COUNT(DATEPART(yy, CALLSTARTTIME)) AS 'Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)|||I think I found the problem,
ALTER PROCEDUREdbo.GET_TOTALCALLS
(
@.varDateas varchar(255),
@.StartDateas datetime,
@.EndDateas datetime
)
AS
SELECT
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
END,
Count(*)as'Total Calls'
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS = '1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <= @.EndDate)
GROUP BY
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
End
ORDER BY
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
End
RETURN
However, the column can't be named this way. My new question is how do I renamed the datepart column by case?
Namely,
when @.varDate = 'Year', the column name should be 'Year',
when @.varDate = 'Month', the column name should be as 'Month', etc..
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN
|||yes, thank you. That will work fine
put results from sp to another sp
Hi,
Can anyone tell me how to use the result of a stored procedure to be used in
another stored procedure?
The first sp is a simple select statement with 2 columns, the second one
needs those two columns to insert in a temptable.
ThnxCreate a temporary table in the second sp and use INSERT ... EXEC
Example:
use northwind
go
create procedure proc1
@.d datetime
as
set nocount on
select orderid, orderdate
from dbo.orders
where orderdate >= convert(char(8), @.d, 112) and orderdate < dateadd(day, 1,
convert(char(8), @.d, 112))
return @.@.error
go
create procedure proc2
@.d datetime
as
set nocount on
create table #t (
orderid int,
orderdate datetime
)
insert into #t
exec proc1 @.d
select * from #t
drop table #t
go
exec proc2 '19980226'
go
drop procedure proc1, proc2
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Jason" wrote:
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
>|||OUTPUT parameters ?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:eoGJzrIMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
> in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
Can anyone tell me how to use the result of a stored procedure to be used in
another stored procedure?
The first sp is a simple select statement with 2 columns, the second one
needs those two columns to insert in a temptable.
ThnxCreate a temporary table in the second sp and use INSERT ... EXEC
Example:
use northwind
go
create procedure proc1
@.d datetime
as
set nocount on
select orderid, orderdate
from dbo.orders
where orderdate >= convert(char(8), @.d, 112) and orderdate < dateadd(day, 1,
convert(char(8), @.d, 112))
return @.@.error
go
create procedure proc2
@.d datetime
as
set nocount on
create table #t (
orderid int,
orderdate datetime
)
insert into #t
exec proc1 @.d
select * from #t
drop table #t
go
exec proc2 '19980226'
go
drop procedure proc1, proc2
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Jason" wrote:
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
>|||OUTPUT parameters ?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:eoGJzrIMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
> in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
Friday, March 23, 2012
Purging Data Cache
How do I go abouts purging the data cache for a particular database? Is there a stored procedure that can be executed??Try the :
DBCC DROPCLEANBUFFERS -- for clear out the buffer cache
DBCC FREEPROCCACHE -- to clear out the procedure cache.
Worse, just shutdown & restart the server - I have to post a waiver for this :-)
SVTsql
DBCC DROPCLEANBUFFERS -- for clear out the buffer cache
DBCC FREEPROCCACHE -- to clear out the procedure cache.
Worse, just shutdown & restart the server - I have to post a waiver for this :-)
SVTsql
Tuesday, March 20, 2012
pulling my hair out over udf and dynamic SQL
Hi, I hope someone can help me.
I have a stored procedure that creates some dynamic SQL which is used
to populate a payroll system with timesheets information. As part of
the SQL that is created a udf is called to calculate the employees base
pay for the month.
the issue is that if I execute the query string using the exec command
the udf does not fire, however if I copy the querystring into QA and
run it, the results are fine, any pointers here would be great.
Thanks
SimonADO? If so, try SET NOCOUNT ON. If not, can you repro the "not firing" scena
rio in QA?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||post your SQL statements
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||Can you post a script to reproduce the problem? Please include DDL.
If you find yourself needing dynamic SQL for basic data manipulation tasks
then that's often an indicator of a poor underlying design. Have you tried
to solve the problem without the dynamic code?
David Portas
SQL Server MVP
--|||Why are you using dynamic SQL? Are the rules for the payroll
constantly changing from month to month, moment to moment? Why do
users know more at run time about how to do a payroll than you know at
design time?
This kind of programming is usually a sign of a lack of a proper data
model and the use of a UDF is usually a sign of proceudral coding in
SQL instead of relational code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Hi all,
I managed to sort it, Looks like the udf was not needed after all in
this instance, but as to why that particular one would not fire when
called through a generated sql string is a mystery to me, especially
when others worked fine.
Tibor - thanks for the tip but I was still testing within QA, but will
bare the NO COUNT in mind should I come across the problem when using
ADO.
David - Thanks for the offer however I did not fancy posting a
storedprocedure containing over 500 lines of code that is not fully
commented as I am still devleloping it. This is not really what I
would term basic data manipulation, due to the rules of our pay
periods, and other factors. I did try to solve the issue without using
generated/dynamic SQL but kept hitting a brick wall, thanks again for
the offer.
Celko - I am using dynamic/generated SQL as trying to build a flat out
view for this task was proving a real headache. Our payroll rules do
not change from month to month, moment to moment, however working
things out like how our pay periods are structured does change, whether
this be a 5 w
pay month or a four w pay month, plus the fact that
the actual period dates differ, and we are not simply paid from the
first to the 31st of each month that would be too easy. The users do
not know more at run time than I know as design time, all they know is
the last day of the pay month. From this the rules are applied and the
script generated to produce the output.
Correct me if I am wrong but aren't udfs ideal for things like business
rules and such like, for example, I am using udf's to manage, our
sickness rules, holiday rules and payroll rules. I would not really
fancy having to re-write views and stored procedures because our
sickness rules have changed slightly due to us buying another company
and honouring their sickness rules, I would be much happier just
changing a udf.
I see this a lot on these groups, saying udf's are bad and
dynamic/generated SQL is a sign of defeat, something I do not agree
with.
Anyway thats my rant over with, thanks for the offer of assistance, but
all I was really looking for was, maybe someone who had come across
this strange issue before.|||Nothing in what you've said indicates to me that dynamic SQL is an
obvious choice. The calendar, payment dates and stuff can go in a
table, where it can be easily modified without code changes.
I wouldn't say dynamic code is universally bad but it isn't best
practice for a production application. All too often, dynamic SQL is
just used as a kludge around poor data design. That may or may not be
the case here but I've worked with a few payroll systems and I haven't
yet seen one that couldn't represent all the configuration information
as data elements rather than dynamic code. In fact AFAIK that's pretty
standard practice with packaged payroll systems (tax and regulatory
stuff is usually implemented as separate modules that can be patched
for localization support), which in principle have to cope with a much
more complex and varied set of circumstances across a variety of
different business.
David Portas
SQL Server MVP
--|||David,
Fair comments I feel,
For some reason I had not actually thought of putting the pay dates and
periods into a table, not sure why as this is now obvious, maybe I
should get my school bag and go home! Actually doing that will get rid
of most of the dynamic/generated stuff I have in this instance as most
of it is working that out, lucky I am not actually writing a payroll
system but creating an add on.
Thanks.
Simon|||>> Our payroll rules do not change from month to month, moment to moment, ho
wever working things out like how our pay periods are structured does change
, whether this be a 5 w pay month or a four w pay month, plus the fact
that the actual period dat
es differ, and we are not simply paid from the first to the 31st of each mon
th that would be too easy. <<
You are still thinking with procedures and not relations. Look up the
use of an auxiliary Calendar table in one of my books. Do not try to
use dynamic SQL to construct this data every time you do a payroll.|||David and Celko,
Thanks for the tips, the using a table for dates/Auxiliary table worked
a treat.
Thanks
Simon.
I have a stored procedure that creates some dynamic SQL which is used
to populate a payroll system with timesheets information. As part of
the SQL that is created a udf is called to calculate the employees base
pay for the month.
the issue is that if I execute the query string using the exec command
the udf does not fire, however if I copy the querystring into QA and
run it, the results are fine, any pointers here would be great.
Thanks
SimonADO? If so, try SET NOCOUNT ON. If not, can you repro the "not firing" scena
rio in QA?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||post your SQL statements
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||Can you post a script to reproduce the problem? Please include DDL.
If you find yourself needing dynamic SQL for basic data manipulation tasks
then that's often an indicator of a poor underlying design. Have you tried
to solve the problem without the dynamic code?
David Portas
SQL Server MVP
--|||Why are you using dynamic SQL? Are the rules for the payroll
constantly changing from month to month, moment to moment? Why do
users know more at run time about how to do a payroll than you know at
design time?
This kind of programming is usually a sign of a lack of a proper data
model and the use of a UDF is usually a sign of proceudral coding in
SQL instead of relational code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Hi all,
I managed to sort it, Looks like the udf was not needed after all in
this instance, but as to why that particular one would not fire when
called through a generated sql string is a mystery to me, especially
when others worked fine.
Tibor - thanks for the tip but I was still testing within QA, but will
bare the NO COUNT in mind should I come across the problem when using
ADO.
David - Thanks for the offer however I did not fancy posting a
storedprocedure containing over 500 lines of code that is not fully
commented as I am still devleloping it. This is not really what I
would term basic data manipulation, due to the rules of our pay
periods, and other factors. I did try to solve the issue without using
generated/dynamic SQL but kept hitting a brick wall, thanks again for
the offer.
Celko - I am using dynamic/generated SQL as trying to build a flat out
view for this task was proving a real headache. Our payroll rules do
not change from month to month, moment to moment, however working
things out like how our pay periods are structured does change, whether
this be a 5 w
pay month or a four w pay month, plus the fact thatthe actual period dates differ, and we are not simply paid from the
first to the 31st of each month that would be too easy. The users do
not know more at run time than I know as design time, all they know is
the last day of the pay month. From this the rules are applied and the
script generated to produce the output.
Correct me if I am wrong but aren't udfs ideal for things like business
rules and such like, for example, I am using udf's to manage, our
sickness rules, holiday rules and payroll rules. I would not really
fancy having to re-write views and stored procedures because our
sickness rules have changed slightly due to us buying another company
and honouring their sickness rules, I would be much happier just
changing a udf.
I see this a lot on these groups, saying udf's are bad and
dynamic/generated SQL is a sign of defeat, something I do not agree
with.
Anyway thats my rant over with, thanks for the offer of assistance, but
all I was really looking for was, maybe someone who had come across
this strange issue before.|||Nothing in what you've said indicates to me that dynamic SQL is an
obvious choice. The calendar, payment dates and stuff can go in a
table, where it can be easily modified without code changes.
I wouldn't say dynamic code is universally bad but it isn't best
practice for a production application. All too often, dynamic SQL is
just used as a kludge around poor data design. That may or may not be
the case here but I've worked with a few payroll systems and I haven't
yet seen one that couldn't represent all the configuration information
as data elements rather than dynamic code. In fact AFAIK that's pretty
standard practice with packaged payroll systems (tax and regulatory
stuff is usually implemented as separate modules that can be patched
for localization support), which in principle have to cope with a much
more complex and varied set of circumstances across a variety of
different business.
David Portas
SQL Server MVP
--|||David,
Fair comments I feel,
For some reason I had not actually thought of putting the pay dates and
periods into a table, not sure why as this is now obvious, maybe I
should get my school bag and go home! Actually doing that will get rid
of most of the dynamic/generated stuff I have in this instance as most
of it is working that out, lucky I am not actually writing a payroll
system but creating an add on.
Thanks.
Simon|||>> Our payroll rules do not change from month to month, moment to moment, ho
wever working things out like how our pay periods are structured does change
, whether this be a 5 w
pay month or a four w pay month, plus the factthat the actual period dat
es differ, and we are not simply paid from the first to the 31st of each mon
th that would be too easy. <<
You are still thinking with procedures and not relations. Look up the
use of an auxiliary Calendar table in one of my books. Do not try to
use dynamic SQL to construct this data every time you do a payroll.|||David and Celko,
Thanks for the tips, the using a table for dates/Auxiliary table worked
a treat.
Thanks
Simon.
Pulling information from an oracle database into my SQL Server database
I have been tasked with pulling information from a clients Oracle database in a stored procedure to pass out information. This needs to be a simple stored procedure but i am not sure as to how to point it to pull from a different server and use the ODBC to connect and pull this information. Any suggestions??:confused:Have you looked at using sp_addlinkedserver|||I took a look at this but was told by my supervisor we are not creating a linked server he just wants to use the ODBC to go from SQL to Oracle, and get the info need. So i am basically looking for a script on how to access the ODBC to perform this.|||Then look at OPENROWSET. Since you are saying that you want to access Oracle from MS-SQL, I'm assuming that this means T-SQL and not a program (VB,C++,Perl).|||I am actually wanting to query the Oracle database directly from a stored procedure. Yes it is T-Sql.|||If you don't (or can't) use Linked Servers then look at OPENROWSET. All you need is the ODBC driver for Oracle.
From BOL
Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO
From BOL
Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO
Wednesday, March 7, 2012
Publisher keeps trying to connect to subscriber
hi,
I manually changed a value in a table that makes part of the Replication and after that the sync failed on the procedure that updates the table on the subscriber.
So I manually re-start the sync. But for some reason it keeps trying to reconnect to the subscriber.
If I try to delete the subscriber to push it again, it locks.
I went to the Agent History, it showed the error "The process could not connect to Subscriber"
ODBC SQL SERVER DRIVER (number 08001)
I checked the SQL on the subscriber and it's running.
Does anybody has an idea of what I could do ?
Thanks !Before somebody try to answer, I got the replication running again.
I STOPPED the Sql Server at the subscriber and Re-started it.
things looks fine now.
Renata
I manually changed a value in a table that makes part of the Replication and after that the sync failed on the procedure that updates the table on the subscriber.
So I manually re-start the sync. But for some reason it keeps trying to reconnect to the subscriber.
If I try to delete the subscriber to push it again, it locks.
I went to the Agent History, it showed the error "The process could not connect to Subscriber"
ODBC SQL SERVER DRIVER (number 08001)
I checked the SQL on the subscriber and it's running.
Does anybody has an idea of what I could do ?
Thanks !Before somebody try to answer, I got the replication running again.
I STOPPED the Sql Server at the subscriber and Re-started it.
things looks fine now.
Renata
Subscribe to:
Comments (Atom)