hi
i need to insert the list of ipaddress using stored procedures.
the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values.
how to do this..
Hi,I'm trying to insert some values into 2 tables using stored
procedures (which should be pretty straight forward) but I'm running
into problems.Given below are my 2 sp that I'm using: USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddToProject] (@DeptCode varchar(20), @ProjectTitle varchar(300), @ProjectDetails varchar(3000), @ProjectManagerID int, @RequestedBy varchar(100), @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @Dept varchar(50), @ProjID varchar(50)
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
EXEC ('INSERT INTO dbo.tbl_ProjectNumber' + @Dept + '(DeptCode) VALUES(' + @Dept + ')')
EXEC GetProjID @Dept, @ProjID OUTPUT
INSERT INTO dbo.tbl_Project (ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID)
VALUES (@ProjID, @ProjectTitle, @ProjectDetails, @ProjectManagerID, @RequestedBy, @DateRequested, @DueDate, @ProjectStatusID);
COMMIT TRANSACTION
---------------------------------------------------------------------
USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjID] (@DeptCode varchar(20), @ProjID varchar(50) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ProjectNumber varchar(100), @Table varchar(100)
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SET @Table = 'dbo.tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
EXEC ( 'SELECT @ProjID = MAX('+@ProjectNumber + ') FROM ' + @Table )
SET @ProjID = @ProjID + '-' + @DeptCode
END When I run the AddToProject sp using the following values: USE [DBName]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[AddToProject]
@DeptCode = N'BAT',
@ProjectTitle = N'Some Title',
@ProjectDetails = N'Some Details',
@ProjectManagerID = 3,
@RequestedBy = N'Me',
@DateRequested = N'04/27/2007',
@DueDate = N'05/04/2007',
@ProjectStatusID = 4
SELECT'Return Value' = @return_value
GO
I get the following errors: Msg 128, Level 15, State 1, Line 1The name "BAT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@ProjID".Msg 515, Level 16, State 2, Procedure AddToProject, Line 17Cannot insert the value NULL into column 'ProjID', table 'NYCEDC.dbo.tbl_Project'; column does not allow nulls. INSERT fails. If I run just the GetProjID sp, I get the following error: Must declare the scalar variable "@ProjID". -------------------------------------What I'm trying to do is, get a bunch of values from the aspx page, insert the DeptCode into the field DeptCode of the table 'tbl_ProjectNumber'+DeptCode so that the IDENTITY value field 'ProjectNumber'+DeptCode can increment by 1. After 'ProjectNumber'+DeptCode has incremented, I want to run the GetProjID sp so that I can insert ProjID into the 'tbl_Project' table along with the other values taken from the aspx page. ProjID is the primary key for the 'tbl_Project' table so it needs to be added along with the other values (other they obviously can't be added). I'm running the AddToProject as a Transaction to make sure that if more than 1 person is adding a project at the same time, the project numbers and other information don't get messed up. Any help would be appreciated.Thanks.
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
View 5 Replies View RelatedI am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
I have my database: "RequestTrack"
My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:
txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text
**After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I setup the databse and Visual Web Developer to use
stored procedures when the insert command is used. The
database also uses the field UserName that I pass using a
SessionParameter within the InserParameter block from the
Membership.GetUser.Username from the aspnet_ tables.
My difficulty is when using the "Formview" as the body to
with which to insert (I wanted the design versatility of
FormView) I get an error: "system.formatExpression: Input
string was not in a correct format" when I leave the
textboxes empty and invoke the insert command. I first
assumed that the bound textboxes are not being converted
correctly when left blank, so I used the
ConvertEmptyStringToNull=True, with no success. I then
coupled that with the Type=[correct format] still with
the same error. Alas, my final attempt was to set
defaults in the "ALTER PROCEDURE" within the stored
procedure itself.
Any help would be most appreciated. Thanks.
.
Hi,
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
Thanks
David
CREATE PROCEDURE UserAssign_Location
@UserID Int, @AreaID Int
AS
BEGIN
SET NOCOUNT ON;
SELECT * From Locations Where EntryID = @AreaID
INSERT INTO Members_Locations (UserID, Location1, Location2, Location3, Location4, Active)
VALUES (@UserID, 'AREA1', 'AREA2', 'AREA3', 'AREA4', 1)
END
GO
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies View RelatedI 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!!
View 11 Replies View Relatedi ve a filed named "Email" of datatype .i ve created this field to insert email address in this field.but when i insert an email address like "bilal@yahoo.com" from a textbox wh is in an ASP.net webpage then in the filed i see this "System.Web.UI.WebControls.TextBox".
can u plz tell me how to fix this
Regards
Ahmed Bilal Jan
Hi,
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.
Thank you in advance for any help on this matter
Can anybody tell me if a stored procedure exists that pings a server and returns the ip address from within SQL Server email address. Any assistance will be highly appreciated.
View 2 Replies View Related
Hi I was wondering if anyone else was running into the problem where SSIS inserts data into a range incorrectly.
I have a Raw File Source in a Data Flow. The data is manipulated and then defined to insert into a named range within an Excel Worksheet. I have a task prior to the data flow that takes a template and copies it to another location. Within the Template I have defined several named ranges.
The Data Flow inserts the data in the line below the named range. For instance if I have a named range defined as B1:C1
the insert occurs on B2:C2
Does anyone know why this is happening or how I can get it to insert into the Range I have specified?
Thanks in advance!
I am trying to pull MTD and YTD sales for this year and last year.
I have this line to pull all invoices between two dates:
WHERE (invc_hdr.ivh_dt Between '08/01/2013' And '08/31/13')
But I want to be able to put the dates in without having to change the code each time. I want a window to pop up so I can input the date range. Is there a way to do this.
I need to import some data into a table that is in a merge bi-directional publication. The table has an identity column that is being "auto managed". The ID numbers I need to import are not within any of the assigned "ranges". They are less than any of the current ranges.
Turning ident_insert on does not work because of the check constraints error. The merge replication triggers validate the ID range as well.
Edit: using SQL 2005 standard publisher and subsciber. Publication is 2005.
I'd like to use a stored procedure to insert large amounts of recordsinto a table. My field A should be filled with a given range ofnumbers. I do the following ... but I'm sure there is a better(faster) way:select @start = max(A) from tbl where B = 'test1' and C = 'test2'while @start <= 500000begininsert into tbl (A, B, C)values (@start, 'test1', test2')set @start = @start +1endanother question is, how to prevent that another user inserts the samenumbers into the field A?Thanks a lot for any help!ratu
View 5 Replies View Related
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?
Thanks,
Jason
I want insert automatically records for Range that user definded
For example user define :
from to city
10000 12000 tehran
15000 19000 babol
I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)
Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.
E.g. -
Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981
INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1
And I want to get Table3
'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981
How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.
On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.
I have two tables, customers and appointments.
I want to bulk insert records for a date range and a day of the week.
For example, John Smith has an appointment on every monday for the next three months. How do I accomplish this?
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
View 2 Replies View RelatedHow 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'
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
-- Procedure
CREATE PROCEDURE sp_009_SiteLead
@sDatenVarChar(10)
AS
--DECLARE @CurDate datetime
DECLARE @StartMonth int
DECLARE @StartYear int
DECLARE @StartDay int
DECLARE @StartDate varchar(10)
SET @StartMonth = DATEPART( month, Convert ( datetime, @sDate ))
SET @StartYear = DATEPART( year, Convert ( datetime, @sDate ))
SET @StartDay = DATEPART( day, Convert ( datetime, @sDate ))
SET @startDate = CONVERT( varchar(2), @StartMonth ) + "/" + CONVERT( varchar(2), @StartDay ) + "/" + CONVERT( varchar(4), @StartYear )
Print @startDate
SELECT *
FROMtbl_Feedback
WHERE tbl_Feedback.DateIn >= @startDate
GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead
' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command")
set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput)
set cmd.ActiveConnection=DBConnection
cmd.CommandText="sp_009_SiteLead"
cmd.Parameters.Append peDate
cmd.Parameters.Append psdate
psDate.Value = "10/01/2004"
Set rsObj = CreateObject("ADODB.Recordset")
rsObj.ActiveConnection = DBConnection
rsObj.CursorLocation = adUseClient
rsObj.PageSize= 20
rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it.
Can anyone help?
Thanks,
Chad
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?
View 3 Replies View RelatedGreetings:
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?
Thanks!
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"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0
,@OrderID INT =0
,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE')
,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John