I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values ------------------------------------------- PoId Podate Approved
22008-07-07 No 42007-05-05 No 52005-08-06 Yes 62006-07-07 Yes
Table B contains the following columns and values ------------------------------------------------- TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No 3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
I have two tables called A and B and C. Where A and C has the same schema A contains the following columns and values-------------------------------------------PoId Podate Approved 2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 6 2006-07-07 Yes Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status 1 A 7/7/2007 No3 B 2/4/2006 Yes Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. Pls provide me with full stored procedure code. It is very urgent.
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/ if NOT EXISTS (SELECT object_id('procedureName','p')) CREATE PROCEDURE [dbo].[procedureName] @location char(2) AS ...Procedure...
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p')) SELECT 'FALSE' ELSE SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'PROCEDURE'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@location'.
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure: Code: ( text )
I have a stored procedure which bulk inserts records into a table based on a passed in variable that contains comma separated values of record Ids.
However I have a constraint on the table ensuring that value-pairs in 2 columns must be unique (as a person can not be twice on the same project)
Since I insert the passed in person Ids in a loop, I’d like to catch if this constraint has been violated and skip that specific cycle if it has but do commit the rest.
Not sure if this can be done, and if yes could someone let me know the SQL syntax and structure please?
Am I explaining this clearly?
Thanks in advance all comments are much appreciated!
Hi. Now I am learning mssql stored procedure and I faced on some trouble. I want to make some stored procedure following as... CREATE PROCEDURE PeopleSearch ( @name nvarchar(50), @country nvarchar(50), @city nvarchar(5), @condition integer ) My question from here...But I don't know how to build the code with IF conditions. IF @condition = 0 THEN "SELECT * FROM tbl_User WHERE fname='@name'" IF @condition = 1 THEN ....SELECT Statement.. IF @condition = 2 THEN ....SELECT Statement.. I wish to make some condition in stored procedure like above style. @condition is not field and it come from outside(vb behind-code). Is it possible ? Please let me know....how do i have to do...
please help for MsSQL stored procedure for ASP.net/C#. Inside stored procedure i used 4 different statements in 'where' clause . first one is working but last 3's are not . Statements are given below-FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' -----workingFILENAME LIKE '+ @FILENAMETEXT +'%'' -----not workingSUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' ----not workingLEFT(FILENAME,4)='+@FILENAMETEXT+' -----not working bunch of code for 'where' clause are given below- DECLARE @FILENAMETEXT CHAR(4) DECLARE @CONDITION VARCHAR(2000) SET @FILENAMETEXT='http' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' please reply me as soon as possible
Is there a way to do a SELECT TOP # using a variable for the #? In other words I'm doing a SELECT TOP 50* FROM DATATABLE If I pass an @value for the number SELECT TOP @value* FROM DATATABLE doesn't work I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have. Thanks in advance.
Hi ~ I made simple stored procedure that is to update user information following as... ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) ) AS UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message. What's the problem ?
Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...
CREATE PROCEDURE dbo.CreateTable @TableName NVARCHAR(50) AS BEGIN
create table @TableName ([id] int identity(1,1),[diller] int)
Hy all. My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign) So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL. Obviously I'd like to do this: SELECT * FROM EXEC sp_fkeys @table_name = 'xy' of course it is not working this way, but I'd like something like this.
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0 i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only *** similarly when it checks the 100th record it compares it for 99 times *** similarly when it checks the 10000th record it compares it for 9999 times so here lies the problem....
when it checks the 100000th record it gets dammm slow... what i have get till now is that i have checked.....>>>>> 5000 records in 4 mins.... 25000 records in 22 mins.... and 100000 records in 20 hours....(means initially its faster but it gradually slows down) ************************************************** ************************ here i m giving the code for the procedure...... ************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON Declare @IvgId as numeric(15) Declare @Address as nvarchar(250) Declare @AddressClean as nvarchar(250) Declare @MaxLen as INT Declare @Add as nvarchar(250) Declare @Ic as int Declare @FoundIvgId as numeric(15) Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0 Begin /*here i m doing string cleaning by removing special characcters */ Select @MaxLen = len(LTRIM(RTRIM(@Address))) Select @Address = LOWER(@Address) Select @Ic = 1 Select @AddressClean = ' ' While @Ic <= @MaxLen /* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */ Begin Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End
If ascii(@Add) > 90 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End
Select @Ic = @Ic + 1 End
/* now we have removed special characters , for failure checking i m using this 'Y' */ Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y' Where IvgId = @IvgId
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N' OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0 Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)) Begin Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20) End
ELSE Begin Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0) Select @NewIvgId = @NewIvgId + 1 End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean End
Close CurData Deallocate CurData SET NOCOUNT OFF
Print 'Done................................'
************************************************** ************************** now the procedure is over....now i m writing the SQL script of DATA & RESULT table ************************************************** ************************
so now i have given the whole description of my problem....i m eagerly waiting for reply...... if anybody can help....i will be very thankful..... bye for now Bhupinder singh
I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.
Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) ) AS BEGIN UPDATE dbo.CodeCoverage SET CreationDate = GETDATE(),ProcedureName = @spName; END
GO It's generating a syntax error at PROCEDURE.
Anyone know why this might happen?
The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?
truncate table tbl_rtv_cosmetic_dif go insert tbl_RTV_COSMETIC_DIF select d.fisc_yr ,d.fisc_pd ,d.fisc_wk ,d.fisc_dy ,substring(a.long_sku,1,4) as DeptNo ,substring(a.long_sku,7,3) as VendorNo ,substring(a.long_sku,10,5) as MarkStyleNo ,a.Loc as LocNo ,convert(int,a.RsnCd) as ReasonCode ,min(a.EAN_Nbr) as UPCEANNo ,coalesce(i.own_rtl,0) as OwnedCur ,sum(convert(int,a.units)) as UPCUnits
from november a inner join generalinfo..tbl_fisc_date d on a.date = d.cal_date
left outer join itemdata..tbl_item_import_history i on substring(a.long_sku,1,4) = i.dept and substring(a.long_sku,7,3) = i.vendor and substring(a.long_sku,10,5) = i.mrk_style and d.fisc_yr = i.amc_yr and d.fisc_pd = i.amc_pd and d.fisc_wk = i.amc_wk
group by fisc_yr,fisc_pd,fisc_wk,fisc_dy ,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5) ,loc,rsncd,own_rtl
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am trying to create table with following SQL script:
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) constraint NN_Prj_Name not null, Creator nvarchar (255), CreateDate datetime );
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact) ... < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) not null, Creator nvarchar (255), CreateDate datetime ); This script executes correctly, however I want named constraints and this does not satisfy me.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
We have vendor from we puchased an application. It is going into production next week and we have our final set of table ddl and it includes stored procedures.
Since I am fairly new to MSSQL 6.5, and forced to include the stord procedures within the application, I would like to get some feedback as to whether or not stored proceures are efficient or are going to cause me problems?
Aside from periodically running sp_recompile what else will I need to do?
Any information that can be provided willl be greatly appreciated. THanks.
In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug". You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?