I have an auto exec stored procedure that needs to complete successfully or:
- the server should shutdown, or
- disable remote connections
Officially I cannot issue a Shutdown from a Stored Procedure. In addition, I can't see how to programatically disable remote connections.
I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
View 3 Replies View Related
Hi,
Anybody now how to this programmaticly ? in registry or TSQL statement ?
Here's what I'm trying to accomplish:
View 5 Replies View Relatedhi,
What is the right procedure for shutdown a production web sqlserver for maintenace purpose. Checking users by sp_who?
Any other monitoring for current connections? lockings?
thanks
ram
How to optimize stored procedure without modify?Is there is any way to forcing index without using FORCESEEK hint?
View 3 Replies View RelatedI'm profiling a specific procedure on a database, I have the start and end times captured but within the same trace I would like to capture all the individual sql statements.
So, given this simple procedure
ALTER PROCEDURE [dbo].[usp_b]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@VERSION;
SELECT GETDATE();
END
In the trace I would like to see the two SELECTs, however all I get is this
I have the following events selected in the trace.
Hi
I've implemented an SQL-CLR stored procedure which makes an HTTP connection using the WebClient class to a server where the processing on the web server takes 10 seconds.
If I fire off a bunch of requests to the stored procedure, e.g. 80 in 1 second I noticed that SQL Server 2005 (Express Edition) only allows 2 concurrent network connections at a time.
I can confirm this by looking at the timing of the results when they come back, basically I get 2 results every 10 seconds and by doing a 'netstat -a' on the web server I notice that there are never more than 2 HTTP connections from the SQL Server at a time.
Is this some sort of sheduling policy in terms of the number of concurrent external network accesses allowed by CLR objects running in SQL Server? Or some side effect of the way the WebClient class blocks in terms of waiting for a network response?
I was expecting all 80 of the requests to block waiting on a network response almost immediately and then for all 80 of them to pretty much complete and return 10-11 seconds later.
[Microsoft.SqlServer.Server.SqlProcedure]
public static int CreditAuthorisation(string Name, decimal Amount)
{
WebClient client = new WebClient();
string result = client.DownloadString("http://someserver/Test.aspx");
return int.Parse(result);
}
Cheers
We try to run a stored procedure in management studio - and we see the following error -
"
Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[SQLSTATE HY000] (Error 0) The log for database 'Some-database' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). The step failed.
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
How can i Trace Database Audit specification Enable and Disable. i want to maintain log for enable or disable database audit specification.
View 4 Replies View RelatedI have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.
Setting the database to FULL and doing frequent log backups does not work.
The log does not get truncated during this log backups.
The stored procedure in question has SET XACT_ABORT ON statement at the beginning.
At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.
-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw
The customer has SQL Server 2008 R2 Express installed
The connection string to the database works on everything else and it is the sa account with password
On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.
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.
Hi there,
I just upgraded from SQL2000 to 2005 and noticed that any DB engines you register do not get re-registerd when you restart the Management Studio, you have to reconnect every time you start Management Studio! SQL2000 used to keep your server connections.
Am i missing something or is this just how it works now!?
Thanks
Ben
The option:
SQL Server Configuration Manager>SQL Server Network Configuration>Protocol for SQL_xxx (right click)>Properties, we can see two Tabs:
Tab 1: Flags
Tab 2: Certificate
If I set the value of Hide Instance= Yes, does "Force Enctyption", need to be set YES as well?
Or Force Enctyption has to be enabled in order to hide instance?
What is the recommend settings?
[URL]
In SQL Server Management Studio, it is possible to do the following:
a) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it X.
b) Use that connection to generate many SQL queries, connected to X, in the right-hand pane.
c) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it Y.
d) Use that connection to generate many SQL queries, connected to Y, in the right-hand pane.
e) Close the connection to X, which was created in step (a), from within Object Explorer.
In the right-hand pane, I am now left with a bunch of queries connected to X, and a similar bunch of queries connected to Y. Any quick way to shut all the queries connected to X, but none of the queries connected to Y?
This question can assume real practical importance if server X is a Live Production Server, and server Y is a Test Server, and my next job of the day is to run a change script against server Y....
I have looked for an option in Management Studio to "Close all queries connected to Server X", but haven't found one.
disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedHi 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 all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
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],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Hi,
Is there any shutdown command in sql that shutdowns the sql server with a single command or statement, as of now I am using the windows shutdown command.
thanks in advance.
bye,
Madhu.
hi friends,
i am new to sqlserver. can anyone tell me how to shutdown the database.
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hello all,
Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!
Does anybody know?
Thanks in advance,
Jerome Smith
I have my suspicions, but I need some proof ...
Is restarting a Windows OS box without first stopping the MSSQL service comparable to a shutdown with nowait command? I know that MSSQL will recover, but wouldn't it recover more nicely if it was shutdown on its own terms prior to the box shutting down? OR. Since it's a Windows service does the OS *know* to stop the service gently, allow the engineto place checkpoints on every db and then shut down the OS?
I've googled plenty and cannot find an exact answer. Lots say it's fine to do - doesn't mean it's right ;)
Thanks!
hi,
for the last six months or so my pc has been shutting down all applications for no apparent reason when a 'low virtual memory' bubble appears. I have removed dozens of items, such as games, image editos; all programmes that require a lot of memory but it is no good. Every 40mins or so the pc decides to shut everything down and where it is impossible to start any further applications, unless I log off and on or shut down the pc myself. I really am fed up with this, its so annoying. Is it because of a virus or do I still have too much on my pc?
insaneolly
I have an SSIS package that looks for the existence of a file (using Konesans file watcher) on the network to begin processing. I am trying to find a way to terminate/shutdown the entire process if that file is not out there by say 7:30 AM. I think I could set a timeout on the filewatcher task, but was hoping there was another tool I could use. (If the process had to be restarted late a timeout would still countdown the same period wouldn't it? We'd still need the job to stop by 7:30.)
More background. The package, once deployed, will be called from a command line script that is started by a scheduled task normally, or manually in the event of late availability.
Thanks,
Rog
I can't seem to get this to work and have to resort to a custom connection for each report in Server Manager. The problem with that is that I have to re-do it every time I re-deploy the reports. Is there a secret to this that I have not found yet?
Dick Campbell