Close discussion

We have a sharepoint <\\ces\cesgypdav> that is a WebDAV connection, not an actual fileshare. A SSRS Subscription fails on file write ... "Failure writing file <name here > : The network path was not found" ... to this path.

Can SQL Reporting write to a WebDAV sharepoint?

WebDAV issue is known, need to 'work around'.

This helps: http://sqljunkies.com/WebLog/tpagel/archive/2005/12/23/17682.aspx

Close cursor in another procedure (part II)

<I'm sorry for new post but I couldn't reply to group because of errors>
Hi David,
I have a lot of reasons to using cursor and I don't know better way to do
this.
For example;
I control datas in the cursor like this:
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
SELECT @.BLABLA = BLABLA FROM BLABLA
IF @.MyVariable < @.BLABLA THEN
BEGIN
RAISEERROR(...)
..
END
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
And this is insert script: "INSERT INTO TableA(...) SELECT ... FROM xxx"
Better idea?CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
AS
IF EXISTS
(SELECT *
FROM blabla AS B
JOIN Inserted AS I
ON I.myfield < B.blabla
WHERE ... /* ' unspecified */)
BEGIN
EXEC my_error_sp
RAISERROR ...
END
GO
David Portas
SQL Server MVP
--|||Thanks David,
Let me ask last question.
I have a log system like this:
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
DECLARE @.RESULT BIGINT
DECLARE @.TABLE_NAME VARCHAR(128)
DECLARE @.OBJ_ID INT
SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
//if log inserted successful then do other operations else rollback and
exit
IF @.RESULT = -1
BEGIN
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor
DEALLOCATE MyCursor
RETURN
END
DoSomething
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
END
--
DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
...
INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
IF @.@.ERROR <> 0
RETURN -1
...
--
How can I do without any cursor?|||See if this helps:
How do I audit changes to SQL Server data?
http://www.aspfaq.com/show.asp?id=2448
AMB
"Tod" wrote:

> Thanks David,
> Let me ask last question.
> I have a log system like this:
> --
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> DECLARE @.RESULT BIGINT
> DECLARE @.TABLE_NAME VARCHAR(128)
> DECLARE @.OBJ_ID INT
> SELECT @.OBJ_ID = parent_obj FROM sysobjects WHERE id = @.@.PROCID
> SELECT @.TABLE_NAME = OBJECT_NAME(@.OBJ_ID)
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.TABLE_NAME, @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC @.RESULT = SpLog @.TABLE_NAME, @.MyVariable
> //if log inserted successful then do other operations else rollback and
> exit
> IF @.RESULT = -1
> BEGIN
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor
> DEALLOCATE MyCursor
> RETURN
> END
> DoSomething
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> END
> --
> DECLARE PROCEDURE SpLog (@.TABLE_NAME varchar(128), ID int)
> ...
> INSERT INTO LOG_TABLE (TABLE_NAME, ID, OPERATION_DATE) values (...)
> IF @.@.ERROR <> 0
> RETURN -1
> ...
> --
> How can I do without any cursor?
>
>|||The problem here is that you apparently want to call an SP for each row
in the INSERT. In itself, that is a weak concept for writing modular
data-manipulation code. You should aim to write set-based stored procs
that operate on SETS of data rather than one row at a time (except for
code supporting the UI where you often want to support single row
updates). Everything you want still can be possible without a cursor
but you may need to adjust the logic of your SpLog code. Since you
haven't told us what it does, I can't show you how to do it.
Also, note that if you need common code in triggers you can generate
the code semi-automatically from the metadata which reduces the need
for code re-use through procs. In general you want to minimize calling
other procs from a trigger.
David Portas
SQL Server MVP
--

Close cursor in another procedure

Hi,
I declared a cursor in an Insert trigger. In this trigger I called a sp. Can
I close this cursor in the sp?
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
CREATE MY_ERROR_SP AS
...
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
...
----Why have you put a cursor in a trigger? Cursors are rarely a good idea
and triggers are absolutely the last place you should use them. Updates
are set-based so triggers should be too.
The answer to your question is no, because your cursor is local. The
better answer is rewrite your trigger.
David Portas
SQL Server MVP
--|||Cursors by default hv global scope. Therefore, it is possible to create them
in 1 SP1 & close it in SP2 called from SP1.
Rakesh
"Tod" wrote:

> Hi,
> I declared a cursor in an Insert trigger. In this trigger I called a sp. C
an
> I close this cursor in the sp?
> ----
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> ...
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> ...
> END
> ...
> ----
> CREATE MY_ERROR_SP AS
> ...
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
> DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
> ...
> ----
>
>|||David is right.. since ur cursor is a local one, u will not be able to close
.
"Rakesh" wrote:
> Cursors by default hv global scope. Therefore, it is possible to create th
em
> in 1 SP1 & close it in SP2 called from SP1.
> Rakesh
> "Tod" wrote:
>

Close connection of SQLExpress!

Hi,

I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.

Is there any way to detach/unlock .mdf file connecting by SQLExpress?

MA.

Hi,

if you are sure you closed down all conenctions, you can use the sp_detach command to detach the database. if you want to close all connection from the server side first you will have to use the ALTER DATABASE command first and change the state of the database to a SINGLe or admin mode.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Unfortunaterly, I dont control the connection but I want to force to close all connection from the client side (using C# code).

MA.

|||

Hi MA,

Jens is exactly correct, you'll need to force the database into single user mode:

ALTER DATABASE pubs
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

This will rollback all transactions and then you can detach the database as you like. I'm not sure what you mean you don't control the connections. If you can not connect to the database, then you can not close it's connections and you will not be able to close the connections.

Mike

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.

close

Below, I have pasted a portion of an MS MSDN article dealing with a specific issue we are having with Report Server performance. The URL (http://support.microsoft.com/kb/821438/en-us) is the MS KB article on the described fix. The fix mentioned is for "ASP.NET 1.1" but our BP Report Server is using "ASP.NET 2.0".

Has anyone encountered and resolved and how?

Running on box:
ASP.NET 2.0.50727;
SSRS 2005 Sp1 + Hotfix#2175;
Win Server 2003, R2, x64, SP1
Trend Micro OfficeScan Cleint for Win 2003/xp v7.3
IIS Version (I cannot find this #) - ? IE 6.0

===========================
Report Manager or the report server runs very slowly

In some circumstances, ASP.NET applications run very slowly on computers that are running anti-virus software. If the Report Server Web service is restarting frequently, and you are running anti-virus software, you can obtain an ASP.NET fix from Microsoft Customer Support Services.

Symptoms include Web applications or Application Domains restarting for no apparent reason, slow performance, session restarts, and more. For more information about the symptoms, cause, and resolution, see Microsoft Knowledge Base article 821438.

You can find out whether there are excessive server restarts by viewing the number of reportserver_<timestamp>.log files. A new log is created each time the server starts. A large collection of logs created at very short intervals is an indication that the conditions described in article 821438 exist on your server
===========================

It appears the issue goes away with SQL2K5 SP-2 load. Thanks to whoever.....