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

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

Stored Proc Permissions

Jan 28, 2008

I have a Active Directory group (under Organizational Groups) and I have a SQLServer group which contains that Active Directory group. In the group under Organizational Groups there are users. I've tried granting the SQLServer group execute permissions to a stored proc I developed and they are not able to execute it. However, when I extract a single user, grant them ability to get to the database and grant that single user execute permissions to the SP, she is able to run the SP.

My question is why isn't the SQLServer group, which contains the Organizational Group who this user is a member of able to execute the SP?

Thanks,
Phil

View 5 Replies View Related

SQL Server Admin 2014 :: How To Identify The Job With The Code

Apr 7, 2015

I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1

Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61

View 1 Replies View Related

Wht Should Be Stored Proc Code??

Jul 25, 2007

Hey all,
I have access as my front end and has upsized to sql server 2000.
DLookup function that was in VBA code before was working fine. but after upsizing there is the problem in that code...
What could be the alternative for DLookup Function in sql server..
Do i have to write a view or stored procedure..?

Any Help appreciated.

View 14 Replies View Related

Stored Proc In SQL Code

Dec 10, 2007

Using SQL Server 2005. Writing SQL code but I need to use the reults from two different stored proc. How do you code a select statement using a stored proc. I know when using views you just use the view name in the From or join statemnt, but do you do the same with using a Stored proc. Thank you for your help. David

View 5 Replies View Related

Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions

Sep 17, 2007

I am writing a SQL 2000 stored procedure which uses an ��EXEC @sqlString�� statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with ��Exec�� I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?

View 1 Replies View Related

Convert SQL Code To Stored Proc

Dec 23, 2004

Dear All,

Lately i have started to convert my sql code to stored procedures, however i am still a bit new on this matter.

I have the following Sql Statement which I wish to convert to a stored procedure:-

SELECT MAX(" & id & ") As maxID FROM " & table

where id and table are parameters that I am passing to this sql from a function which is called getMaxID

for example, in the vb.net code i call it as follows:-

getMaxID("personID", "persons")

Can anyone tell me how to convert it to a Stored Proc please

Thanks for your help and time

Johann

View 2 Replies View Related

Validating Stored Proc Code

May 1, 2007

This is for SQL Server 2005

The problem: I need to validate all stored procedures on my server that they have not been altered after the application has been created.

Our server installation creates the user stored procedures for the application. Once created, I need a way to validate that the proc code has not been altered.

Previously, we were validating against the value of stats_schema_ver and crdate in the sysobjects table for the given stored procedure. During installation we would create a hash value of the crdate and stats_schema_Ver values for all our user defined procs. Then during validation, we would recreate the hash value and compare to that created during installation. If they differed, we knew that the proc code had been altered in some way.

Any alteration of the stored proc would update the stats_schema_ver- if the proc was dropped and recreated, it would update the crdate.

In 2005, the stats_shema_ver value is no longer updated when the proc is altered.

Perhaps this method was completely off-base to begin with...

So, how *does* one verify after installation that no proc code has been altered since installation?

The other option was to create a hash of the proc text definition- but that seems absolutely tedious to rehash the current proc definition every time the proc is accessed. There's also the problem that if you use the "with encryption" directive, it's no longer easy to get the text definition of the proc back out.

Appreciate any general thoughts or specific feedback.

Cheers,
Eric

View 12 Replies View Related

Trigger Not Executing CLR Code/stored Proc

May 7, 2008

I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -

I have CLR integration enabled on the sql server.

The dbo has UNSAFE ASSEMBLY rights

I have the both the assembly and the serialized dll imported in the database.

Here's the definition of the stored proc -

CREATE PROCEDURE [dbo].[WriteXMLNotification]

@TaskID [nvarchar](20)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]

and the trigger -

CREATE TRIGGER [dbo].[tr_Task_U]

ON [dbo].[_Task]

FOR UPDATE, INSERT AS

IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)

BEGIN

SET NOCOUNT ON;

DECLARE @status AS INT

DECLARE @taskType AS INT

DECLARE @taskSubType AS INT

DECLARE @taskID as sysname

DECLARE @cmd as sysname

DECLARE @parentTask as sysname

DECLARE @NotificationXMLTaskID as sysname

SELECT @status = [TaskType_Status] FROM inserted

SELECT @taskType = [TaskType] FROM inserted

SELECT @taskSubType = [TaskType_SubType1] FROM inserted

SELECT @taskID = [TaskID] FROM inserted

SELECT @parentTask = [Parent_TaskID] FROM inserted

SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask

IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)

BEGIN

exec WriteXMLNotification @taskID;

END

ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)

BEGIN

exec WriteXMLNotification @parentTask;

END

END

I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?

Please advise!

Thanks in Advance,
-Mihir Sonalkar.

View 1 Replies View Related

Debug (step Into) Sql Stored Proc From Managed Code

Aug 22, 2004

I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?

Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549

Thanks.

View 3 Replies View Related

How To Call AS400 Stored Proc And Evaluate The Return Code?

May 30, 2007

I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:

Code Snippet

Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.

Task failed: SendDataToAs400 1

Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Load_ELEP.dtsx" finished: Failure.

I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.

View 3 Replies View Related

Exec SQL Task: Capture Return Code Of Stored Proc Not Working

May 19, 2006

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure

CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT

---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO

Thanks in advance

Dave

View 6 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

How To Know All Tables In A Stored Proc

Sep 28, 2007

Im trying to find a way without doing it in excel or something by hand to show what tables are used in each of my many stored procs. I didnt know if there was a way to do this with say another stored procedure or what not. Any input would be greatly appreciated.

View 3 Replies View Related

Identify ANSI/SQL-92 Non-compliant Code

Nov 4, 2006

How can we scan our code and identify the ANSI/SQL-92 non-compliant code?
Is there a tool/utility to do so?

I have already tried "SQL Best Practice Analyzer" and it seems it does not cover everything.

Thanks/

View 11 Replies View Related

Nested Stored Proc. And Global Temp Tables

Mar 2, 1999

I have an sql file that contains several queries that are generating numbers to populate a sql table. The sql file is too large for a single sp so I am nesting them. I have 4 nested stored procedures. Each of the queries in each stored procedure dumps into its own global temp table. The final stored procedure needs to insert into a sql table all the information gathered in the global temp tables. So the final stored proc. looks something like:
"Create procedure usp_myProc_4 AS EXEC usp_myProc_3
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myTempTable (which was created in usp_myProc_1)

INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myOtherTempTable

INSERT INTO......etc;"

I have done this befor and it worked fine. The only difference is that when I did this before these insert statements were being called from within an sp_makewebtask procedure.

Now when I try to save this final stored procedure it tells me "Invalid Object Name: ##myTempTable"

How do I call on these global temp tables from my final nested stored procedure?

Thanks for any help.

View 1 Replies View Related

Stored Proc Won't Update From C# .NET Code, But Will Update When Testing On Its Own.

Jul 23, 2006

I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)

/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId

IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId

IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN

View 2 Replies View Related

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

SQL Server 2014 :: SSAS Stored Procs (CLR) - Identify Real Data Type Of MDX Value Returned From Expression

Feb 13, 2015

I have a SSAS stored procedure with a signature:

public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}

with the measures being real or query-scoped calculated members.

To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.

My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.

However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.

If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?

View 0 Replies View Related

How To Identify Hot Tables

Jul 20, 2005

Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.

View 2 Replies View Related

Permissions With Sql Server Tables

Jul 20, 2005

Hello,I need some help with implenting the following:I recently migrated from access to sql server and i now i want to usemaintainable permissions on my tables, views, etc. The access database willserve as a front-end.I've created for testing purposes an testaccount with only a public role toaccess to my database.Now the hard part is when i want users to select and manipulate the datathrough views and stored procedures.I want only permissions set on views andstored procedures. The reason for this is because i don't want users to getthe data directly from tables by means of linking or importing them toaccessor other databases. Only views and stored procedures can be used.Unfortunelately it doesn't work how i wanted to. When i open a view which islinked in access as a table, i'm getting a message that the underlying tablehas not the appropiate permissions.Now there should be a way to apply a maintainable security, so if i couldhave some advice and maybe an example on this matter i would be verythankful.

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

Identify What Tables Are Being Access In SQL

Jul 20, 2005

The database I'm currently working with is very old and some of thetables, SP, and views are not being used. I'm looking for a way toidentify what items are no longer in uses, or what items arecurrently in use.

View 4 Replies View Related

Identify If A Tables Has An IDENTITY Column

Apr 1, 2008

Been poking around, but how can I tell if a an identity column exists in a table?

View 10 Replies View Related

Trying To Identify A Unique Record On Two Separate Tables

Oct 31, 2007

Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P.

View 8 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

DB Design :: How To Identify Tables That Have Been Made On Temporary Basis Not Used

Sep 9, 2015

In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?

View 2 Replies View Related

DB Engine :: Using Metadata To Identify Which Objects Update Tables?

May 6, 2015

we run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.

I am looking at two tables and would like to learn which, if any objects on same db updates these tables. I'd even live with any references to these tables. I already know sql agent is out of the picture. I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.

Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs? What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?

In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts.

I don't know that I've ever seen a way to script all triggers before. Procs I can easily script from the object explorer details. But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.

View 6 Replies View Related

Schema Permissions, Alter Proc But Not Table?

Jan 10, 2008

I want our developers to be able to alter procs owned by the dbo schema, but for data modelling reasons, I want to exclude them from creating or altering any tables in the dbo schema. I can't seem to figure out how to do this, is there a way?

Thanks,

View 1 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

Getting Proc Name From Within .Net Code

Aug 29, 2006

Hi,

I'm currently in the midst of writing my first sqlclr sproc.

In our normal T-SQL sprocs we use the following:

OBJECT_NAME(@@PROCID)

to get the name of the sproc. We later use the result of this as a parameter to a custom message when we use RAISERROR.

Question is, is there a way of doing the same from within a SQLCLR sproc? i.e. Can someone give me a bit of code that will return the name of the current sproc? (Obviously this is not the same as the name of the .Net class that implements my sproc).

Thanks in advance for any help that you can provide.

Regards

View 1 Replies View Related