_WA_SYS% indexes

Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
sysindexes impact performance on update, delete or insert?
Any answer will be helpful.
Thanks
TarloNo, These are not indexes, they don't have a b-tree associated with them, don't store any data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
> sysindexes impact performance on update, delete or insert?
> Any answer will be helpful.
> Thanks
> Tarlo|||Thanks a bunch it helps.
Regards
Tarlo
"Tibor Karaszi" wrote:
> No, These are not indexes, they don't have a b-tree associated with them, don't store any data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Tarlo" <Tarlo@.discussions.microsoft.com> wrote in message
> news:500DE04C-0239-49B3-BD6C-6B90D44D6C1A@.microsoft.com...
> > Does _WA_SYS%(I understand these are optimizer statistics indexes) indexes in
> > sysindexes impact performance on update, delete or insert?
> >
> > Any answer will be helpful.
> >
> > Thanks
> >
> > Tarlo
>
>

[SQLCE Mobile]Do Select during Transaction

Hi,

I need to do a Select and after a Insert during a Transaction. But when i want to commit this transaction i've the following error message :

The transaction can not be committed if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before committing the change.

I don't understand this error message. How can i close my cursors.

Before my Insert i'm closing my SqlCeDataReader and i dispose it. My SqlCeCommand are also closed and dispose.

Do you have any ID?

Thanks.

Hi Kardath,

I tried to repro this here and could not reproduce the problem. I am able to have both INSERT and SELECT in a single transaction. Can you please try to dispose the all the sqlce objects and see if the problem disappers?

Thanks,

Laxmi

How to insert a byte() to a blob column?

Hi everybody,

I don't where to asked this question in this forum. How do you insert to a column of a table with a blob or binary datatype if there is one, from a byte() datatype? What is wrong? How can I fixed this? I need help. Thanks.

Code:
string sqlText = "Insert table1(id, dataByte) values('" + id + "'," + byteData + ")";

OdbcCommand cmd = new OdbcCommand(sqlText, odConn);

//opening connection here

int iRes = cmd.ExecuteNonQuery();

Result:
iRes = -1
den2005parameterize, parameterize, parameterize
read up on parameters

on a side note - never build sql!
on another side note. . . don't use odbc (there are bugs in the MDAC sql odbc driver)
use oledb. . .
better yet - use the sqlclient library

untested code (might have missed a particular point but this is the gist):

SqlDbCommand cmd = new SqlDbCommand("Insert table1(id, dataByte) values(@.id , @.data)", sqlConn);
cmd.Parameters["@.id"].ParameterValue = id;
cmd.Parameters["@.data"].ParameterValue = byteData;
int iRes = cmd.ExecuteNonQuery()

Research "how to store an image in a database" - its the same concept.
Again. . . parameterize your queries.
Insist that your peers do the same.

Security - Performance - Maintainence

and this question belongs in .Net Data Access Forum|||Thanks for reply, Blair Allen. I solved this my problem now is retrieving this BLOB from database and converting it to byte() and loading it to a Micorosft.Ink object using Ink.Load() method. The error occurs at Ink.Load() statement. Can anyone help? Thanks for advise. I'll post this problem at .Net Data Access Forum.

den2005|||I think this is it:
just hacked, not checked

byte[] bytes = null;
/* first get the size. . . */
int num = MySqlDataReader.GetBytes("myBlobField", 0, null, 0, int.MaxValue);
if (num != 0)
{
/* allocate the bytes */
bytes = new byte[num];
/* load the bytes */
MySqlDataReader.GetBytes("myBlobField", 0, bytes, 0,num)
}

cheers|||Thanks Blair Allen for reply I used a different approach. I converted the byte() to a base64 string format and then store it as a Text data in database and retrieving it as string and used Convert.FromBase64String() method to convert it back to byte() and load it to Ink.Load() and it works.

den2005

[DBNMPNTW]ConnectionOpen (CreateFile()).

Hi,
When i tried to run insert statement ,i am getting below
error,please give your expert solutions.We are running
sqlserver7 on active/active cluster on NT4.0.
&&&&
[OLE/DB provider returned message: Client unable to
establish connection]
[DBNMPNTW]ConnectionOpen (CreateFile())
&&&&&&
Thanks
Motati
Looks like you are using the named pipes client library, do you still get an
error if you switch to tcp/ip ?
Vikram
"Motati" <motati@.hotmail.com> wrote in message
news:1aff401c44f0b$fc72c0f0$a001280a@.phx.gbl...
> Hi,
> When i tried to run insert statement ,i am getting below
> error,please give your expert solutions.We are running
> sqlserver7 on active/active cluster on NT4.0.
> &&&&
> [OLE/DB provider returned message: Client unable to
> establish connection]
> [DBNMPNTW]ConnectionOpen (CreateFile())
> &&&&&&
> Thanks
> Motati

[DBNMPNTW]ConnectionOpen (CreateFile()).

Hi,
When i tried to run insert statement ,i am getting below
error,please give your expert solutions.We are running
sqlserver7 on active/active cluster on NT4.0.
&&&&
[OLE/DB provider returned message: Client unable to
establish connection]
[DBNMPNTW]ConnectionOpen (CreateFile())
&&&&&&
Thanks
MotatiLooks like you are using the named pipes client library, do you still get an
error if you switch to tcp/ip ?
Vikram
"Motati" <motati@.hotmail.com> wrote in message
news:1aff401c44f0b$fc72c0f0$a001280a@.phx
.gbl...
> Hi,
> When i tried to run insert statement ,i am getting below
> error,please give your expert solutions.We are running
> sqlserver7 on active/active cluster on NT4.0.
> &&&&
> [OLE/DB provider returned message: Client unable to
> establish connection]
> [DBNMPNTW]ConnectionOpen (CreateFile())
> &&&&&&
> Thanks
> Motati

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together
.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? Ho
w
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert
a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
in
> each set MUST be kept together, and sets in each batch MUST be kept togeth
er.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updatin
g
> won't be a problem, but inserting will be) before I start inserting rows?
How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So giv
e
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonagh
Looking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:

> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh
|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David
|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:

> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best RegardsMake a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best Regards
Make a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I
|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s returned NULL value?
Should i do an INSERT in the master..sysservers? or can you tell me if exists something that correct this situation?
Best RegardsMake a backup of master firs
do sp_dropserver 'servername
g
sp_addserver 'servername', loca
g
then stop and start your server
Then select @.@.servernam
Jef
MCDBA, MCSE+|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@Identity returns a decimal?

After doing an insert, I am using a System.Data.SqlClient.SqlDataReader to fetch the identity of the inserted row with the following code:

dbCommand.CommandText = "SELECT @.@.IDENTITY As LogID SET NOCOUNT OFF"
Dim dataReader As System.Data.SqlClient.SqlDataReader = dbCommand.ExecuteReader
dataReader.Read
dataReader.GetInt32(0)
dataReader.Close

The problem is that I'm getting an invalid cast exception. If I use dataReader.GetDataTypeName(0), it tells me that value is a decimal? This is odd because the identity column in the SQL table is an int (size: 4). When I use very similar code for another database on the same server, it works.
Anybody have any ideas?

According to the SQL Books Online @.@.Identity is of type numeric (not integer). This would explain why it is returned as decimal instead of an integer.
Brian

|||@.@.Identity doesnt return the ID from the same scope. You should use SCOPE_IDENTITY and furthermore, you should use a stored procedure, which does the insert AND returns the new ID.|||

Hi,
I'm adding a simple stored procedure to show a sample for SonuKapoor's note

CREATE PROCEDURE SiteCreate
(
@.SiteName as nvarchar(100)
)

AS

INSERT INTO Site SELECT @.SiteName, 1

SELECT SCOPE_IDENTITY()

GO

Eralper
http://www.kodyaz.com

|||If we're going down the, "You don't want to do it like that route" then you shouldn't 'SELECT' the value back, you should use an OUTPUT param, and also include the SET NoCount ON, oh oh oh ;) use INSERT ([cols]) VALUES ([values]) syntax, don't select into the insert like that.