Active Tables

I have a live database. In that Database so many tables
and stored procedures are not using. I have to find those
and delete or move to a different database. Is there any
way in System tables (Or using profiler) or some other
method to find unwanted tables and SP? I mean. I want to
run a query to find last two months not accessed objects
(Tables and SP)Renny,
As you mentioned, Profiler is your friend here. You might want to check the
Entegra auditing utility (www.lumigent.com) as well.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Renny P" <rpannikodu@.aquarion.com> wrote in message
news:0b8a01c3aeaa$2b892f30$a301280a@.phx.gbl...
> I have a live database. In that Database so many tables
> and stored procedures are not using. I have to find those
> and delete or move to a different database. Is there any
> way in System tables (Or using profiler) or some other
> method to find unwanted tables and SP? I mean. I want to
> run a query to find last two months not accessed objects
> (Tables and SP)|||You can also have a look at SQLClean from www.lockwoodtech.com, which can
actually check the source code of your application for unused database
objects if you have it.
You might also find the following script useful:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=629
to look for columns that are used but have only one distinct value in them
and similar issues.
--
Jacco Schalkwijk
SQL Server MVP
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OphQq6qrDHA.1184@.TK2MSFTNGP10.phx.gbl...
> Renny,
> As you mentioned, Profiler is your friend here. You might want to check
the
> Entegra auditing utility (www.lumigent.com) as well.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Renny P" <rpannikodu@.aquarion.com> wrote in message
> news:0b8a01c3aeaa$2b892f30$a301280a@.phx.gbl...
> > I have a live database. In that Database so many tables
> > and stored procedures are not using. I have to find those
> > and delete or move to a different database. Is there any
> > way in System tables (Or using profiler) or some other
> > method to find unwanted tables and SP? I mean. I want to
> > run a query to find last two months not accessed objects
> > (Tables and SP)
>|||Hi all,
We have a few databases that we need to convert from LATIN1_GENERAL_BIN
to UNICODE. What are the steps involved in doing this?
Thanks!sql

Active Directory and Stored Procedures

I have a need to access the active directory from a stored

procedure. I am certain that CLR is the answer but I am having a

hard time finding and example and I am a true newbie to AD and CLR.

Has anyone ever attempted to do this? I would love to see an example in VB if possible.It's certainly feasible to use SQLCLR to access AD. However, depending on how you plan to use the data coming from AD, it's not necessarily the most convenient approach. Instead of connecting to AD from managed code, you might want to consider linking to your AD store from
within SQL Server (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/joining_heterogeneous_data.asp,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_and_executing_a_view.asp,
and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_a_heterogeneous_join_between_sql_server_and_active_directory.asp for details). This would allow you to create joins directly to AD-sourced views, which may better suit your use of AD data from a stored procedure.|||Thank you, I had seen the first link and had started working with it.

I now have to find a way to convert the SID from a byte array so I can

store it in my application. I am only storing the SID and will

make "round trips" to the AD for all user information.

I appreciate your help.

Rick

Activation procedure design

I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.

I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.

In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.

Any comments?

David.

Stored procedures are compiled into execution plans that bind strictly the rowsets being involved. That means that a stored procedure cannot be compiled to issue a RECEIVE (or SELECT for the matter) against a generic 'queue', but only agains a very specific <queue_name>. This is the same reason why one cannot write a SELECT where the table name is a @.variable. The only workaround, both in SELECT and in activation case, is to use dynamic SQL, with the likely cost of having to compile the dynamic SQL when the procedure executes.

Given this it would not make sense to have the queue name passed as a parameter, it is not a performant pattern. However, if you must, there is the trick to get the queue you were activated for from sys.dm_broker_activated_tasks and build dynamic SQL to RECEIVE the messages.

HTH,

~ Remus

accuracy vs generalization

Hi!

So sorry for posting yet another one of my silly posts about the correct way of doing things!

I am using Stored Procedures to do various things in my database (inserting/deleting/updating) and using SQL Server 2000 SP4

The current situation is that I have a stored procedure which checks certain user details and returns either -1 or 1 as a success indicator to the caller.

Now, obviously if we executed several queries in this stored procedure, performance will be an issue.

However, I have no idea how to balance up the whole "more useful information" vs "general information". What I mean is this:

if we are checking 3 items in a table, I want to return the success value back to the caller - in this case, we could do an IF statement to see if the record exists, if it does, set the return value to 1, else, -1.

But then you may want to be more informative to the user, specifically stating what part of the information they entered is incorrect/invalid. So having this in mind, we would then need to execute, say, 3 queries to return a more specific "error" value.

What should I do in this case?

I want to check the username, password and if the account is activated.

Currently I have this query going on:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

firstly, is that the good way of doing things? if not - then what is the better way?

secondly, if we decide that we want a specific detailed return value/error message, it would mean I have to check the results I want using a couple more queries, such as one query to check username, one query to check password and another to check if the account is active or not and then return the appropriate value back to the caller.

is this a good way of doing things? I am confused and stuck!

Many thanks for your valuable response :)

>>Now, obviously if we executed several queries in this stored procedure, performance will be an issue.<<

This isn't necessarily true, but it is always better to minimize the number of queries.

Take this query:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

A better way to write this might be:

select ID, case when activate = 0 then 'inactive' else 'active' end as activeStatus,
case when password = @.pw then 'correct' else 'incorrect' end as pwCheck
from users
where username = @.un

Then, the caller can interpret the details as they see fit:

No result set - invalid user name, the other two possibilities are obvious.

|||Many thanks!

Accessing Views from Stored Procedures

Hi,

I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?

Thanks.

CREATEPROCEDURE [dbo].[usp_From_A_View]

AS

BEGIN

SETNOCOUNTON;

SELECT*FROM dbo.View_1

--retrieve from a view

END

|||

Thanks for the code.

I know we can't use parameters with Views, so in that case how would I go about implementing the following stored procedure if I had to split it up into a view and a sproc?

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[GetProjectInfo]
( @.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL, @.ProjTypevarchar(20) =NULL,
 @.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)
AS
BEGIN
SET NOCOUNT ON
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjIDEND )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID
END

TIA.

|||

Create 3 views as following:

Create View v_Projectasselect *from tbl_Project GO-----------Create View v_ProjectManagerasselect *from tbl_ProjectManagerGO-----------create view v_ProjectStatusasselect *from tbl_ProjectStatus GO-----------

and here is the stored procedure that used those created views:

ALTER PROCEDURE [dbo].[GetProjectInfo]
@.ProjectTitlevarchar(300) ,
@.ProjectManagerIDint ,
@.DeptCodevarchar(20) ,
@.ProjTypevarchar(20) ,
@.ProjIDvarchar(50) ,
@.DateRequesteddatetime ,
@.DueDatedatetime ,
@.ProjectStatusIDint

AS

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM vProject P, vProjectManager M, vProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjID END )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID

Hope this will help you.

Good luck.

|||

That's it!? That's pretty simple.

Ok. Great.

Thank you (both responses).

Accessing SQL Server 2005 stored procedures from Visual Studio.NET

Hello,
What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment.

Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table.

My guess is that there is security involved, if so, what do I set on the new server?
If I'm wrong, and it's something else entirely, please advise.

Thanks!

See Books Online for answers on permission required by all database operations. For example, the command to create a table is CREATE TABLE - the Permission section of the article tells you what permission you need to have to be able to perform this operation.

Thanks
Laurentiu

Accessing Second Record Set

hello all,
i had two stored procedures,where the 1st SP is calling 2nd SP(which returns 2 record sets).
how can i access the second recordset from the 1st SP
it would a great help if anyone help me out!!
thanx in advance,
ravi.WHY? I mean, you could rig up some hackneyed process that loads the data into a temp table and parses out the recordset you want, or whatever, but there is just never a good reason for building applications this way. One stored procedure should return one recordset. One function should return one recordset.|||I agree with Blindman 100% on this issue. Stored procedures can return more than one result set. This is to allow for nested procedures that each return their own result sets (like sp_help does). I can't think of any reason that a procedure should be designed to intentionally return more than one result set, other than as a user convenience "wrapper" for a group of other procedures.

-PatP

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegroups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegroups.com...
>
> Narayana Vyas Kondreddi wrote:
>> You would have to set up linked severs. For more information and
>> examples,
>> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL
>> Server
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an alias
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.
> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.|||Okay, you will have to make a change to the job step, if the server context
has to change.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegroups.com...
> Narayana Vyas Kondreddi wrote:
>> No. Instead you could have a constant linked server name, by adding an
>> alias
>> on the server, using Client Network Utility, and by adding a linked
>> server
>> using the alias name. this way you could change the server name, but
>> still
>> keep the same alias name.
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>> Anyway, makes me curious why are you movig the job to a different server?
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.
You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegro ups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>
|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =
N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =
N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.
|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.
|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegr oups.com...
>
> Narayana Vyas Kondreddi wrote:
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>
|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an alias
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.

> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.
|||Okay, you will have to make a change to the job step, if the server context
has to change.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegr oups.com...
> Narayana Vyas Kondreddi wrote:
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegroups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Serve
r
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =
N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =
N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.|||Narayana Vyas Kondreddi wrote:[vbcol=seagreen]
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server[/vbcol
]
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegroups.com...
>
> Narayana Vyas Kondreddi wrote:
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an ali
as
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.

> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.|||Okay, you will have to make a change to the job step, if the server context
has to change.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegroups.com...
> Narayana Vyas Kondreddi wrote:
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the name of the linked server. However, I cannot seem to execute a stored procedure that is resident on th
e Oracle server
The procedure is called XOKHOUS and the method is UPDADR. It takes a number of parameters and returns a 0 or 1 as a scalar value.
I've tried:
declare @.result int
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
with no luck. Suggestions? Thanks.
> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execu
te queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the
name of the linked server. However, I cannot seem to execute a stored proce
dure that is resident on th
e Oracle server
The procedure is called XOKHOUS and the method is UPDADR. It takes a numbe
r of parameters and returns a 0 or 1 as a scalar value.
I've tried:
declare @.result int
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
with no luck. Suggestions? Thanks.> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
--
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the name of the linked server. However, I cannot seem to execute a stored procedure that is resident on the Oracle serve
The procedure is called XOKHOUS and the method is UPDADR. It takes a number of parameters and returns a 0 or 1 as a scalar value
I've tried
declare @.result in
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2
with no luck. Suggestions? Thanks> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
--
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.