The following 2 stored procedures are used to insert and select attachments for a web application I'm creating. The stored porcedures work in the basic sense but when I do the insert only one record is inserted. Also, when I do the select only one record is selected.
I'm wondering if I there is suppose to be some sort of record counting or looping involved to insert and return all records. If there is, I would appreciate any advice and/or examples on the proper way of doing this.
If the code is also needed I'd be glad to post it as well.
Thanks
CREATE PROCEDURE AddAttachments
(
@FILENAME varchar(200),
@FILE_PATH varchar(2000),
@T_PK int
)
AS
INSERT INTO ATTACHMENTS (FILENAME, FILE_PATH, T_PK) VALUES (@FILENAME, @FILE_PATH, @T_PK);
hi, i want to send a file as attachment to a specific mailid.i am using sp_SQLSMTPMail sp.how i wlll do it.when im passing the path of the file name,it is giving error.,but it is working fine with sending mail. can any body help me thanx in advance Ashwini Code od sp_SQLSMTPMail is attachment. when im executing like this: exec sp_SQLSMTPMail 'x.yahoo.com','y.yahoo.com','sub','body','cc','bcc ','c:x.jpg' error is giving
i have sql server 2005 and sqlserver management studio;I want to get all stored procedures in my db as a filewhat can I do to get all stored procedures together as a single file
May i know whether i can write some text/string to a file from inside a stored procedures? Currently i can retrieve data from a query and use bcp to write it to a file but i need to add some others text to the same file. Thanks for any reply!
Can anyone tell me whether i can create/open a file inside a stored procedures?? I need to retrieve data from my database and then send these data to other users by using xp_sendmail. I plan to arrange the data in a text file format. Thanks for any reply.
hi, I am new to Sql Server and I want export the stored procedures of my SQL Server 2000 database to a flat file, so I can import it later to another SQL Server 2000 database (it is not anywhere in the network).
Can anyone explain me the easiest way to do that ?
I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?
Hi I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job.
Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 )
dim strFecha as string strFecha = cstr(intFecha)
Dim f As New FileIOPermission(PermissionState.none) f.AllLocalFiles = FileIOPermissionAccess.write f.Demand()
FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10) Using connection As New SqlConnection("context connection=true") Connection.Open() dim strConsulta as string strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp"
Dim command As New SqlCommand(strConsulta, connection) Dim reader As SqlDataReader reader = command.ExecuteReader() using reader Dim intKon as integer intKon = 0
While reader.Read() fileput(1, reader.GetSqlstring(0) & chr(0), intKon) intKon = intKon + 1 End While
End Using End Using fileClose(1) End Sub End Class
As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error:
Msg 6522, Level 16, State 1, Procedure prueba1, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prueba1':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am encountering the same problem above and I did exacly as described . But it is not working.
I must send emails every month with dynamically named files as attachments. The files are named according to the date on which they are generated. For example on the first of November 2007, the file will be named myfile_1_11_2007.
I have created a variable called DynamicFileName with package scope, data type string and default value: d:\tests\
In "Send Mail Task Editor" Dialog Box, I have specified the following:
smtpConnection: smtptest.server.com From :nemo@smtptest.server.com To: nemo@smtptest.server.com Subject: Dynamic File Email MessageSourceType: Variable MessageSource: blank Priority: blank Attachments: blank
When I execute the package, I get the following errors: ----------------------------------------------------------------------------------------- Error at Send Mail Task [Send Mail Task]: Either the file "d:\tests\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.
Error at Send Mail Task: There were errors during task validation. ---------------------------------------------------------------------------------------------------
Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?
By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d: estsmyfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.
I would be most grateful if anyone could be of help
I hace an Execute SQL task which calls a sproc which contains the sp_send_mail system sproc to e-mail a set of query results . It is OK as long as the query results are small, but I get this error if they get too big: - File attachment or query results size exceeds allowable value of 1000000 bytes.
any way to change (and where to change) this value.
Hi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
Hi! i can't understand how to perform file attachment to SQL2000: <connectionStrings> <add name="SimpleListsConnectionString" connectionString="Data Source=local;AttachDbFilename=SimpleLists.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> Server return error: error 602: could not find row in sysindexes for database run dbcc checktable on sysindexes Can any body help?
BOL says to use a pipe delimiter to send multiple attachments. This works if I only have a couple of files. Once I get a few more attachments built in the expression builder I get a truncation error. If I build the whole expression in notepad and past it into the property window it also gets truncated. I see in the MSDN library that some expressions may be limited to 4000 bytes. I'm nowhere near that. Maybe a thousand bytes at most.
Question 1: Does anyone know if certain expressions have smaller byte limits?
Question 2: Is there a smaller byte limit for the file attachment value?
Question 3: Is there way I could build this string outside of the file attachment property and assign it to a variable and then use this variable in the file attachment property?
I am having difficulty creating a dynamic connection string to an Excel file. For flat files I would assign the ConnectionString property to a variable. However I keep getting errors at compile time when I do this with a Excel file. I am trying to use a ForEachFile Loop to id the Excel file and pass it to the Excel Connection Manager. The ForEachFile Loop works fine with passing the connection to a flat file. I have followed the steps in BOK http://technet.microsoft.com/en-us/library/ms345182.aspx
I keep getting the following error: TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at SLOCs [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Error at SLOCs [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error at SLOCs [DTS.Pipeline]: One or more component failed validation. Error at SLOCs: There were errors during task validation. Error at MS2 Generic Requirements [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.". (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK ------------------------------
Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
I would like to use logging to be able to view information about the package execution afterwards, especially to be able to find out which task that failed and why it failed.
Something similar to this:
----------------------------------------------------------------------------- The execution of the following DTS Package succeeded:
Step 'DTSStep_DTSActiveScriptTask_2' was not executed
Step 'DTSStep_DTSExecuteSQLTask_33' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server Step Error Description:The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e2f): Cannot insert the value NULL into column 'ID_adress', table 'VPKBA.dbo.aktAdress'; column does not allow nulls. INSERT fails.) Step Error code: 80040E2F Step Error Help File: Step Error Help Context ID:0
Hello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest. The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market. In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below? I've considered the CASE statement but was not clear on how it would work. Any suggestions would be great.
set nocount on select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id
select @totcount = count(*) from t_job_vstat where job_id = @job_id
select @totcount = @totcount + 3
DECLARE Cand_Cursor CURSOR FOR
select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and t_cand_vstat.key_skill_id = t_key_skill.key_skill_id order by candidate_id
CREATE TABLE #ReturnTemp ( candidateid uniqueidentifier NOT NULL, displayid int, city varchar(50), state varchar(2), Industry varchar(50), travel varchar(50), position varchar(50), hitcount smallint, tpercent numeric, ks1 varchar(50), ks2 varchar(50), ks3 varchar(50), ks4 varchar(50), ks5 varchar(50) )
OPEN Cand_Cursor
declare @candidate_id uniqueidentifier
FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest select @holdid = @candidate_id
WHILE @@FETCH_STATUS = 0 BEGIN
if @candidate_id <> @holdid begin select @icount = @icount + 1 if @match = 1 update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid select @match = 1 select @ks1 = "" select @ks2 = "" select @ks3 = "" select @ks4 = "" select @ks5 = "" select @holdid = @candidate_id select @icount = 1 select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id if @candposition = @jobposition select @icount = @icount + 10 if @candyears = @jobyears select @icount = @icount + 8 if @candtravel = @jobtravel begin select @icount = @icount + 2 end
else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin select @match = 0 end else begin
select @icount = @icount + 1 end end
DECLARE City_Cursor CURSOR FOR
select distinct city, state from t_cand_pref_city_state C, t_city_state S where c.city_state = s.city_state and C.candidate_id = @candidate_id
OPEN City_Cursor
FETCH NEXT FROM City_Cursor into @prefcity, @prefstate
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM City_cursor into @prefcity, @prefstate select @citymatch = 0 if ((@prefcity = @jobcity) and (@prefstate = @jobstate)) begin --do nothing select @citymatch = 1 select @icount = @icount + 1 end
END
CLOSE City_Cursor
DEALLOCATE City_Cursor
if @citymatch = 0 select @match = 0
if @candindustry <> @jobindustry select @match = 0 if @strongest = 1 begin if @ks1 = "" select @ks1 = @key_skill_desc else if @ks2 = "" select @ks2 = @key_skill_desc else if @ks3 = "" select @ks3 = @key_skill_desc else if @ks4 = "" select @ks4 = @key_skill_desc else if @ks5 = "" select @ks5 = @key_skill_desc end if @match = 1 begin
select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry select @candPositiondesc = position_desc from t_position where position_id = @candPosition select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel
INSERT INTO #ReturnTemp(Candidateid, displayid, city, state, Industry, travel, position, hitcount)
Hi,I'm trying to work around a bug that our helpdesk software has. When a newissue is created, it cannot automatically default 2 fields to the value ofNo like we need it to.I have a field called "Audited" and one called "Billed to Client". When anew issue is openned, it just leaves the value as Null in the databaseinstead of a value of No.I would like to create a stored procedure and schedule it to run every 10minutes to change any value of Null in those columns to No.Database: bridgetrakTable: IssuesColumn: AuditedColumn: BilledIf someone could help me out that would be great! I just don't have verymuch experience with SQL statements.Please email me at Join Bytes!Thanks,Shawn
I have report which accepts a card number and fromdate and todate as parameters to the report. This report needs to be sent on a quarterly basis to each of the customer mail id to which their card number is linked. I am getting all this information from a database and sending as an attachment to the customer. Now I would need making these filename's which are attached to be dynamic based on the input parameters.
In datadriven subscriptions, the option of include report has only true or false values and another option was to take from a database. I tried putting the dynamic file name in the database and getting the value from the database but no success, the subscription itself is failing here. I guess I am doing something wrong here by binding the report name from the value which I am getting from DB to the actual report name.
If report name = Mytransactions, and the parameters passed are Card = 123,fromdate = 1/1/2010,todate = 31/3/2010.
Now in the attachment the file name should be something like "Mytransactions_123_January1st2010_March31st2010". How to make the filename dynamic.
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind? Stored ProcedureCREATE PROCEDURE InsertNearMiss @Branch Int, @Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@Branch) SET @Identity = SCOPE_IDENTITY() GO
This is a fairly detailed problem, so this will be a long post... I do appologize. I have been agonizing over this now for over a week and cannot find a viable solution. Hopefully one of you can help.
First off, I work for a realestate company, and this query will display a list of properties based on a number of different criteria and criteria types. There are multiple tables involved:
dbo.Prop: Property Database. Holds basic info about each property
dbo.Prop_Features: holds all the features (such as Pool, Carpet, Drapes etc) for each property. The only information stored in this table are PropID and FeatureID (PropID being the Identity of the Prop table, FeatureID being the Identity of the Features Table)
dbo.Features Holds information on each possable "feature" in the system.
dbo.Members Holds basic information and criteria for each of our members.
dbo.Members_Features This table holds the MemberID and FeatureID where members have chosen one ore more features to be used for criteria when searching for a property.
Ok, now... That said, here is my problem. The query I had written (by a professional hired thru Robert Half Technologies) takes over 30 seconds to execute. I will post a copy of that SP below. This is unacceptable. We have to process thousands of these per hour, and a 30 second process time is very bad. Can any of you give me a idea of how to better approach this problem?
In the code below, you will notice there are other tables I did not mention - they are not important. The Speed problem is surrounding a single function, which I will mention below.
CREATE PROCEDURE dbo.Member_Get_List ( @MemberID Int, @UpdatesOnly Bit
SET @PropCount = (SELECT Count(*) FROM Members_Features WHERE MemberID = @MemberID ) END
IF @PropCount = 0 BEGIN
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10), P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End as 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage
AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
CREATE TABLE #Prop ( PropID int, Bedrooms tinyint, Baths float, Garage float, DisplayText varchar(75), Rent float, Address varchar(100), Xstreets varchar(100), DateAvailable varchar(10), Lease tinyint, Features Varchar(3500), Deposit float, Phone1 varchar(12), AreaName Varchar(50), County Varchar(30), City Varchar(30), State Varchar(75) )
DECLARE curProp Cursor FORWARD_ONLY for
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10),P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features',
This function is the cause of the speed problem, methinks. When run alone, it takes 29 seconds to process with the same MemberID that takes about 33 seconds to process completely (the whole Proc). This function simply returns a list of comma delimited features for each property, for displaying on a customer list. Here is that function:
CREATE Function dbo.Prop_Get_Feature_List_Fun (@PropID int) RETURNS Varchar(3500)
AS BEGIN
Declare @FeatureList Varchar(3500)
select @FeatureList = Coalesce(@FeatureList + ', ', '' ) + F.FeatureName FROM Prop P
INNER JOIN Prop_Features PF
ON P.PropID = PF.PropID
INNER JOIN Features F
ON PF.FeatureID = F.FeatureID
WHERE
P.PropID = @PropID
ORDER BY
F.FeatureName
Set @FeatureList = isnull(@FeatureList,'Please call for features.')
RETURN @FeatureList END
Now, I know I probably gave lots more information then needed to solve this issue - but its better to have too much then not enough.
Any help at all in speeding up this function or describing another way to do this would be most appreciative.
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
