CDC not tracking changes

Hi

I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.

any ideas ?

thanks

CDC is a SQL Server 2008 feature. Not to mention nothing to do with SSIS. Can you refine your issue so that we can redirect your question appropriately? This is a SQL Server 2005 forum for the SQL Server Integration Services tool.

|||

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

|||

Sqlgoof wrote:

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

Try the SQL Server 2008 Data Warehousing forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1617&SiteID=1

CDatabase::OpenEx( "ODBC; ...") question

CDatabase::GetConnect() returns "ODBC; ..."
However, CDatabase::OpenEx() may not include "ODBC;". It will fail to
connect if it has that prefix.
So you can not simply do:
{
CDatabase a, b;
a.OpenEx("..."); // assume this connects fine.
// try another connection to the same.
b.OpenEx( a.GetConnect() ); // fails: Data source name not found and no
default driver specified
}
I must remove the "ODBC;" prefix.. Why this intolerance? This is cumbersome!
This means I can not simply store GetConnect() string to resource and open a
new DB connection using it.
I guess I can use Open() instead of OpenEx() but that sucks too.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.vc.mfc:407326 microsoft.public.s
qlserver.odbc:41712
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:Ohmp0if2DHA.2528@.TK2MSFTNGP10.phx.gbl...

quote:

---

> CDatabase::GetConnect() returns "ODBC; ..."
> However, CDatabase::OpenEx() may not include "ODBC;". It will fail to
> connect if it has that prefix.
> So you can not simply do:
> {
> CDatabase a, b;
> a.OpenEx("..."); // assume this connects fine.
> // try another connection to the same.
> b.OpenEx( a.GetConnect() ); // fails: Data source name not found and

---

no

quote:

---

> default driver specified
> }
> I must remove the "ODBC;" prefix.. Why this intolerance? This is

---

cumbersome!

quote:

---

> This means I can not simply store GetConnect() string to resource and open

---

a

quote:

---

> new DB connection using it.
> I guess I can use Open() instead of OpenEx() but that sucks too.

---

You could perhaps write a function that extracts the fields you want.
b.OpenEx( to_conn_str(a.GetConnect()) );
Tom.|||Yep, I did.. CDBString::MinimalizeODBC( s.GetConnect() );
Better yet, probably subclass CDatabase, override the OpenEx to just remove
the ODBC;
Thanks.
"TT (Tom Tempelaere)" <_N_OSPAMtiti____@.hotmail.comMAPSO_N_> wrote in
message news:Ei0Nb.11053$cQ5.3537008@.phobos.telenet-ops.be...

quote:

---

> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:Ohmp0if2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> no
> cumbersome!
open[QUOTE]
> a
> You could perhaps write a function that extracts the fields you want.
> b.OpenEx( to_conn_str(a.GetConnect()) );
> Tom.
>

---

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Cast Problem

If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))
ORDER BY SpecimenID DESC

However, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found... Can someone please explain what I am missing here...

MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

*"AccessionPresent" is the value of the text field retrieved from the form.

I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.

Thank you for any or all assistance !!!

Looks like you have a blank value that you are trying to convert to int. Check for NULL/Blanks in your application before you CAST as varchar.

|||

Instead of:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

Try:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST('" & AccessionPresent &"' AS VARCHAR(50))"

Remove the Paren's, also as shark said, do something like if string.isnullorempty(AccessionPresent) then put a dummy value their or whatever

|||

Try this (use this query in your MySQL):

1SELECT *2FROM ClinicalSpecimen3WHERE SpecimenID =CAST(4ISNULL(5 ('" & AccessionPresent & "')6 , -1-- you will get -1 for Null cases7 )8AS VARCHAR(50)9 )1011-- I splited the query to make it more readable (you can put it in one line)

Hope this will help.

Good luck.

|||

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't.

Try:

Dim conn as new SqlConnection(...)

Dim cmd as new SqlCommand("SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))=@.SpecimenID",conn)

cmd.Parameters.Add("@.SpecimenID",SqlDbType.varchar).Value=AccessionPresent

Note, this also removes the SQL Injection problem you had.

|||

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

|||

ndinakar:

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

Yes, good catch Motley

How we did not realise that!!