BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

Bogus ''out of disk space'' error message

When running SQLEVAL.EXE, I'm getting an error message when the installation wizard finished 'reading contents of the package' reading:

There is not enough space on C:\ to extract this package.

Having checked the requirements http://www.microsoft.com/sql/editions/developer/sysreqs.mspx, the free disk space on my c:\ is 45.42Gb.

Any ideas?

See: http://blogs.msdn.com/nikop/archive/2005/03/13/394797.aspx

Bogus 'out of disk space' error message

When running SQLEVAL.EXE, I'm getting an error message when the installation wizard finished 'reading contents of the package' reading:

There is not enough space on C:\ to extract this package.

Having checked the requirements http://www.microsoft.com/sql/editions/developer/sysreqs.mspx, the free disk space on my c:\ is 45.42Gb.

Any ideas?

See: http://blogs.msdn.com/nikop/archive/2005/03/13/394797.aspx

Blue Screen

Hello, I'm trying to restore a large database (350gb) to a sql server over the network. However after running for about 10 hours or so the target sql server crushes with a blue screen. Has anybody encountered similar problem or do you have an idea as to why this may happen. Thanks a lot for any help.Hello Wolv,

What sort of network are you running? If you are working on a gigabyte LAN/SAN, then you should have no problem depending on whether your SQL server hardware is up to scratch.

You are trying to move a big DB, therefore if you don't have the hardware to support that move then you may want to consider an alternative for moving the data.

Hope this helps, at least in understanding why...|||well, what kind of network would be helpful, but If you are restoring it, I would think that you've had it in the same place before, just something happened, and that's why you are restoring it...correct?

Blocking SQL server by machine name?

Hello,
I've Windows 2000 server with SQL 2000 server running.
I have a SQL user (let's call miniSa) which is mostly "sa" on one SQL box.
And that account is used to all over the places (VB apps,Web app, DTS
connection). Now I know that a person shouldn't be access to the SQL box bu
t
he does using the account. Only way I can track down him is from SQL profil
e
with his machine name.
Is there a way that I can block the SQL box only from a specific machine nam
e?
Thank you in advance.
SangHun"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:F23A84F9-6350-4BE8-B002-62A9BD320BCB@.microsoft.com...
> I've Windows 2000 server with SQL 2000 server running.
> I have a SQL user (let's call miniSa) which is mostly "sa" on one SQL box.
> And that account is used to all over the places (VB apps,Web app, DTS
> connection). Now I know that a person shouldn't be access to the SQL box
but
> he does using the account. Only way I can track down him is from SQL
profile
> with his machine name.
> Is there a way that I can block the SQL box only from a specific machine
name?
It's kind of ugly, but you could do TCP/IP filtering on the server level and
block the IP address of the computer that your "SQL user" uses. A better way
would be a review of the security implementation, eliminate this commonly
used account and implement Windows Authentication with nt group membership.
Steve|||Thanks for the reply Steve.
Using TCP/IP Filtering is not an option because of DHCP server. I may use
MAC address but that way that user may not use all the apps in the server.
I
don't want that happen either. I want just SQL server databases access
denied from the PC.
I will work on the whole problem but I need some time and ofcourse runing
several projects, support developers, and admin issues......tough.
Any other suggestions?
SangHun
"Steve Thompson" wrote:

> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:F23A84F9-6350-4BE8-B002-62A9BD320BCB@.microsoft.com...
> but
> profile
> name?
> It's kind of ugly, but you could do TCP/IP filtering on the server level a
nd
> block the IP address of the computer that your "SQL user" uses. A better w
ay
> would be a review of the security implementation, eliminate this commonly
> used account and implement Windows Authentication with nt group membership
.
> Steve
>
>|||
> I will work on the whole problem but I need some time and ofcourse runing
> several projects, support developers, and admin issues......tough.
> Any other suggestions?
Yes, I still recommend my previous suggestion, some times there are no
+easy+ solutions. Sorry.
Steve
[vbcol=seagreen]
commonly[vbcol=seagreen]
membership.|||You can use IPSec to block a particular machine from contacting another
machine on the network.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Blocking issue in DTS package running on 2005

I'm testing an existing 2000 DTS package in 2005. A Data Transformation Task selects from a view and loads a table that was just truncated. The view definition contains a subselect selecting data off the table,

View: select ... from a, (select ... from b) b2 ON ...

Table to insert into: b

There is blocking going on during the execution of the package. The select * from view (Source) and the insert bulk into the table (Destination) are blocking each other. This does not happen in 2000.

Is there a command that I can put in the view definition subselect that will allow me to just grab the data that it can without worrying about blocking? Would READCOMMITTED or READUNCOMMITED work without impact on the view? Other recommendations?

Has anyone ever run into this?

I found that when you uncheck the Table Lock box of the Data Transformation Task, that this blocking does not occur. Does anyone know why this is required in 2005 but works fine in 2000 with the box checked?

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden -> integer
'Aantal records is resultaat(0).aantalrecords -> integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzerman
Martin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>
|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true.
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman
|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>
|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detec
ted.
Martin IJzerman.

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;
The vb module i use
Imports Syste
Imports System.Dat
Imports System.Data.SqlClien
Module SQ
Public SQL_CONNECTION_STRING As Strin
'Structuur van de resultaat arra
'Aantal velden is resultaat(0).aantalvelden -> intege
'Aantal records is resultaat(0).aantalrecords -> intege
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> strin
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> objec
Structure Resultaat
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordse
Dim VeldNamen() As String ' namen van de velde
Dim VeldInhoud() As Object ' inhoud van de velde
End Structur
' Resultaat array is de inhoud van de query uitvoerin
Public Resultaat() As Resultaat
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolea
If upd_Q = True The
GeenResultaat(strSQL
End I
If upd_Q = False The
WelResultaat(strSQL
End I
End Functio
Private Sub GeenResultaat(ByVal queryStr As String
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING
myConnection1.Open(
Dim myCommand As SqlCommand = myConnection1.CreateCommand(
Dim myTrans As SqlTransactio
' Start a local transactio
myTrans = myConnection1.BeginTransaction(
' Must assign both transaction object and connectio
' to Command object for a pending local transactio
myCommand.Connection = myConnection
myCommand.Transaction = myTran
myCommand.CommandTimeout = Tr
myCommand.CommandText = querySt
myCommand.ExecuteNonQuery(
myTrans.Commit(
Catch e As Exceptio
Tr
myTrans.Rollback(
Catch ex As SqlExceptio
If Not myTrans.Connection Is Nothing The
Debug.WriteLine("An exception of type " & ex.GetType().ToString() &
" was encountered while attempting to roll back the transaction."
End I
End Tr
Debug.WriteLine("An exception of type " & e.GetType().ToString() &
"was encountered while inserting the data. " & Now()
Debug.WriteLine("Neither record was written to database."
Finall
y = y +
myCommand.Dispose(
myConnection1.Close(
myConnection1 = Nothin
End Tr
End Su
Private Sub WelResultaat(ByVal queryStr As String
Dim lRow As Integer = 0 ' recordnumme
Dim lFIELD As Integer = 0 ' kolomnumme
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velde
Dim myDataR As SqlDataReade
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING
myConnection2.Open(
Dim myCommand As SqlCommand = myConnection2.CreateCommand(
myCommand.Connection = myConnection
Tr
myCommand.CommandText = querySt
myCommand.CommandTimeout = myDataR = myCommand.ExecuteReader(
'haal het aantal velden op van de recordse
LFieldsAffected = myDataR.FieldCoun
'redimensioneer de dynamische arra
ReDim Resultaat(1
ReDim Resultaat(0).VeldNamen(LFieldsAffected
ReDim Resultaat(0).VeldInhoud(LFieldsAffected
'zet het aantal velden in resultaat(0).aantalvelde
Resultaat(0).AantalVelden = LFieldsAffecte
'Vul de veldnamen i
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Ur
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000, and there is no possibility to change to a other db-server
I shall try to cancel the query in state of rolling them back
Thanks, Martin IJzerma|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detected
Martin IJzerman

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hou
rs running a application developt with vb.net 2003, after a time this blocki
ng is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a par
t of a factory and is also used for controlling machines. If there is a bloc
king then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSB
A02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden ->
integer
'Aantal records is resultaat(0).aantalrecords ->
integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) ->
string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) ->
object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boo
lean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar,
begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--
--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bi
ts & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150
select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidab
le.', i hope it is not true.
In my application blocking is not acceptable. before i have written a applic
ation with mysql as database-server and after 2.5 years 24 hours 7 days a we
ek, there are never blocking problems. The policy by the factory i work for
is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with mysql as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and
in the logging file there is no blocking detected, and also in the profiler
trace there are no special things to see. There is after 10 to 14 hours some
blocking with is not detec
ted.
Martin IJzerman.

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin Janecke
The knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke
|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:

> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
>
>

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin JaneckeThe knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:
> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> > Hi,
> >
> > we have a sql server 2000 database running that manages amout 10 million
> > invoices (growing at 10000/day). in addition, we have a file server that
> > holds all invoices as pdf files. the problem is that it is extremely
> > difficult to backup these files or to do other file-realted task since
> > there
> > are so many of them.
> >
> > thats why we started thinking abut moving all these files directly into
> > the
> > database as binary objects (BLOBs) so that invoice meta data and content
> > is
> > at the same place. the average file size is about 40k. im not a big fan of
> > blobs but in this case it seems to be a good thing since backup and file
> > management wouldnt be a problem anymore (among other advantages).
> >
> > my question is if you already experienced having large amounts of blobs in
> > the database and if you think its a good idea or not. i don't want our
> > overall query performance to be affected in any way (i know that sql
> > server
> > stores pointer to the blob data in the tables so it shouldnt have a great
> > impact on performance, should it?). i'm interested in your experiences and
> > recommendations.
> >
> > btw.: we would upgrade the hardware to something like this:
> >
> > 2 x Xeon 64, 3.6 GHz
> > 8 GB RAM
> > 10-14 disk drives for a total of 1-2 TB
> >
> > i think it should be enough.
> >
> > thanks in advance,
> > Benjamin Janecke
>
>

BLOBs and Stored Procedures

I have been told that using BLOBS and Stored Procedures is a bad thing.
Running the SQL in the page is the only correct way. We are using SQL Serve
r
2000 - soon to go to 2005. Could someone direct me to documentation that
addresses this situation?
--
Tonywho told you this?
BLOBS I can see why people would advise you to avoid them, they aren't
bad things, they just have the capability to be used badly.
Stored procedures are very good things, by "running the SQL in the page
is the only correct way" do you mean passing SQL as a string to your
sqlCommand objects? is this just for when you are using BLOBS or all
SQL you run?|||some of the key benefits of stored procedures are mentioned in this
article:
http://msdn.microsoft.com/library/d...>
_07_31vb.asp|||Will,
Thanks for getting back to me. I have used Stored Procedures for years.
No, my questions is specific to BLOBS and stored procedures. (I have heard
all the negatives about BLOBS - pdfs in databases, but I have a client ...)
My DBAs tell me that 'adding' a layer to the data process - for BLOBs only-
is too high a price to pay (resources) for me to use them.
I can't find definitive proof one way or the other. I would really like to
continue to use my data layer and not use in-page SQL (ADO .Net to SQL Serve
r
with no SP). I can't argue to vehemently because I don't know if a query
plan is even generated for the BLOB handling SP.
With over twelve years of experience with SQL Server, I have never seen a
situation where performance was better without a stored procedure. But SQL
has to handle BLOBs differently, so I was hoping to find something to suppor
t
either side of the argument that I could take to my DBAs.
Thanks for your time.
--
Tony
"Will" wrote:

> some of the key benefits of stored procedures are mentioned in this
> article:
> http://msdn.microsoft.com/library/d...
es_07_31vb.asp
>

Blank Parameters do not work when running stored proc in Data view

I have a stored procedure that gives different outputs based on whether an
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.

Blank Page On Crystal Report 10

Hi Im' using Crystal Report 10 and the modules created works well when running on Windows XP, but when I try to run the modules under Windows 2003 or Windows 2000 then only a blank page is being displayed (without any error being returned).

Any idea? Please help!

Btw, Im' using Crstal Report Advanced Developer on all machines.Hello,

Can you please tell me if you got any answer for your problem as i'm getting the same issue too... Thanks in advance

bracoute
email addr: bracoute@.hotmail.com|||Yes me to but im using cystal report 8. In my development PC there are no problems but when i installed it at another PC to test it happens. the report have no error message but it jus blank??

please help anybody

Blank page in report manager

When i load report manager everything appears to be running fine. The header, footer and nav bar at the top load correctly. Even the directory and file tree load fine.

The problem is when you click on a report everything from below the nav bar ( where the report would be rendered) blanks out and load bar at the bottom just tacks at full.

One way to fix this problem is to open the web config file for report manager save it then close the file.

This is not a viable solution to the problem. I was wondering if any one else had the same problem . If so how to fix it ?

Has anyone had a similar problem with reporting services. Also is there a place to formally site this as a bug

|||

We are having a very similar problem that is driving me nuts. My end users will get this situation - Report Manager banner and links at top, and blank below the color line. They all have the appropriate permissions. At first, we thought it was caused by an Internet Explorer patch, an uninstalling that patch seemed to work... for a little while. Now, they are complaining again. I get the same patches, and I still have full access (of course, I do have sys admin and sql admin rights). I have a suspicion that it is not an IE patch, but an IE security setting or security policy that may be causing it. When I looked at the Report Manager homepage in FrontPage (just to see what I could see), I noticed that banner is rendered in HTML, and it sets the language for the rest of the page to Javascript. If the Internet Explorer security doesn't allow Javascript, or even prompts for permissions, that may be preventing the display of the rest of the page, and would explain why the banner displays and doesn't just throw an error message to the end user. I am investigating this more. Let me know if you have any thoughts.

Tee Dubs

Blank page in report manager

When i load report manager everything appears to be running fine. The header, footer and nav bar at the top load correctly. Even the directory and file tree load fine.

The problem is when you click on a report everything from below the nav bar ( where the report would be rendered) blanks out and load bar at the bottom just tacks at full.

One way to fix this problem is to open the web config file for report manager save it then close the file.

This is not a viable solution to the problem. I was wondering if any one else had the same problem . If so how to fix it ?

Has anyone had a similar problem with reporting services. Also is there a place to formally site this as a bug

|||

We are having a very similar problem that is driving me nuts. My end users will get this situation - Report Manager banner and links at top, and blank below the color line. They all have the appropriate permissions. At first, we thought it was caused by an Internet Explorer patch, an uninstalling that patch seemed to work... for a little while. Now, they are complaining again. I get the same patches, and I still have full access (of course, I do have sys admin and sql admin rights). I have a suspicion that it is not an IE patch, but an IE security setting or security policy that may be causing it. When I looked at the Report Manager homepage in FrontPage (just to see what I could see), I noticed that banner is rendered in HTML, and it sets the language for the rest of the page to Javascript. If the Internet Explorer security doesn't allow Javascript, or even prompts for permissions, that may be preventing the display of the rest of the page, and would explain why the banner displays and doesn't just throw an error message to the end user. I am investigating this more. Let me know if you have any thoughts.

Tee Dubs