Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).
If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.
On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).
Environment:
Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )
We often run a certain large stored procedure which is basically a select from a number of tables (these tables can be quite large 1million+ rows).
Usually the stored procedure runs in under 3 seconds, however, on occasion through query analyser we can manage to get the procedure to pretty much time out - there are no other processes running on the database, so why does the process sit there as 'sleeping' rather than finish?
Hi,Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server. The book uses C# I have attempted to convert the code to call the stored procedure as follows: TryDim units As Int16 = 0 Dim cmd As SqlCommand = New SqlCommand("FindNearby") 'Assign input values to the sql command cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude) cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance) cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString) If con.State = ConnectionState.Closed Then con.Open() If con.State = ConnectionState.Open Then cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader() If dreader.Read() Then If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString End If End If End IfIf con.State = ConnectionState.Open Then con.Close() Catch ex As Exception Finally End Try
The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code. Output Window Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ). ID ordnum ProxDistance ----------- ------------------------- ------------------------- 1 009999/USP 0 2 109999/USP 14.5971373147639 3 119999/USP 57.7144756947325 No rows affected. (3 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[FindNearby]. Regards, JoeBo
Hi I have decided to approach the problem here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103310
by using a Stored procedure, someone mentioned it's possible for a SP to read the contents of a text file ?
IS it ?
For example i have a DB table with fields to,from,subject,body
problem is the body field references a file (full path) and i need to extract all info into a txt file the full txt body, so can i perform some sort of inner operation on the select body .. part of the SP ?
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
CREATE PROCEDURE TestDataRead @TestString varchar(20) OUTPUT AS
SET @TestString = 'Mr.String' GO ----------------------------------------------------------------------- SSIS package details.
OLEDB connection manager; Connection works fine. Execute SQL Task Editor properties are as follows. ResultSetà None SQLStatementà exec TestDataRead SQLSourceTypeirectInput Parameter Mapping: VariableName: selected the user variable from the list. Direction: Output DataType:varchar Parameter Name: 0 Parameter Type: 20 When I run I am getting the following error.
SSIS package "TEST SSIS1.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ........................................... ...................................................................
Hi I wounder if there is a sp in the master db that can give me what usergroup a username is connected to / and or give me info if the username is valid (Windows NT login). If not: then how do I read what usergroup a username is connected to? sysxlogin table in master db? And can I connect to a sp in master db from another db? Can I create a sp in my db that reads in the master db? Thanks!! Best Regards Staffan
In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As BEGIN CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20)) INSERT INTO tblStatusSummary SELECT TckStaffCd_F, RecType END
I am coding a DataList control for 2 column data from a stored procedure. The 1st column isprogramName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..? TIA,Jeffrey <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner] ! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd> ! -- <N> must be a successive number starting from 1 ... TableToBeCleaned_1=Transactions,24,24 TableToBeCleaned_2=Payments,24,24 TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
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.
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
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 am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.
This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.
I have a stored procedure that is located in one database and I would like to have it execute against a different database. My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?
This is what I have now, which isn't working:
Code Snippet
use [Database2] exec [Database1].[dbo].usp_SetupDatabaseUser
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, I have a set of databases. Each user had his own database. Every database had the same stored procedures. The is one "admin" database where content about these users is stored. When I want to update or change a stored procedure I have to update it in every database. Therefore I want to put the stored procedures in the "admin" database. From there I have to access the databases of the user. I want to send the database name as a parameter. How can I change de database which te procedure should access? If I use the "use" statement I complains about that I can't use that command in a stored procedure. I've tried this method too: SELECT * FROM @databasename.dbo.tblname but that's not accepted too. Does anybody know how to select another database in the stored procedure?
We are trying to develop some standards for SQL stored procedures and I am wandering what is the preferrable method to reference a table within a stored procedure for use within a Client Server / Web OLTP. One: SELECT Customer_Id, Customer_Name FROM CUSTOMER
Or TWO: SELECT Customer_Id, Customer_Name FROM [DATABASENAME].[dbo].[CUSTOMER]
Most of my background is in working with data warehousing which is constantly loading between a staging and main DB, so I am in the habit of using TWO, but is this the best method for an OLTP and why.
Hi all, I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement. Thank you for all the help you can give me. Regards, Eve
I have created a stored procedure in the master database that can be run against any database on the server. I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?
hey guyz... i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct? if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase @Path VARCHAR(100) AS Restore Database Test From Disk = @Path GO
Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!
