Connect To Another Server From A Stored Proc

Sep 21, 2004

I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?





thanks


D.

View 1 Replies


ADVERTISEMENT

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

SQL Server Stored Proc Question

Jul 26, 2004

Can anyone help me? I'm trying to assign @totalvisits the sum of visits.totalvisits in the example below. I keep getting errors concerning the line

"@totalvisits= Sum(visits.totalvisits) AS SumOfTotalVisits"

I can't figure out how to write it out correctly so the sum fills @totalvisits.

--------------------------------------------------

DECLARE @email varchar (50)
DECLARE @totalvisits int

SELECT
@email = users.email,
@totalvisits= Sum(visits.totalvisits) AS SumOfTotalVisits
LEFT JOIN visits ON users.username = visits.username
GROUP BY users.username HAVING ((users.username) = @username)

--------------------------------------------------

Any help would be appreciated!

View 1 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

Stored Proc Using Cursors On A Linked Server

Jul 13, 2004

As part of a nightly process, I must call a stored procedure that uses cursors to update a Sybase linked server from SQL Server. The stored proc was written by someone else and is intended to interface to a finance system to upload new transactions.

The stored proc works fine most of the time but occasionally dies part-way through with the following confusing error message:

[OLE/DB provider returned message: Internally generated command failed to affect a row.]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange::SetData returned 0x80004005: ].
Msg 7399, Level 16, State 1, Server ********, Procedure ********, Line 162
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.

I initially ran this stored proc from a perl script via an ODBC link but in the futile hope of fixing this problem I tried manually executing it via isql.exe instead. Both return exactly the same error. I have also increased the OLE/DB query timeout to 15 minutes with no success in stopping the problem.

There seems to be no connection I can spot between when it succeeds and when it fails. When scheduled to run just after midnight it fails about 30% of the time (at a time when there should be a light load on the servers). When run manually it almost always suceeds (I have only managed to get it to fail through running manually once in dozens of attempts).

I can't seem to find anything relevant about this error via Google or any of my usual reference sources so hoped someone else might have a clue as to what the hell's going on. :confused:

The stored procedure uses a cursor to traverse a SELECT statement then performs several INSERTs, UPDATEs and DELETEs depending upon each row processed and attempts (but usually fails) to perform a ROLLBACK upon an error.

I'd appreciate any pointers in the right direction here. Is it because I'm performing cursor-based operations on a linked server? Is it due to the linked server being a Sybase server? Could it be due to locking problems or timeouts? Is my server in a bad mood and simply trying to taunt me? I'm tearing my hair out trying to find more information on this annoyingly intermittent problem and really appreciate any help or tales of similar errors.

Thanks in advance

View 11 Replies View Related

How To Migrate Sql Server Stored Proc Into Oracle

Apr 20, 2008

hi
pls can any one help me out in how to migrate sql server stored procedures into oracle plsql procedures .

View 1 Replies View Related

Call A Oracle Stored Proc In SQL SERVER

Jul 20, 2005

We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev

View 1 Replies View Related

SQL Server Express Stored Proc Does Not Return Any Data

Jan 28, 2008

Hi!I have this table:Units  -id uniqueidentified (PK),  -groupName NVARCHAR(50) NOT NULL,  -name NVARCHAR(50) NOT NULL,  -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS    SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id                                     groupName                                          name                                               ratio                     -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks! 

View 1 Replies View Related

SQL Server Error 7405 While Compiling A Stored Proc

Jul 18, 2000

I am attempting to compile a stored proc that contains SQL statements that access databases across different SQL servers. I am getting the following error:

"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

Does this mean that I need to put the necessary statements (Set ANSI_NULLS ON etc.) in my stored proc or that I need to configure my SQL Server differently somehow? I tried the former without success.

Thanks.

View 2 Replies View Related

SELECT A Field From Another Database (on Same Server) From Stored Proc

Jul 12, 2007

Hi

Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?

thanks

View 10 Replies View Related

SQL Server 2008 :: WAITFOR Being Ignored In Stored Proc Only When Called From EF

Oct 19, 2015

We're trying to troubleshoot a timeout issue, so it was requested that I add a WAITFOR statement (1 hour) in a certain stored proc our application uses. I added it and confirmed that it was working by executing the stored proc in SSMS.

However, when our application (using Entity Framework) calls the stored proc, the WAITFOR statement is ignored.

View 4 Replies View Related

Pass Null To Stored Proc In Sql Server Mgmt

Jan 18, 2008

I'm in sql server mgmt 2005 and i highlighted a stored proc and clicked on "execute". I need to pass a null value in as the first param value to test this stored proc. what is the syntax, is below correct?

EXECUTE @RC = [SomeDataBaseName].[dbo].[sp_SomeStoredProcedure]
null
,'1234'
,1

View 3 Replies View Related

Executing A Stored Proc On Another Server From A Scheduled Task

Jul 20, 2005

Ok, I thought this one would be easy.I have a stored proc: master.dbo.restore_database_fooThis is on database server B.Database server A backs up database foo on a daily basis as a scheduledtask.What I wanted to do was, at the end of the scheduled task is then call thestored proc on B and restore the database.If I go into Query Analyzer and log into database A, then execb.master.dbo.restore_database_foo works.But if I take the same command and make it part of the scheduled task itfails.Error is:OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)[SQLSTATE 01000] (Error 7312). The step failed.To me this seems like a permissions issue, but nothing I've tried seems tohave helped.Suggestions?----Greg D. MoorePresident Green Mountain SoftwarePersonal: http://stratton.greenms.com

View 3 Replies View Related

Still Trying To Figure Out Identification Of SQL Server Stored Proc. Permissions Via VB

Jul 20, 2005

I've looked through many suggestions and partial examples all overthis newsgroup and still am not coming up with anything that doesspecifically what I'm wanting to accomplish.I'm writing a VB 6.0 application which uses SQL Server as theback-end.Here's an example of what I'm wanting to do...A user accessing the VB GUI attempts to open a certain form. Coderuns behind the scenes in VB that checks the user's "Execute"permissions on the stored procedure that retrives the data into theform. If the user is determined to have "Execute" permissions to thatstored procedure, the user is allowed to proceed with opening theform. If they don't have permissions, they are informed of this andaren't allowed to open the form and view the data.Let's say that we initially determine through the VB code that theuser has the ability to "Execute" the stored procedure that allows forviewing of the data. We go ahead and let them open the form. Oncethe form is being opened though, we run VB code to check for theirpermissions on other stored procedure that can be used in UPDATING /INSERTING / DELETING the records being displayed in the form. If theydon't have "Execute" permissions on these particularUpdate/Insert/Delete stored procedures, then I'll have a text box onthe form show as visible to advise them while they're looking at thisdata that they can't do anything to this data other than view it...they can't insert new records, they can't delete any records, and theycan't update any records.I have yet to find specific code that tells me how to checkpermissions on a storedc procedure for the user that's logged-in, whatthe resulting codes (i.e. 12291, etc.) mean as far as identificationof their permissions, etc.If anyone can help me out here, I'd greatly appreciate it. I'd hateto just have to resort to trying to allow the user to run the storedprocedures and just trap the error codes that may arise once eachstored procedure is executed and inform the user on the tail-end ofthe process that they can't run the stored procedure. I'd like toidentify their permissions to the stored procedures on the front-endof the process either before each form is opened or just as it'sopening to advise them early as to what they can/can't do with thedata being displayed in the form.Thanks in advance for any examples/information!Sincerely,Brad H. McCollumJoin Bytes!

View 1 Replies View Related

Load Blob Into DB Using A Stored Proc, SQL Server 2000

May 15, 2008



Hi,

I want to load a file into a blob column using a stored proc. The file will be on a UNC path on the network. Is there a system stored proc or any utility that can be fired off from the sp, using xp_cmdshell that will take a file path from the filesystem and load it into a blob column in a database table.

I have worked with blob fields in the past but in those instances I loaded the blobs into the database from front end ADO.net code. This time around I need to pull this off stricktly from a stored proc.

Any help will be appreciated.

thank you.

View 1 Replies View Related

Run Stored Proc From Local Server On Remote Server As A Job

Apr 30, 2002

Hi Listers
I am using sql7 on both servers
i would like to run a stored proc(which has a distributed query) as a job from my local server on a remote server.This proc checks for info on the local server zzdb and remote server xxdb for updated fields.
This info is then inserted into yydb.The stored proc is defined on the yydb.
I have set up the linked server and login.The services is using the local account.The master and target server will not serve my purposes as this job is defined on the local machine and needs to be run from the local machine.
this job fails? any help will be appreciated

TIA

View 1 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Stored Proc Calls Another Stored Proc

Jan 13, 2006

Hi all,

I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?

Thanks for your help!
Cat

View 5 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

NH: Best Practices Approach - Call Stored Proc - Or Run It Via Linked Server?

Mar 27, 2007

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff 

View 4 Replies View Related

Find Stored In Server Vide Proc For Given Table And Column

Jul 1, 2004

Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi

View 2 Replies View Related

Stored Proc Requesting Data Via An Exchange Linked Server

Jan 7, 2004

Hello,
I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised :
Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3
" Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. "
<== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider"

I would like to know if I can make run my stored proc in the admin account or what should I do to make it work

View 1 Replies View Related

SQL Server 2012 :: Run DBCC Inside Stored Proc Using EXECUTE AS

Mar 18, 2014

I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE

I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:

CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO

When I try to create this proc, I get the following error:

Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.

Ok, so I can't EXECUTE AS sa...

View 5 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

SQL Server 2012 :: Include Source Feature In More Than One Stored Proc

Sep 2, 2015

I need to use the same SQL source code in more than one stored proc.

Is there a way to -include mysql.sql or something like that ?

Is InsertSnippet the only way to do this ?

,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal

View 9 Replies View Related

How To Identify Permissions For SQL Server Tables && Stored Proc. Via VB Code

Jul 20, 2005

I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!

View 1 Replies View Related

Problem With Linked Server Stored Proc In A Maintenance Plan

Feb 12, 2007

Hello there,

I have a scenario where I need a few stored procs to auto-execute on an hourly basis so I thought it would be nicely done in a maintenance plan job list. I have experience with this in sql 2000 but I am struggling with sql 2005.

I have been struggling with my maintenance plan to successfully run the 2 jobs that it has to complete:
1) execute a stored proc that creates/updates a client in the Client table on the local server
(This step works fine without hassles)
2) execute a stored proc that synchronizes this entry with a database on another server. This stored proc works fine outside the maintenance plan, but inside the maintenance plan job it gives me an error :
Executed as user: NT AUTHORITYSYSTEM. Cannot roll back T1. No transaction or savepoint of that name was found. [SQLSTATE 25000] (Error 6401)

I have tried looking on the net and forum to see whether i can solve this but i am stuck. What do I have to keep in mind executing this stored proc as a maintenance plan? What am i missing.

Thanks for any advice
Mike

View 3 Replies View Related

SQL Server 2012 :: Stored Proc Execution Time Diff Between Environments

Jul 3, 2015

I have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.

I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.

Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved