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!!
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"?
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)?
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?
hi it looks like my thread has been deleted but I keep on asking. When trying to connect to a table i a SQL2005 database in a vb.Net webapplication I get the following error message from the wizard: "The wizard detected the following errors when configuring the data adapter "SqlDataAdapter1".
"Check" Generated SELECT Statement "Check" Generated Mappings "Warning" Generated INSERT Statement There were errors configuring the data adapter "Warning" Generated UPDATE Statement There were errors configuring the data adapter "Warning" Generated DELETE Statement There were errors configuring the data adapter"
The same connection works fine in a vb6 application. I have seen other threads concerning this in this forum but I can't find any answers. Any ideas anyone? ciao chris
Hi guys. Does anybody have any clue about how to create a class which inherits from SqlDataAdapter? I tried but looks like SqlDataAdapter is not inheritable. I would like to extend it with some custom method such as a method which fills a Dataset (used with a DataGrid) with the only few records to display per page (say 20) against the thousand records it might instead contain otherwise.Any workarounds? Many thanks in advance for your help.
Hi I am writing an app in flash which needs to hook up to MS SQL via asp.I need the code below to pass the var (ptodaysDate) to the sql statement. I can hard code it in and it works great but I really need to pass the var from flash.Pulling my hair out here, not much left to go.Any help greatly appreciated.---------------------------------------------- [WebMethod] public Schedule[] getSchedule(int ptodaysDate) { SqlDataAdapter adpt = new SqlDataAdapter("SELECT scheduleID, roomName, eventType,unitName,groupName,staffName,staffName2,theDate,theEnd FROM tb_schedule Where theDate >= @rtodaysDate", connString); SqlParameter rtodaysDate = new SqlParameter("@rtodaysDate", ptodaysDate); DataSet ds = new DataSet(); ArrayList al = new ArrayList(); adpt.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { Schedule obj = new Schedule(); obj.scheduleID = (int)row["scheduleID"]; obj.roomName = (string)row["roomName"]; obj.eventType = (string)row["eventType"]; obj.unitName = (string)row["unitName"]; obj.groupName = (string)row["groupName"]; obj.staffName = (string)row["staffName"]; obj.staffName2 = (string)row["staffName2"]; obj.theDate = (string)row["theDate"]; obj.theEnd = (string)row["theEnd"]; al.Add(obj); } Schedule[] outArray = (Schedule[])al.ToArray(typeof(Schedule)); return outArray; } public class Schedule { public int scheduleID; public string roomName; public string eventType; public string unitName; public string groupName; public string staffName; public string staffName2; public string theDate; public string theEnd; }
It's a pretty basic question but I haven't been able to find any examples out there. I dimmed a dataadapter and would like to reuse later in my code (line 3 in the code below). What is the correct syntax to do this? Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)da.Fill(myDataTable)da.______ ("SELECT * FROM myTable2", conn)da.Fill(myDataTable2)
I'm working on a project to dynamically create PDFs with content from an SQL server. My current approach is to get the data I need into a DataSet using SqlDataAdapter, write the DataSet to a stream as XML, transform the XML into FO and then output a pdf using FOP. For some reason I get the following exception "System.NullReferenceException - Object reference not set to an instance of an object" when I try to set the SelectCommand property of my data adapter. Code for the project follows:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim con As SqlConnection Dim cmd As SqlCommand Dim IDlist As String Dim keyidary As Array Dim query As String Dim tempxml As Stream query = "*****query is valid and very long, so it's not included here*****" IDlist = Request.Form("chosenIDs") keyidary = Split(IDlist, ",") Dim makequery As New StringBuilder(query) For Each ID As String In keyidary makequery.Append(" OR (KeyID = '" & ID & "')") Next query = makequery.ToString() 'Response.Write(query) When uncommented this prints the query just fine 'Response.End() Try con = New SqlConnection con.ConnectionString = "****Connection String is valid****" cmd = New SqlCommand cmd.CommandText = query cmd.CommandType = CommandType.Text cmd.Connection = con Dim adpt As SqlDataAdapter adpt.SelectCommand = cmd 'Response.Write(query) When these are above or between the previous 2 statements, query is printed, 'Response.End() otherwise I get the error described above. Dim profiles As New DataSet adpt.Fill(profiles) profiles.WriteXml(tempxml) Dim step1 As XslTransform = New XslTransform Dim step2 As XslTransform = New XslTransform step1.Load(Server.MapPath("TransAttmpt1.xslt")) step2.Load(Server.MapPath("formatXML.xsl")) Dim xml, pdf As String xml = "profiles.xml" pdf = "Profiles.pdf" Dim temp2xml As New XPathDocument(tempxml) Response.Write(query) Response.End() Dim midstream As Stream Dim finalxml As StreamWriter = New StreamWriter(xml) step1.Transform(temp2xml, Nothing, midstream, Nothing) Dim xpathdoc2 As XPathDocument = New XPathDocument(midstream) step2.Transform(xpathdoc2, Nothing, finalxml, Nothing) GeneratePDF(xml, pdf) 'There's a lot more but it doesn't seem relevant now.... I'm somewhat at a loss on how to proceed and any help is very greatly appreciated.Thanks!
Hi all, I have datatable having around 50 rows and 3 columns ID, Name and ExpVal, which is an expression columns,where the values can be any SQL functions Like REPLICATE(), SOUNDEX ( 'value' ) Or REVERSE ( 'value' )..... i want to insert each row in that datatable like INSERT INTO TAB1 ( ID, Name, ExpVal) VALUES (1, 'some name', SOUNDEX ( 'some name' ) ) so that the ExpVal will have value of the function ie inserted row look like ID Name ExpVal1 some name S500 <--- Result of SOUNDEX ( 'some name' ) I'm using sqldatadapter to insert these values to the database string sql = "INSERT INTO TAB1 (ID, Name, ExpVal)VALUES (@ID, @Name, @ExpVal) ";SqlDataAdapter sqlAdptr = new SqlDataAdapter();SqlCommand sqlCmd = new SqlCommand(sql, con);sqlCmd.parameters.Add("@ID", SqlDbType.Int, 0, "ID");sqlCmd.parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");sqlCmd.parameters.Add("@ExpVal", SqlDbType.VarChar, 100, "ExpVal");sqlAdptr.InsertCommand = sqlCmd;sqlAdptr.Update(dataTable); This works fine, but the problem is, now the TAB1 contains ID Name ExpVal1 some name 'SOUNDEX ( 'some name' )' instead of S500 Thatis the sql funtion is passed by SqlDataAdapter to database as a string and it is not executing while row is inserted to the table.Please provide what changes i have to make if i want SOUNDEX ( 'some name' ) to executed while data insertion take place Thanks in advance
I'm getting a strange error with my dataadapter. Here's my code: using (SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { SqlDataAdapter da = new SqlDataAdapter("sprocCUSTOM", conn); da.SelectCommand.Parameters.AddWithValue("@eventID", EventID);
conn.Open(); //open connection DataSet ds = new DataSet(); da.Fill(ds); gvAttendees.DataSource = ds; gvAttendees.DataBind(); }This is the error I'm getting: Incorrect syntax near 'sprocCUSTOM'.But the error is highlighted at the line that reads "da.Fill(ds);"There's nothing wrong with the stored procedure either. It exists and works fine.
I am using SqlDataAdapter.Update(DataSet) to insert records into multiple tables in one call, but for some reason only first table was inserted.
Dim _cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim _sql As String = "Select a,b FROM Table1;Select d, c From Table2;" Dim _da As New SqlDataAdapter(_sql, _cnn) Dim _sqlCmdBdr As New SqlCommandBuilder(_da) Dim _ds As New DataSet _da.Fill(_ds) Dim _newrow1 As DataRow = _ds.Tables(0).NewRow _newrow1("a") = "NewA" _newrow1("b") = "NewB" _ds.Tables(0).Rows.Add(_newrow1)
Dim _newrow2 As DataRow = _ds.Tables(1).NewRow _newrow2("d") = "NewD" _newrow2("c") = "NewC" _ds.Tables(1).Rows.Add(_newrow2)
_da.Update(_ds)
new record inserted only into Table1, no new rows in Table2
I am porting an ASP.NET/C# application from Microsoft Access to SQL Server 2005. In my code I have a method that uses the DataAdapter class to open a table, read some values out of it which are stored, open a second table, read some additional data from it storing the data in the same structure as used before, then updating the first table with the combined data.
When I execute the dataAdapter.Update() method I get the following error:
"Dynamic SQL generation for the Update Command is not supported against a Select Command that does not return any key column information."
I am uncertain what this error is about. This code worked fine against Microsoft Access so it must be something different in how SQL Server works.
Can anyone tell me what this error is complaining about?
Since my select command involves multiple tables, I manually write the insert command, proplating the SqlParameters as follows: string insCmdText = "INSERT INTO itcac_alan.COURSE " + " ([COURSE_ID], [COURSE_NAME], [COURSE_NO], [BEGIN_DATE], [END_DATE], [CREATER]) " + " VALUES(@COURSE_ID, @COURSE_NAME, @COURSE_NO, @BEGIN_DATE, @END_DATE, @CREATER)"; SqlCommand insCmd = new SqlCommand(insCmdText, conn); insCmd.Parameters.Add("@COURSE_ID", SqlDbType.VarChar); insCmd.Parameters.Add("@COURSE_NAME", SqlDbType.VarChar); insCmd.Parameters.Add("@COURSE_NO", SqlDbType.VarChar); insCmd.Parameters.Add("@BEGIN_DATE", SqlDbType.DateTime); insCmd.Parameters.Add("@END_DATE", SqlDbType.DateTime); insCmd.Parameters.Add("@CREATER", SqlDbType.VarChar); da.InsertCommand = insCmd; // da is a data adapter
However, as I use the preceding adapter to update the underlying database which in fact is based on a disconnected DataRow, an exception is thrown: The parameterized query '(@COURSE_ID varchar(8000),@COURSE_NAME varchar(8000),@COURSE_NO ' expects the parameter '@COURSE_ID', which was not supplied. The code I update the database is as follows: DataRow newRow = sourceTable.NewRow(); //sourceTable is a DataTable whose data rows are populated with da (SqlDataAdpater) in the preceding code // Populating the newRow Hashtable newValues = new Hashtable(); ..................... foreach (DictionaryEntry entry in newValues) { // entry.Key is identical to a column name in the newRow newRow[(string)entry.Key] = (entry.Value == null ? DBNull.Value : entry.Value); } sourceTable.Rows.Add(newRow); adapter.SelectCommand.Connection.Open(); adapter.Update(sourceTable); // Exception is thrown here adapter.SelectCommand.Connection.Close();
Please give me some directions to debug. It seems that the values of Columns withn DataRow cannot be fetched into the corresponding adapter.InsertCommand.Parameters by the adpater. (As I use SqlCommandBuilder to automatically generate insert command, the code above wroks rightly.) Thanks a million. Ricky.
I know there are loads of posts on this, but this just will not update. I have tried various forms of the code all to no avail. And now I'm ready to throw my PC out the window........ What am I doing wrong? cheers Mike Dim da As SqlDataAdapterDim dSetOrp As DataSet oCn = New SqlConnection(db.m_ConnectionString) oCn.Open()da = New SqlDataAdapter("Select * from contact WHERE conid = " & lngConId, oCn) dSetOrp = New DataSet da.Fill(dSetOrp, "locTable") ' Fill the DataSet. dSetOrp.Tables(0).Rows(0)("ConSttDate") = dtEffDateDim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da) myBuilder.GetUpdateCommand() da.UpdateCommand = myBuilder.GetUpdateCommand() lRows = da.Update(dSetOrp, "locTable")
DataSet updateSet = finalSet.GetChanges(DataRowState.Added); saveCenterCoaches.Update(updateSet.Tables[0]); } catch(Exception ex) { throw ex; } Iam getting "Procedure expects parameter @ConsultantName, which was not supplied." I have consultantname and other parameters built in my datatable. Is it the correct way of doing? Can someone help.It is urgent.
Hi, I was looking at how to update a database using SqlDataAdapter and I stumbled upon this code snippet from (http://www.java2s.com/Code/CSharp/Database-ADO.net/UpdatedatabaseusingtheSqlDataAdapter.htm):using System; using System.Data; using System.Data.SqlClient;
class Class1{ static void Main(string[] args){ SqlConnection thisConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Employee");
Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
} }I was just wondering, without iteration, how did the line "thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";" managed to know which row it is to update? I tried it on my own application and it worked fine.Any help to assist me in understanding this would be appreciated. Thanks
Hello Dears; I have an SqlDataAdapter which contains may queries. One query contians 3 query parameters as follows: SELECT SUM(Amount) AS TotalFROM BoxesWHERE EntryDate BETWEEN @date1 AND @date2 AND Area=@Area the query cannot be ran unless the queries parameters were provided. I have to access this query in code to add the parameters from controls. Is it possible to access it or use sqlDataSource instead? Thanks alot
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong? Here is my code:try { my_conn = conn_open(); da = new SqlDataAdapter(query, my_conn); da.Fill(result.resultDataset); my_conn.Dispose(); } catch (Exception e) { result.errMsg = "Database Error: " + e.Message; result.success = false; } Kehil
How to configure sqldatadapter with query like "select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)" Is the nested subquery is not allowed while configuring sqldaadapter? Swati
Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma
How do i get the result 'puzzle' from the SQL and assign it to Integer and use it to compare with Request.Form ? Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load
Dim connString As String = _ ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
Dim strsql As String = _ "Select CustomerID,CustomerNo,dbo.LA_DEC(Passkey) as passcode,dbo.LA_DEC(PuzzleKey) As puzzle, PuzzleFlag, NickName from Customers Where CustomerNo = '" & myAccount & "'" Using myConnection As New SqlConnection(connString) Dim myCommand As New SqlCommand(strsql, myConnection)
myConnection.Open() Dim PuzzleDataSet As New DataSet Dim PuzzleAdapter As New SqlDataAdapter(myCommand) // <---- Coding here
I have a block of code that does a Fill() with a DataAdapter, which seems to throw an exception if no values are returned. Then, when I try to bind the DataSet that gets filled, an error occurs, it doesn't even try to bind the column headers or anything.
This process seems to work fine when some rows are found, but it is not always guaranteed that the row count will always be > 0. The nice thing about using the Fill() function, is that it creates all my column headers automatically, so I was just wondering if there's a way to do a Fill() then a subsequent DataBind() to a DataGrid which would automatically format the columns even when no rows are found?
Here's a bit of the source.. if the last line finds no rows, it throws an exception ( I assume this is normal):
strSQL = "SELECT * FROM Prods"; cm.CommandText = strSQL; da = new SqlDataAdapter( cm ); da.Fill(shelf);
