DB Engine :: How To Save Dataset In Server 2014 Stored Procedure
Aug 17, 2015
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
Scenario: I have created a dataset from an excel file to display it on a screen. Now I would like to save this same dataset in a SQL Server table. Any ideas would be greatly appreciated.
The other day I was asked to build a SQL Server process to terminate blocking sessions that could be safely destroyed in order not to drain necessary instance resources. The solution is made of below stored procedure and by a SQL Server Agent jobs that runs every 3 minutes just to invoke the sproc. The T-SQL code should be easy to read and has plenty of remarks.
USE [<<yourDBAgoodStuffDatabase...>>] GO SET ANSI_NULLS ON GO
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
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?
I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.
I've managed to get my Instance connected to the internet and I can query it using PHP and SQL, I can also look at views with no problem.
I have it working as an "ADODB.Connection" and like I said it connects and I can query data and display results.
Now I have coded a Stored Proc "GetMonthDays" in Sql Server:
Which returns days 1 through xxx in a given month and also returns the Day name eg... Sat for each date
2014-01-01 Thurs 2014-01-02 Fri etc...
It works perfectly and very fast so All cool with that side BUT... I want to be able to query the Database through a Stored Proc, I've spent all day trying to find a way to get this to work and I've hit a wall
In Microsoft SQL Server Management Studio Express you can right click on "Stored Procedures" under a database in the object explorer and the resulting Context Menu offers a selection called "New Stored Procedure".
If you select "New Stored Procedure", a Stored Procedure Template Document is added for editing. It has the suffix ".sql"; I can save this document to the file system after I edit it, but I cannot figure out how to add it to the database as a Stored Procedure.
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN SET NOCOUNT ON; WITH Darin_Import_With_Key AS ( SELECT [pra_id] ,[pra_ClientPracID]
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20'
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
Hi! I'm using VS2005 and trying to save my very simple stored procedure.The error message "Invalid object name 'dbo.xxxxx' just pop's up. The SP is written through the Server Explorer > Stored Procedures > Add new.... etc.Using SQL server 2000. Don't think this has to do with the SP-code but here it is: ALTER PROCEDURE dbo.KontrollUnikPersNr@PersNr nvarchar(50) = null OUTPUTASSELECT @PersNr = PersNr FROM User WHERE PersNr = @PersNrRETURN @@ROWCOUNT Thanx i advance for any help!
I am running Sql Server 2005, When I create a new stored procedure and try to save it, I'm prompted with the 'save file as dialog'. Is'nt the stored procedure suppose to be saved within the database?
Hi, I have created a store procedure and want to save them. this is wha ti did.
I clicked on the Northwind--> Programm--> Stored Procedure-- > right clicked --> new stored procedure after writing my stored procedure I saved it. But when I am saving I want to save it under the Stored Procedure folder of the NorthWind database. I guess this is what it should do. When I use to use the sql 2000, i could always send my stored procedure here , i guess it was by default. But even if i save my sql stored procedure at any other location than at the run time, how the applicaiton will find my store procedure.
Well so thinking of that I have to save my stored procedure under the database for which table it is created however, i can not save it there. I tried to figure out the directly but I couldnt.
Hi, i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio. But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp. Thanks tartuffe
Hi, Recently, I started to move more databases from SQL Server 2000 to 2005, well the Stored Procedures in 2005 is hard to handle.
First of all, I created the new SP in Object Explorer, the "New Stored Procedure" query just created a new name under the Stored Procedures folder, then I have to re-open it to put in TSQL code.
After I put in the code in the new SP, if I click "Save", the File Manager comes out to let me to save in a physical location (C: or D: or Network place...). But after I close and open the new SP, the code I put in there is not really in the new SP.
I know that I have to "Execute" the SP in order to save the change. But the problem is I don't want to run the code at this step, the SP is to get a lot of data transactions, I'll setup a "Job" to do this.
So, the question is: Is this the way to create and save a new SP? Is there any other way to save the code changes in the SP WITHOUT "Execute" it?
Please give me any advise, article or links to read.
I am trying to save an uploaded image and its associated info to sql server database using a stored procedure but keep getting trouble. When trying to save, the RowAffected always return -1. but when i debug it, I dont' see problem both from stored procedure server explore and codebehind. it looks to me every input param contains correct value(such as the uploaded image file name, contentType and etc). well, for the imgbin its input param value returns something like "byte[] imgbin={Length=516}". Below is my code, could anyone help to point out what did I do wrong? Thank you.
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error: Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) my code looks as follows for the dataset:<script runat="server">Sub ListSongs() ' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer 'retrieve albumn ID for track listings intID4 = Int32.Parse (Request.QueryString("id")) ' Create Instance of Connection myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open() 'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4) ' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) 'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs") 'Bind the datatable object called dtsongs to our Datagrid: dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html> Any help or advice would be greatly appreciated. Thank You - Jason
I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
Hi all, Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere. Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this. This is what I have so far:- 1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 } As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data. Any help is greatly appreciated. Slainte, Sean
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem. Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to. Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e) {GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C"); }
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath