sql rowcount: dear

Showing posts with label dear. Show all posts

Tuesday, March 20, 2012

[Ugent] attach Database error in SQL 7.0

Dear All
I have try to detach and re-attach database from SQL7.0 (Production Server).
The detach process was success, but re-attach database occur a error. There
is a Error message show on my screen.
Changed Language setting to us_english.
Server: Msg 3624, Level 20, State 1, Line 1
Location: pageref.cpp:3882
Expression: pPage->GetLsn () == lp -> GetPrevPageLsn ()
SPID: 9
Process ID: 148
Connection Broken
Do anyone got experience to fix it ? Thanks very much
Gary
Hi Gary
"Gary" wrote:

> Dear All
> I have try to detach and re-attach database from SQL7.0 (Production Server).
> The detach process was success, but re-attach database occur a error. There
> is a Error message show on my screen.
> ----
> Changed Language setting to us_english.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: pageref.cpp:3882
> Expression: pPage->GetLsn () == lp -> GetPrevPageLsn ()
> SPID: 9
> Process ID: 148
> Connection Broken
> ----
> Do anyone got experience to fix it ? Thanks very much
I have not seen the error, but you may want to try renaming the log file and
using sp_attach_single_file_db. I would also check that this disc if fine
(CHKDSK or similar) and that no errors are being reported in the System Event
log.

> Gary
>
John

[Ugent] attach Database error in SQL 7.0

Dear All
I have try to detach and re-attach database from SQL7.0 (Production Server).
The detach process was success, but re-attach database occur a error. There
is a Error message show on my screen.
----
--
Changed Language setting to us_english.
Server: Msg 3624, Level 20, State 1, Line 1
Location: pageref.cpp:3882
Expression: pPage->GetLsn () == lp -> GetPrevPageLsn ()
SPID: 9
Process ID: 148
Connection Broken
----
--
Do anyone got experience to fix it ? Thanks very much
GaryHi Gary
"Gary" wrote:

> Dear All
> I have try to detach and re-attach database from SQL7.0 (Production Server
).
> The detach process was success, but re-attach database occur a error. Ther
e
> is a Error message show on my screen.
> ----
--
> Changed Language setting to us_english.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: pageref.cpp:3882
> Expression: pPage->GetLsn () == lp -> GetPrevPageLsn ()
> SPID: 9
> Process ID: 148
> Connection Broken
> ----
--
> Do anyone got experience to fix it ? Thanks very much
I have not seen the error, but you may want to try renaming the log file and
using sp_attach_single_file_db. I would also check that this disc if fine
(CHKDSK or similar) and that no errors are being reported in the System Even
t
log.

> Gary
>
John

Monday, March 19, 2012

[SQL Server 2000] How to programmatically read Merge Agent properties

Dear all,
I would like to read from SQL Server information that is presented in
Enterprise Manager \ <server_name> \ Replication Monitor \ Agents \ Merge
Agents (particularly the columns "Subscription" and "Action Time").
Which tables should I read? I checked the tables in msdb and master database
but couldn't find anything suitable. BOL and Google were of no help either.
Thank you in advance for your help.
Best regards,
AndrewTry a profiler trace and examine the source of the executed procs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:uBPvoxZAHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I would like to read from SQL Server information that is presented in
> Enterprise Manager \ <server_name> \ Replication Monitor \ Agents \ Merge
> Agents (particularly the columns "Subscription" and "Action Time").
> Which tables should I read? I checked the tables in msdb and master
> database
> but couldn't find anything suitable. BOL and Google were of no help
> either.
> Thank you in advance for your help.
> Best regards,
> Andrew
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:12D028F8-1237-454D-8387-4A30B283851F@.microsoft.com...
> Try a profiler trace and examine the source of the executed procs.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>> Dear all,
>> I would like to read from SQL Server information that is presented in
>> Enterprise Manager \ <server_name> \ Replication Monitor \ Agents \ Merge
>> Agents (particularly the columns "Subscription" and "Action Time").
>> Which tables should I read? I checked the tables in msdb and master
>> database
>> but couldn't find anything suitable. BOL and Google were of no help
>> either.
>> Thank you in advance for your help.
>> Best regards,
>> Andrew
Ok, I've checked, but unfortunately that is an undocumented procedure:
sp_MSenum_replication_agents @.type = 4
(type = 4 stands for merge replication agent)
Thank you for your help.
Best regards,
Andrew|||I took cursory look at the text of that proc and the called proc and it
looks to me like the source of the data is the MSmerge_history table. It's
documented in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:uizTrebAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:12D028F8-1237-454D-8387-4A30B283851F@.microsoft.com...
>> Try a profiler trace and examine the source of the executed procs.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> Dear all,
>> I would like to read from SQL Server information that is presented in
>> Enterprise Manager \ <server_name> \ Replication Monitor \ Agents \
>> Merge
>> Agents (particularly the columns "Subscription" and "Action Time").
>> Which tables should I read? I checked the tables in msdb and master
>> database
>> but couldn't find anything suitable. BOL and Google were of no help
>> either.
>> Thank you in advance for your help.
>> Best regards,
>> Andrew
> Ok, I've checked, but unfortunately that is an undocumented procedure:
> sp_MSenum_replication_agents @.type = 4
> (type = 4 stands for merge replication agent)
> Thank you for your help.
> Best regards,
> Andrew
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CF65CD9F-E7BA-4FAB-AC81-326BD56C2FB7@.microsoft.com...
>I took cursory look at the text of that proc and the called proc and it
>looks to me like the source of the data is the MSmerge_history table. It's
>documented in the Books Online.
[...]
> Dan Guzman
> SQL Server MVP
>> Dear all,
>> I would like to read from SQL Server information that is presented in
>> Enterprise Manager \ <server_name> \ Replication Monitor \ Agents \
>> Merge
>> Agents (particularly the columns "Subscription" and "Action Time").
>> Which tables should I read? I checked the tables in msdb and master
>> database
>> but couldn't find anything suitable. BOL and Google were of no help
>> either.
>> Ok, I've checked, but unfortunately that is an undocumented procedure:
>> sp_MSenum_replication_agents @.type = 4
>> (type = 4 stands for merge replication agent)
Dan,
Thank you for your help!
Best regards,
Andrew

Thursday, March 8, 2012

[Q]Problems related to the MySQL linked Server.

Dear everyone.
I have two questions about the linked server for the MySQL.
I need to connect to the mysql DB from the my SQLServer 2000 SP4 on the
Windows 2000 standard Edition(MDAC 2.8)
So, I made a linked server at the SQLServer. I worked well, but I faced with
some problem.
1. Query method
I need to query some data in the mysql with dynamic parameter.
ex) select @.v_intUserNo = uno
from tb_test
where userid = 'testid'
*input of the userid is changed everytime by the user.
so, I cannot use the OPENQUERY or OPENROWSET.
I want to query above like this,
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
When I used above syntax at the linked server of the SQLServer, it worked
well.
But, it did not worked with the mysql DB.
(unfortunately, I cannot remember the exact error message.T.T)
2. Not killable SPID.
After #1 step, I tried many method to solve the problem.
As one trial, I set the catalog at the linked server's property.
After that, I tried query as followings
---
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
or select @.v_intUserNo = uno
from OPENQUERY('TEST_DB', 'select * from test_tbl where userid =
''testid''')
---
It doesn't returned any error or result, so, I killed the session.
but it was not cleared promptly, only return the following message.
SPID 70: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
After restarting the SQLServer, they were cleared.
I wish to know the all the experiencies for managing the linked server to
the mysql DB at the SQLServer.
Thank you in advanceFurther information of my environment.
1. I used the mysql ODBC Ver 3.51
2. The error messge when I query data with following SQL.
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
**Following query also doesn't work.
select @.v_intUserNo = uno
from [linked server name].[catalog name].[table name]
where userid = 'testid'
select @.v_intUserNo = uno
from [linked server name].[schema name].[table name]
where userid = 'testid'
select @.v_intUserNo = uno
from [linked server name].[table name]
where userid = 'testid'
Thank you.
"?" <dialup@.nownuri.net> wrote in message
news:ObJWtpObGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Dear everyone.
> I have two questions about the linked server for the MySQL.
> I need to connect to the mysql DB from the my SQLServer 2000 SP4 on the
> Windows 2000 standard Edition(MDAC 2.8)
> So, I made a linked server at the SQLServer. I worked well, but I faced
with
> some problem.
> 1. Query method
> I need to query some data in the mysql with dynamic parameter.
> ex) select @.v_intUserNo = uno
> from tb_test
> where userid = 'testid'
> *input of the userid is changed everytime by the user.
> so, I cannot use the OPENQUERY or OPENROWSET.
> I want to query above like this,
> select @.v_intUserNo = uno
> from [linked server name].[catalog name].[schema name].[
table name]
> where userid = 'testid'
> When I used above syntax at the linked server of the SQLServer, it worked
> well.
> But, it did not worked with the mysql DB.
> (unfortunately, I cannot remember the exact error message.T.T)
> 2. Not killable SPID.
> After #1 step, I tried many method to solve the problem.
> As one trial, I set the catalog at the linked server's property.
> After that, I tried query as followings
> ---
> select @.v_intUserNo = uno
> from [linked server name].[catalog name].[schema name].[
table name]
> where userid = 'testid'
> or select @.v_intUserNo = uno
> from OPENQUERY('TEST_DB', 'select * from test_tbl where userid =
> ''testid''')
> ---
> It doesn't returned any error or result, so, I killed the session.
> but it was not cleared promptly, only return the following message.
> SPID 70: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> After restarting the SQLServer, they were cleared.
> I wish to know the all the experiencies for managing the linked server to
> the mysql DB at the SQLServer.
> Thank you in advance
>
>

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv())DId you enable remote con

Dear All

I am using sql server 2000 SP4. Os is win2003 server. I was able to register sql server from my server using Enterprise manager. but i am not able to register the same from any client machine. pls help me

raffi

Hi,

DId you enable remote connections yet ? Have a look at my screencast on my site. You will find a tutorial to enable remote connections for your SQL Server 2005. By default this is turned off.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv())

Dear All

raffi

Hi,

DId you enable remote connections yet ? Have a look at my screencast on my site. You will find a tutorial to enable remote connections for your SQL Server 2005. By default this is turned off.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, February 24, 2012

[help]:SQLServer 2000 setup fails!

Dear All:
I receive the error with _INS5576._MP.exe when i installed sqlserver 2000
pls help me to solve this problem
ThanksWhat is the version of MSSQL server you tried to install?
What is your operating system?
Can you post the error message you got?|||Originally posted by gyuan
What is the version of MSSQL server you tried to install?
What is your operating system?
Can you post the error message you got?

i wanna to install STANDARD version in my windows 2000 server
I receive the error with "application error in _INS5576._MP "

pls help me ,thanks|||In which step did you get the error message?|||Originally posted by gyuan
In which step did you get the error message?

The first step,i click the SETUPSQL.EXE|||What is the Service Pack on your windows 2000 server? When you tried to install the MSSQL server did you close any other applications?|||Did you log on windows using Administrator account?|||Originally posted by gyuan
What is the Service Pack on your windows 2000 server? When you tried to install the MSSQL server did you close any other applications?

I've installed sp3 ,i have closed any other applictions when i install the sqlserver and i use administrator installed it|||Is is solved ? If not, read the following link:

link (http://dbforums.com/arch/175/2002/3/331731)

Also, check your installation log files for more information- sqlstp.log, sqlsp.log, and setup.iss.|||Originally posted by rnealejr
Is is solved ? If not, read the following link:

link (http://dbforums.com/arch/175/2002/3/331731)

Also, check your installation log files for more information- sqlstp.log, sqlsp.log, and setup.iss.

but i didn't find ActiveSync in my computer

can you tell me where is the sqlstp.log ,sqlsp.log,and setup.iss?|||Originally posted by board
but i didn't find ActiveSync in my computer

can you tell me where is the sqlstp.log ,sqlsp.log,and setup.iss?

here is the sqlstp.log

15:23:58 Begin Setup
15:24:28 ?ɡ

15:24:28 End Action: Locked Connectivity Files Check
15:24:28 ??? Microsoft ? (MDAC) ...
15:24:28 D:\NET\SQLSER~1\ENTERP~1\x86\Other\sqlredis.exe /q:a /C:"setupre.exe WARN=1 -s -SMS"
15:24:43 ExitCode: 0
15:24:43 ??? Microsoft ??_ (MSDTC) ...
15:24:43 C:\WINNT\TEMP\SqlSetup\Bin\cldtcstp.exe -SupportDir "C:\WINNT\TEMP\SqlSetup\Bin" -DTCPkg "D:\NET\SQLSER~1\ENTERP~1\x86\Other\dtcsetup.exe" -LogFile "C:\WINNT\sqlstp.log"
15:24:43 Process Exit Code: (0)
15:24:43 InstallMSSearch instance: MSSQLSERVER
15:24:43 Software\Microsoft\Search\Install:Version : 9.107.5512
15:24:43 MSSearch 2.0 or greater version check returned 1
15:24:43 ??? Microsoft ??...
15:24:43 "D:\NET\SQLSER~1\ENTERP~1\x86\FullText\MSSearch\Sea rch\SearchStp.exe" /s /a:SQLServer
15:24:52 Process Exit Code: (0)
15:24:52 /Q:A /T:C:\WINNT\TEMP\ixp001.tmp
15:24:52 ??? HTML
15:24:52 HTML Help installer exit code: 0
15:25:14 End Action InstallPkgs
15:25:14 Begin Action MoveFileData:
15:25:14 Enabled SELFREGISTERBATCH
15:25:14 Enabled CORECOMPONENTHANDLING
15:25:47 Begin Action: MoveFileDataSpecial
15:25:47 End Action: MoveFileDataSpecial
15:25:47 End Action MoveFileData
15:25:47 Begin Action ProcessAfterDataMove:
15:25:47 D:\NET\SQLSER~1\ENTERP~1\x86\Binn\hhcol.exe C:\Program Files\Microsoft SQL Server\80\Tools\Books
15:25:49 End Action ProcessAfterDataMove
15:25:49 Begin Action BuildServer:
15:25:49 C:\WINNT\TEMP\SqlSetup\Bin\scm.exe -Silent 1 -Action 5 -ExePath "C:\Program Files\Microsoft SQL Server\MSSQL\binn\sqlservr.exe" -Service "MSSQLSERVER"
15:25:49 Process Exit Code: (0)
15:25:49 Begin Action: CreateRegistrySetSQL
15:25:49 End Action: CreateRegistrySetSQL
15:25:49 Begin Action: RegWriteSetupEntry
15:25:49 End Action: RegWriteSetupEntry
15:25:49 Begin Action: CreateSer
15:25:49 End Action: CreateSer
15:25:49 Begin Action: SkuIt
15:25:50 End Action: SkuIt
15:25:50 SetFileSecuritySQLAndAdmin for d:\Program Files\Microsoft SQL Server\MSSQL returned: 1350, 1350
15:25:50 SetFileSecuritySQLAndAdmin for C:\Program Files\Microsoft SQL Server\MSSQL returned: 1350, 1350
15:25:50 SetRegSecuritySQLAndAdmin for Software\Microsoft\MSSQLServer\MSSQLServer returned: 0, 0
15:25:50 Begin Action: UpdateSystemPath
15:25:50 Path successfully updated.
15:25:50 %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\Sy stem32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\BINN
15:25:50 End Action: UpdateSystemPath
15:25:51 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINNT\sqlstp.log" -I MSSQLSERVER -V 1 -M 0 -Q "Chinese_PRC_CI_AS" -H 1179808 -U sa -P
################################################## #############################

?

Chinese_PRC_CI_AS

-m -Q -T4022 -T3659

?

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

SQL Server ?

################################################## #############################

15:28:02 Process Exit Code: (-1)|||It looks like it is failing when configuring the server. Please post the output from cnfgsvr.out.|||Also, what is the translation on these 2 lines:

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

SQL Server ?|||Originally posted by rnealejr
Also, what is the translation on these 2 lines:

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

SQL Server ?

The problem had solved , I installed the sp4 for my windows 2000 server
It's ok now!

Thanks

Sunday, February 19, 2012

[DBNETLIB][ConnectionWrite (WrapperWrite()).] General Network error. Check

Dear All,
I am getting this error frequently and some times sql server is getting
stopped automatically.
[DBNETLIB][ConnectionWrite (WrapperWrite()).] General Network error. Check
you network documentation.
Can any one help me out to get rid of this.
OS : Windows 2000 professional
SQL Server version is 2000
Thanks and Regards,
PeriHi Peri,
Here are some things I would do if I were in your shoes:
1) Check the Event Log for Network problems... Specif with the Network Card
2) If the SQL Server is in another subnet, check the router for errors
3) Check HOW you are connecting, use TCPIP if you can
4) Ping your machine so it pings over and over (see /flags help).. See if
you ever get dropped
5) Check for issues with the CLIENT machine to
That's all I can think of right now.. Good luck!
Erik
"Peri" <lperi@.CSPL.com> wrote in message
news:emmKD1CaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I am getting this error frequently and some times sql server is getting
> stopped automatically.
> [DBNETLIB][ConnectionWrite (WrapperWrite()).] General Network error. Check
> you network documentation.
> Can any one help me out to get rid of this.
> OS : Windows 2000 professional
> SQL Server version is 2000
> Thanks and Regards,
> Peri
>|||I ran across this problem on my XP machine that went away when I re-loaded t
he machine and did NOT install Service Pack 2 of Windows. I still haven't f
igured out how to fix it yet. Maybe this information will lead you in the r
ight direction.

Thursday, February 16, 2012

[C#] Failing to use SCOPY_IDENTITY()

Dear Reader,

Currently I am building an application for a theme park where I work as a trainee for school, one project for me is to rebuild all the hundreds of databases into a few sql driven application's. Now I got a problem whit the use of SCOPE_IDENTITY(). Because the data has to be correct before inserting it into the database I use the transact features of .NET and I create 1 SQL string wich I dump in that method. The problem is that I can't be able to use the value of SCOPE_IDENTITY() for some reason, maybe you guys see a mistake in the actual (dynamic) query:
Here is the query built up by my program to write the data (of a single form) into the database:


DECLARE @.OID int;
INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)VALUES(3,1123,,'','','',,,,'','False','False',45,'',);
SELECT @.OID = SCOPE_IDENTITY();
INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, 2,23, 23 );

Everything else works unless the SCOPE_IDENTITY() things.
I hope someone can help me out fixing this mistake.
Tnx in advanced,

Grz.
StefanEXACTLY what is your problem? Does Medisch have an IDENTITY column? Why are you not passing any values for some rows (not sure this syntax would work). Why not just not include the column names in the column list?|||The problem is that SCOPE_IDENTITY() doesn't seem to return any values.
They don't have all valeus, because I did not fill in all the data in the form so it will insert a null value. The string is ok, only the part of returing the identity. (Medisch has an autoincrement column called med_ID).|||To make it clear, you are saying that @.OID is null?|||No, it does not seem to get any value :P. The following error ocurse:
Incorrect syntax near ',' wich occurs in the part

VALUES(@.OID, "+medI

so for some reason it does not want to get the value in the sqlvariabele @.OID.

For clearance, it will put that string in my first post inot the following method:


public bool SqlNieuweInvoer(string sqlDatabase, string sqlQuery)
{
SqlConnection conData = new SqlConnection("server="+strServerNaam+";" + "database="+sqlDatabase+";Trusted_Connection=yes");
SqlCommand comData = new SqlCommand(sqlQuery, conData);
conData.Open();SqlTransaction TranData = conData.BeginTransaction();
comData.Transaction = TranData;
try
{
comData.ExecuteNonQuery();
TranData.Commit();
return true;
}
catch (Exception e)
{
TranData.Rollback();
MessageBox.Show(Convert.ToString(e));
return false;
}
finally
{
conData.Close();
}
}

|||Is this an error when you compile? If so, then it has NOTHING to do with the SQL Syntax, it is an error in how you are building the string in C# code.|||This error occurs when running the application.
The user is going to fill in the form and if he presses the "OK" button of that form the query will be generated (for example it has to indiviualy add the different kinds of medicine related to the medical week rapport (that's why I need the SCOPE_IDENTITY() function to give me the internal auto generated increment value of that medical report).|||This is the code that builts the query bye the way:


private string BouwQuery()
{
cstrInvoerQuery = cstrInvoerQuery + "DECLARE @.OID int;";
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)" +
"VALUES("+this.DierID+","+tbWeekNr.Text+","+tbEnen.Text+",'"+tbBijzonderheden.Text+"','"+tbAfwijkendGedrag.Text+"','"+tbSexGedrag.Text+"',"+tbAdemfrequentie.Text+","+tbHoesten.Text+","+tbTemperatuur.Text+",'"+gcbConditie.Text+"','"+cbBloedgeprikt.Checked+"','"+cbCupje.Checked+"',"+tbBasisVis.Text+",'"+tbEetlust.Text+"',"+tbGewicht.Text+");";
cstrInvoerQuery = cstrInvoerQuery + " SELECT @.OID = SCOPE_IDENTITY();";string[] test = new string[] {};
int medID;
string strDelimiter = "\t[";
char[] delimiter = strDelimiter.ToCharArray();
foreach (string Item in lbMedicijnenSupplementen.Items)
{
if (!Item.StartsWith("Naam:"))
{
test = Item.Split(delimiter, 6);
medID = VerkrijgMedicijnSupplementID(test[0]);
if (medID != 0)
{
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, "+medID+","+test[1]+", "+test[2]+");";
}
}
}
if(cbBloedgeprikt.Checked)
{
cstrInvoerQuery = cstrInvoerQuery + "UPDATE OmgevingsWaardes SET ow_LaatstBloedGeprikt='"+tbWeekNr.Text+"' WHERE ow_za_ID = "+this.DierID+";";
}
tbBijzonderheden.Text = cstrInvoerQuery;
return cstrInvoerQuery;
}

Maybe this helps you find me an answer :).|||Your problem is this. You are calling ExecuteNonQuery. This DOES NOT expect a result set to be returned. However, you ARE returning a result set (that is what SELECT @.OID... does).

Use ExecuteReader and get back a DataReader, or better use ExecuteScaler() and the return from that method can be cast to an integer.|||That might be helpfull, I thought the whole query was being excuted on the SQL server, but they actually being excecuted all @. once? That explaines allot, thanks, I will go and trie the other 2 things :).|||Whoohoo!!! I used the scalar 1 and it finally works, thank you very very much :).

[BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred

Dear group
The following error occured
[BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred
and after using DBCC was rectified, but we are not sure the cause of
this problem.
Is it due to SAN installed last month or is there any other reason.
KaranError 605
Severity Level 21
Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object
'%.*ls', not to object '%.*ls'.
You have corruption in your DB.
Look at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Karan Lobana" wrote:
>
> Dear group
> The following error occured
> [BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred
> and after using DBCC was rectified, but we are not sure the cause of
> this problem.
> Is it due to SAN installed last month or is there any other reason.
>
> Karan
>

Thursday, February 9, 2012

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

SELECT @.@.IDENTITY

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

HTH, Jens Suessmeyer.

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

SELECT @.@.IDENTITY

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Tuesday, March 20, 2012

[Ugent] attach Database error in SQL 7.0

[Ugent] attach Database error in SQL 7.0

Monday, March 19, 2012

[SQL Server 2000] How to programmatically read Merge Agent properties

[SQL Server 2000] How to programmatically read Merge Agent properties

Thursday, March 8, 2012

[Q]Problems related to the MySQL linked Server.

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv())DId you enable remote con

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv())

Friday, February 24, 2012

[help]:SQLServer 2000 setup fails!

Sunday, February 19, 2012

[DBNETLIB][ConnectionWrite (WrapperWrite()).] General Network error. Check

Thursday, February 16, 2012

[C#] Failing to use SCOPY_IDENTITY()

[BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred

Thursday, February 9, 2012

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

sql rowcount

Blog Archive

About Me