Getting Data Fields In Stored Procedures
Aug 18, 2004Hello,
How can I get the name of the fields along with datatypes of a stored procedures.
Thanks in advance,
Uday.
ADVERTISEMENT
Calculated Fields In Queries Using Stored Procedures
Dec 20, 1999Help!
I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
Thanks.
Problem In Stored Procedures When I Change The Name Of The Tables Of Fields
Oct 5, 2004Hi My friends
I have develop big Database System in Microsoft SQL Server 2000 and my DB System content more then 30 Table and also more then 150 Stored procedure and from that stored procedures I have call my the tables
But the problem when I change the name of any table, SQL Server don’t prompt my about the stored procedure that use that table .. so I cant remember witch Stored procedure use that table
By example if I am in programming language like C# or VB.NET when I try to change any name of any class the compiles catch all the error that coming from changing the name of the class and simply I will fix the problem because the compiler help me and list all the object content the error
But in SQL Server how can I search for stored procedure that use un existing table like compiler in any programming language ??
Or is there any why to solve my problem ??
And the above problem also there when I change the filed name
And thanks with my best regards
Fraas
Data Access :: Transfer Data From One To Another Table On Another Server Using Stored Procedures
Jun 9, 2013I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies View RelatedCan Anyone Tell Me How To Access Data From Stored Procedures Using Data Adapters?
May 21, 2008Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?
View 1 Replies View RelatedC# Stored Procedure Processing Binary Data To Sql Fields
Jul 25, 2007
In the code sample below, case eLABEL, eENGUNITS works ok. The target SQL field is defined as varchar(50).
The second section is not so happy. It is attempting to write to an SQL field defined as binary(2)
Executing an SQL script to excercise this line results in error:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'foreign'
where 'foreign' is the name (FieldDef.sFldName) of the SQL field being written.
The c# code composes the following command:
"UPDATE " + acPointType + " SET " + FieldDef.sFldName +
" = @data_params WHERE VEC = '" + acVECName +
"' and name = '" + acPointName + "';";
What is the proper syntax for the second case set?
Code Snippet
case vcidatatype.eLABEL:
case vcidatatype.eENGUNITS:
{
byte[] bbuff = new byte[512];
bbuff = rdr.ReadBytes(FieldDef.iLen);
vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.VarBinary));
vciSqlCommand.Parameters["@data_params"].Value = bbuff;
break;
}
case vcidatatype.eFIDADR:
case vcidatatype.eLANADR:
{
byte[] bbuf = new byte[512] ;
bbuf = rdr.ReadBytes(2);
vciSqlCommand.Parameters.Add(new SqlParameter("@data_params", SqlDbType.Binary));
vciSqlCommand.Parameters["@data_params"].Value = bbuf;
break;
}
Stored Procedures For Inserting And Deleting Data
May 2, 2007Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.
SELECT Data: Stored Procedures Or View?
May 10, 2007Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
View 3 Replies View RelatedData Warehousing :: Run Stored Procedures On PDW Via SSIS
Aug 4, 2015How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW. PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
View 3 Replies View RelatedData Warehousing :: How To Use Stored Procedures In SSIS
Jul 23, 2015How to use Stored Procedures in SSIS?
View 2 Replies View RelatedStored Procedures Not Possible As Data Source View?
Apr 3, 2007only the tables and views are shown in the wizard (BIDS) thanks...
View 1 Replies View RelatedOracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I 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 RelatedUsing Stored Procedures To Insert And Pull Data From Database
Mar 21, 2008I 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!!!
Stored Procedures Or C# Code For My Purpose (Data Synchronisation)
Jul 4, 2005Hi all,
I am in the position where I have to transfer data from an old database
schema to a new database schema. During the transfer process alot of
logic has to be performed so that the old data gets inserted into the
new tables and are efficiently done so that all foreign keys are
remained and newly created keys (as the new schema is Normalised alot
more) are correct.
Is it best if I perform all this logic in a Stored Procedure or in C# code (where the queries will also be run)?
Tryst
User Defined Data Types And Stored Procedures
Mar 13, 2001I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO
Dave
Array Data Type In SQL Server Stored Procedures?
Aug 17, 2000Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins
Ref: Stored Procedures, Mixed Data Type In Sql Command
Mar 20, 2006Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:
CREATE PROCEDURE KPISU_F_TotalByF
@inputT_From varchar(10),
@inputT_To varchar(10),
@TableName varchar(1000)
AS
-----------------------------------------------------
--------input variable-------------------------------
DECLARE @inputTerm_From varchar(10),
@inputTerm_To varchar(10),
@sql_empty varchar(2000),
@sql_refresh varchar(2000)
----------------------------------------------------
IF EXISTS (select * from tempdb.dbo.sysobjects
where id LIKE object_id('tempdb..#tmpOTLTotalByF'))
DROP TABLE #tmpOTLTotalByF
CREATE TABLE #tmpOTLTotalByF (Faculty varchar(50),Term_From varchar(10), Total_G12 int, Total_G3 int, Total_G4 int, Total_Faculty int)
DECLARE @iFaculty varchar(50),
@iTerm_From varchar(10),
@iTotal_G12 int,
@iTotal_G3 int,
@iTotal_G4 int,
@iTotal_Faculty int
SET @iTotal_Faculty = 0
SET @iTotal_G12 = 0
SET @iTotal_G3 = 0
SET @iTotal_G4 = 0
DECLARE su_OTL_F_cursor CURSOR
FOR
SELECT Faculty, Term_From, SUM(Grades_12), SUM(Grades_3), SUM(Grades_4)
FROM #tmpOTLTotalByFaculty
GROUP BY Faculty, Term_From
OPEN su_OTL_F_cursor
FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql_refresh = 'INSERT '
SELECT @sql_refresh = @sql_refresh + @TableName
SELECT @sql_refresh = @sql_refresh + ' VALUES (' + @iFaculty + ', ' + @iTerm_From + ', ' + @iTotal_G12 + ', ' + @iTarget_12 + ', ' + @iTotal_G3 + ', ' + @iTarget_3 + ', ' + @iTotal_G4 + ', ' + @iTarget_4 + ', ' + @iTotal_Faculty + ')'
SET @iTotal_Faculty = 0
SET @iTotal_Faculty = @iTotal_G12 + @iTotal_G3 + @iTotal_G4
INSERT #tmpOTLTotalByF VALUES (@iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4, @iTotal_Faculty)
Exec ( @sql_refresh)
FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4
END
CLOSE su_OTL_F_cursor -----line 222
DEALLOCATE su_OTL_F_cursor
CLOSE su_OTL_T_cursor -----line 63
DEALLOCATE su_OTL_T_cursor
SELECT * FROM #tmpOTLTotalByF ORDER BY Faculty
SELECT * FROM KPISU_F_OTLTotalByF05 ORDER BY Faculty
GO
EXECUTE KPISU_F_TotalByF '2005', '2006', 'KPISU_F_OTLTotalByF05'
GO
----------------------------------------------------------
I got the following error message:
Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256
Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int.
-----------------------------------------------------------
I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?
Many thanks.
Su"
Stored Procedures - Insert Data Gathered From Sele
Mar 28, 2008Hi,
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
SQL Server 2012 :: How To Write Stored Procedures To Load Data Model From OLTP To DWH
Nov 24, 2014How to write Stored Procedures to load the Data Model from OLTP to DWH ?
View 9 Replies View RelatedStored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
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
How To Automated Send Email To Inform The Status Number Of Data In Database Using Stored Procedures?
Feb 23, 2008Hi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;
Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
All My Stored Procedures Are Getting Created As System Procedures!
Nov 6, 2007
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
Update Fields With Data From Other Fields In Same Row
Jun 30, 2000Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures
Apr 29, 2008How 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'
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems 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 RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
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!
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We 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!
How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello 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.
SSIS And Stored Procedures Results Stored In #Tables
Mar 26, 2008Hello
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
MS SQL Stored Procedures Inside Another Stored Procedure
Jun 16, 2007Hi,
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
-------
Calling Stored Procedures From Another Stored Procedure
May 8, 2008I 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
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi 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?