EXEC Command in all existing connections

If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, Reuven
Thanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, ReuvenThanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

Exception - Insert Record into DB

Hello

I'm using express edition to create my trail testapplication. Below is the code that I have and I'm trying to insert data to thedatabase table named "Coin".

ProtectedSubbtnSave_Click(ByVal senderAsObject,ByVal eAsSystem.EventArgs)Handles btnview.Click

Dim sAsString = txtCname.Text

'Dim myConnection As NewSqlConnection(myConnString)

Dim descAsString = txtCDesc.Text

Dim ConStrAsNew SqlClient.SqlConnection

ConStr.ConnectionString ="server=test\sqlinstance;Integrated Security=True"

Response.Write("Connection string: " & ConStr.ConnectionString)

Try

Dim SelectQueryAsString ="SELECTmax(coinid) from coin"

Dim idvalAsInteger = 0

Dim commandAsNew SqlCommand(SelectQuery, ConStr)

ConStr.Open()

idval = command.ExecuteScalar()

Console.WriteLine(idval)

idval = idval + 1

Dim InsertQueryAsString ="INSERTINTO COIN(coinname, coinid, ebayid, ebaymember, ebaymemid , amount , coindesc)VALUES('1992-Proof'," & idval &",'eewerwer','sp6937','serwryana',67.70,'MattProff of 1992- Mint Set')"

Dim command1AsNew SqlCommand(InsertQuery, ConStr)

command1.ExecuteScalar()

Dim S1AsString ="Recordinsert - Successful!"

Console.WriteLine(S1)

Catch exAsException

Label2.Text = ex.ToString

Finally

ConStr.Close()

EndTry

End Sub

This program isthrowing an exception (mentioned below)

Exception --> System.Data.SqlClient.SqlException:Invalid object name 'coin'. atSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception, BooleanbreakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSetbulkCopyHandler, TdsParserStateObject stateObj) atSystem.Data.SqlClient.SqlDataReader.ConsumeMetaData() atSystem.Data.SqlClient.SqlDataReader.get_MetaData() atSystem.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResultresult) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) atSystem.Data.SqlClient.SqlCommand.ExecuteScalar() at _Default.btnview_Click(Objectsender, EventArgs e) in C:\Documents and Settings\arsha\My Documents\VisualStudio 2005\WebSites\WebSite1\Default.aspx.vb:line 92

"Coin" is the table name – which is in SQL server. Kindlyhelp me to handle and overcome this exception.

Thanks

perhaps you have set up your database with a case sensitive collation?

Try altering your sql statement so it's case exactly matches your table.

also, i would strongly recommend that you let sql server take care of incrementing the CoinId data by using an identity column. By trying to do it yourself, if 2 people run your page simultaneously, you could end up with a problem.

|||

Try using ExecuteNonQuery instead ofExecuteScalar