sql reports: error

Showing posts with label error. Show all posts

Thursday, March 29, 2012

Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Sunday, March 25, 2012

Can views be used in query builder?

I tried it but got an invalid object error. I then copied and pasted the sql statement contained in the view but it doesn't run correctly inside query builder and only shows 1 valid row, and a 2nd row that's blank the the first column and has a number 1 in the second column. The view runs correctly in SQL Server Management Studio Express.

How about wrapping the view inside a stored procedure?

|||

Stored procedures can't be used in query builder either. All I can do is put in a dummy SQL statement (like Select 1 as column1, 2 as whatever) and then change it to a stored procedure later when I have access to the "data" tab in [Design].

That doesn't seem right but is the only way I can figure out how to do it.

Thursday, March 22, 2012

Can variable be used in SQL UPDATE statement in VB.NET

Hy, i have this problem in vb.net:

I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:

Dim variable_name As String

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

When I don't use a variable after SET statement, everything work fine. This code works fine:

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET column_name = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

Please, if someone can help me in this...thanks..

Hi,

Did you missing ampersand sign in ?

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Try

Dim sqlStringAsString = ("UPDATE table_name SET " & variable_name &" = " & variable &" WHERE UserID = '" & UserID &"'")

|||

thanks on answering..i was getting numeric value, but need string value, that was a problem, variable_name has numeric value, and that was error in sql statement...thanks on help

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
Thanks in advance
TakeCare
Love
Amit
dev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegr oups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID = Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID = Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID = dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>sql

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>

can u ignore errors in a trigger

if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the tableI don't think so.

What kind of errors are you getting?|||you might be able to eat them in 2005 with try/catch. however it seems better to eliminate the root cause of the error, rather than covering it up.|||DROP TRIGGER <trigger_name>

But I would fix the trigger|||I suppose you could temorarily disable triggers. That seems like a dumb thing to do, but you could... I guess.

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
PaulAFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
doesn't matter when the page was torn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all 1)
are at read time. At least that is how I read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
option is set, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page detection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to a
>> torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page
>>detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore
>> or can it error due to a torn page that may have been in the database
>> for a while.
>> Thanks
>> Paul
>>
>>
>|||Ahh, thanks. I assume there must be some flag in the page header saying something like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which the code can determine
whether to check for tp or not?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages that have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
>> option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on at
>>the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to
>> a torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>>
>>
>|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages
>> that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But
>> detection (all 0 or all 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during
>> restore or can it error due to a torn page that may have been in the
>> database for a while.
>> Thanks
>> Paul
>>
>>
>>
>>
>|||> So torn page detection wil activate only for fresh corruption.
That is not how I read Paul's statement. Assuming you have had torn page detection on for the
lifetime of the database. Then, as I understand it, a torn page could have happened a year ago.
Assuming you haven't read that page since it happened until now, you wouldn't have discovered it
until now.
Above scenario (again, as I understand how it works) is not very likely, though.
First, torn pages should be detected by DBCC CHECKDB, which I assume you do regularly.
Also, a torn page is most likely to occur if the system is shut down unexpectedly, and during the
following startup and the recovery phase, the pages which are torn would be very likely to be read
as they are likely to be involved in the recovery work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
> So torn page detection wil activate only for fresh corruption.
> That helps ie our problems are current.
> We are in one of those situations where the db is corrupting but our disks and controllers say all
> is well. We have turned off caching (batt backed) etc.
> No luck. We have installed sp4 and -T818. No errors from that.
> We have run the new sqliostress. No lost writes, stale reads etc but still the corruptions
> continue.
> System, dell pe 8450 raid 10, had been stable for over 2 years.
> We've built another box today and will move tonight.
> Wish us luck!
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying something like "torn
>> pages flagged/flipped" (i.e. the page was written while detection was on) by which the code can
>> determine whether to check for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages that have been written
>> out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when
>> db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on
>>at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during restore or can it error due
>>> to a torn page that may have been in the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>
>>
>>
>|||I think I may have not been clear. We did not have torn page on but have
been doing a full checkdb/checkalloc using sqlmaint, once a week.
We did not have torn page on as our dell perc3/dc (rebadged megaraid 1600)
have battery backup and we have a hefty ups.
To try to resolve is we had a hardware problem we turned off write bac
cache. No luck we then installed sql sp4 and added -t818. This did not show
any errors.We then enabled torn page. We got a detection after a few hours.
I'm at a loss as to why the dell raid event log shows no errors. I've done a
raid consistency test and it came up clean. chkdsk is OK.
The new sqliostress looks like it an excellent simulation. I ran it with the
extra i/o checking flags. Again clean.
Well we should know by tomorrow night if it was hardware. If it is, then we
may have a very expensive door stop. We'll nuke the box and rebuild it but
I'd feel scared to go back to a box that shows no errors but corrupts data.
I guess we'll run all dells diags for an age, memory etc. This was my dream
machine. I wanted to spec out a great server. 24 small disks, raid 10, 6
channels, 128Mb cache per controller etc.
Perhaps the secret is keep it simple, two big mirrors and just throw a ton
of memory in.
What a fortnight.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FbYj6JjFHA.1044@.tk2msftngp13.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
> That is not how I read Paul's statement. Assuming you have had torn page
> detection on for the lifetime of the database. Then, as I understand it, a
> torn page could have happened a year ago. Assuming you haven't read that
> page since it happened until now, you wouldn't have discovered it until
> now.
> Above scenario (again, as I understand how it works) is not very likely,
> though.
> First, torn pages should be detected by DBCC CHECKDB, which I assume you
> do regularly.
> Also, a torn page is most likely to occur if the system is shut down
> unexpectedly, and during the following startup and the recovery phase, the
> pages which are torn would be very likely to be read as they are likely to
> be involved in the recovery work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
>> That helps ie our problems are current.
>> We are in one of those situations where the db is corrupting but our
>> disks and controllers say all is well. We have turned off caching (batt
>> backed) etc.
>> No luck. We have installed sp4 and -T818. No errors from that.
>> We have run the new sqliostress. No lost writes, stale reads etc but
>> still the corruptions continue.
>> System, dell pe 8450 raid 10, had been stable for over 2 years.
>> We've built another box today and will move tonight.
>> Wish us luck!
>> Paul
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying
>> something like "torn pages flagged/flipped" (i.e. the page was written
>> while detection was on) by which the code can determine whether to check
>> for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for
>> pages that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on).
>> But detection (all 0 or all 1) are at read time. At least that is how
>> I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>>> AFAIK, torn page detection is done every tome a page is accessed
>>> from disk. This mean that it doesn't matter when the page was torn.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during
>>> restore or can it error due to a torn page that may have been in
>>> the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>>
>>
>>
>>
>>
>sql

Sunday, March 11, 2012

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KM
I realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The t
ext, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KMI realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]
The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to sto
p
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.
Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:
>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Saturday, February 25, 2012

Can Someone PLEASE help! (PLEASE)

I can not figure out why I am getting this error. I have gone over everything and cant find any discrepancies.

I have confirmed that eveything matches up with the Database/tabel

Error:

Parameterized Query '(@.WorkSheetID nvarchar(14),@.Origin nvarchar(5),@.DealerName nvarc' expects parameter @.Address, which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Parameterized Query '(@.WorkSheetID nvarchar(14),@.Origin nvarchar(5),@.DealerName nvarc' expects parameter @.Address, which was not supplied.

Source Error:

Line 401: .Add(New SqlParameter("@.DOB", sDOB))Line 402: End WithLine 403: myCommand.ExecuteNonQuery()Line 404: myConnection.Close()Line 405: 'Lets Check to see if there is a Co Applicant

My Code:

Protected

Sub bSubmitNC_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles bSubmitNC.ClickDim myConnectionAs SqlConnectionDim myCommandAs SqlCommandDim connStringAsString ="Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True"Dim CPhoneNum, CWorkNum, CCellNum, sWorkSheetNum, sSaleOriginAsStringDim sDealerShipName, sBkupSlsRep, sDateCreated, sFirstName, sMiddleName, slastname, sBuyAddress, sSSN, sDOBAsStringDim scity, sState, sZip, sEmailAddres, sBuyersCounty, sSalesTaxAsStringDim SlsManLoginNAsStringDim myPrincipalAs IPrincipal =Me.User'Customer Information

sWorkSheetNum = lblCustWksnum.Text

sSaleOrigin = lblSaleOrigin.Text

sDealerShipName = lblDealerShipName.Text

sDateCreated = lblDateCreated.Text

sBkupSlsRep = lblBkupSlsRep.Text

sFirstName = lblcFirstName.Text

sMiddleName = lblcMiddleName.Text

slastname = lblcLastName.Text

sBuyAddress = rBuyAddress

scity = rcity

sState = rState

sZip = rZip

sEmailAddres = lblEmailAddres.Text

sBuyersCounty = lblBuyersCounty.Text

sSalesTax = lblSalesTax.Text

sSSN = lblSSN.Text

sDOB = lblDOB.Text

'Lets get the Current Logged on User

SlsManLoginN = lblSlsRep.Text

myConnection =

New SqlConnection

myConnection.ConnectionString = connString

myConnection.Open()

CPhoneNum = lblHnum.Text

CWorkNum = lblWnum.Text

CCellNum = lblCnum.Text

Const sqlAsString ="Insert into CustomerTable (WorkSheetID, Origin, DealerName, DateCreated, SalesRep, " _

"BkupSalesRep, FirstName, MiddleName, LastName, Address, City, State, Zip, HomePhone, WorkPhone, " _

"CellPhone, Email, County, SalesTax, SS, DOB) Values(@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, @.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

myCommand =

New SqlCommand(sql, myConnection)With myCommand.Parameters

.Add(

New SqlParameter("@.WorkSheetID", sWorkSheetNum))

.Add(

New SqlParameter("@.Origin", sSaleOrigin))

.Add(

New SqlParameter("@.DealerName", sDealerShipName))

.Add(

New SqlParameter("@.DateCreated", sDateCreated))

.Add(

New SqlParameter("@.SalesRep", SlsManLoginN))

.Add(

New SqlParameter("@.BkupSalesRep", sBkupSlsRep))

.Add(

New SqlParameter("@.FirstName", sFirstName))

.Add(

New SqlParameter("@.MiddleName", sMiddleName))

.Add(

New SqlParameter("@.LastName", slastname))

.Add(

New SqlParameter("@.Address", sBuyAddress))

.Add(

New SqlParameter("@.City", scity))

.Add(

New SqlParameter("@.State", sState))

.Add(

New SqlParameter("@.Zip", sZip))

.Add(

New SqlParameter("@.HomePhone", CPhoneNum))

.Add(

New SqlParameter("@.WorkPhone", CWorkNum))

.Add(

New SqlParameter("@.CellPhone", CCellNum))

.Add(

New SqlParameter("@.Email", sEmailAddres))

.Add(

New SqlParameter("@.County", sBuyersCounty))

.Add(

New SqlParameter("@.SalesTax", sSalesTax))

.Add(

New SqlParameter("@.SS", sSSN))

.Add(

New SqlParameter("@.DOB", sDOB))EndWith

myCommand.ExecuteNonQuery()

myConnection.Close()

EndIfEndIfEndSub

Make sure you clear the sqlParameters before you ever add something to it.

After looking at that code i'm pretty confused on which way you are trying to do this. Are you using sql stored procedures or regular sql statements?

Can you please post your sql stored procedure if your using one. I can help you further but I need to know what one your going to plan on using.
|||

I am not using any stored procedures... Under Server Exploror/Data Connections I just have the CustomerInfo.mdf./Tables I dont have anything under Stored procedures

Whats got me stumped is I had this working and was successfully populating data into my database.

And for somereason unknown to me, Its not working now

I did tested the Data Connecttion by right clicking on the CustomerInfo.mdf and choose the "test Connection" and it came back successful.

I dont have any other code. This is a simple Webform with Textboxes and labels. User just files in all the informationa and clicks the Submit button.

Any help would beso greatly appricated.

|||

I did some research online and ended up makingn some changes... I am not getting any errors now but no data is being populated??

Here is what I changed to

Dim

myCommandAs SqlCommandDim myConnectionAs SqlConnectionDim insertCmdAsString

myConnection =

New SqlConnection("Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True")

insertCmd =

"Insert into CustomerTable Values (@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, " _

"@.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

myCommand =

New SqlCommand(insertCmd, myConnection)

myCommand.Parameters.Add(

New SqlParameter("@.WorkSheetID", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.WorkSheetID").Value = lblCustWksnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.Origin", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Origin").Value = lblSaleOrigin.Text

myCommand.Parameters.Add(

New SqlParameter("@.DealerName", Data.SqlDbType.Char))

myCommand.Parameters(

"@.DealerName").Value = lblDealerShipName.Text

myCommand.Parameters.Add(

New SqlParameter("@.DateCreated", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.DateCreated").Value = lblDateCreated.Text

myCommand.Parameters.Add(

New SqlParameter("@.SalesRep", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SalesRep").Value = lblSlsRep.Text

myCommand.Parameters.Add(

New SqlParameter("@.BkupSalesRep", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.BkupSalesRep").Value = lblBkupSlsRep.Text

myCommand.Parameters.Add(

New SqlParameter("@.FirstName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.FirstName").Value = lblcFirstName.Text

myCommand.Parameters.Add(

New SqlParameter("@.MiddleName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.MiddleName").Value = lblcMiddleName.Text

myCommand.Parameters.Add(

New SqlParameter("@.LastName", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.LastName").Value = lblcLastName.Text

myCommand.Parameters.Add(

New SqlParameter("@.Address", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Address").Value = rBuyAddress

myCommand.Parameters.Add(

New SqlParameter("@.City", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.City").Value = rcity

myCommand.Parameters.Add(

New SqlParameter("@.State", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.State").Value = rState

myCommand.Parameters.Add(

New SqlParameter("@.Zip", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Zip").Value = rZip

myCommand.Parameters.Add(

New SqlParameter("@.HomePhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.HomePhone").Value = lblHnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.WorkPhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.WorkPhone").Value = lblWnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.CellPhone", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.CellPhone").Value = lblCnum.Text

myCommand.Parameters.Add(

New SqlParameter("@.Email", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.Email").Value = lblEmailAddres.Text

myCommand.Parameters.Add(

New SqlParameter("@.County", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.County").Value = lblBuyersCounty.Text

myCommand.Parameters.Add(

New SqlParameter("@.SalesTax", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SalesTax").Value = lblSalesTax.Text

myCommand.Parameters.Add(

New SqlParameter("@.SS", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.SS").Value = lblSSN.Text

myCommand.Parameters.Add(

New SqlParameter("@.DOB", Data.SqlDbType.NVarChar))

myCommand.Parameters(

"@.DOB").Value = lblDOB.Text

myCommand.Connection.Open()

Try

myCommand.ExecuteNonQuery()

ShowMessageBox(

Me,"Record Added " & insertCmd)Catch exAs SqlExceptionIf ex.Number = 2627Then

ShowMessageBox(

Me,"ERROR: A record already exists with " _

"the same primary key")Else

ShowMessageBox(

Me,"ERROR: Could not add record")EndIfEndTry

myCommand.Connection.Close()

|||

I would suggest to use the function .AddWithValue and not Add and set the value separately. I think I had the same problem that you have. I have it working with AddWithValue.

Rumoldus

|||

(1) Try specifying the column names too in the INSERT statement as in :

"Insert into CustomerTable(WorkSheetId, Origin,..... ) Values (@.WorkSheetID, @.Origin, @.DealerName, @.DateCreated, " _

&"@.SalesRep, @.BkupSalesRep, @.FirstName, @.MiddleName, @.LastName, @.Address, @.City, @.State, " _

&"@.Zip, @.HomePhone, @.WorkPhone, @.CellPhone, @.Email, @.County, @.SalesTax, @.SS, @.DOB)"

(2) You can simplify your code a little bit by declaring and setting the value for the parameter in 1 line.

myCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int)).Value = 1

Friday, February 24, 2012

Can someone explain why this is not an error?

I accidentally did something similar to this, and I can't figure out why it
doesn't give an error.
Use Pubs
Select * From Authors
Where Au_LName In (Select Au_LName From Publishers)
Of course, "Select Au_LName From Publishers" by itself gives an error
message since Publishers doesn't have a field called AULName.
What's up? An error message would have helped here.
For a slightly different case, if the two tables have a field name in
common, and you use thet field in the inner Select, but don't qualify the
field name in the inner Select, what happens?
Thanks.
David WalkerDavid,
See the following thread:
http://groups.google.com/group/micr...6c9a254304c7629
HTH
Jerry
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||I think I know what happened here (someone more knowledgeable can correct me
if I am wrong)...
Since there is no Au_LName column in the Publishers table, SQL Server goes
looking for any other place in your statement that it can find a table with
a column named Au_LName. Since the Authors column is 'in scope' within the
subquery, the statement you posted is functionally equivalent to this:
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Publishers)
This in turn is functionally equivalent to:
--note the cartesian product in the subquery
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
If the Authors table was not 'in scope' in the subquery, then you could not
write correlated subqueries, since the outer table(s) would not be
accessible from inside the subquery. So everything worked as it should, but
it ends up looking really strange if you are not expecting this to happen.
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||Correction: that should be "Authors table", not "Authors column".
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>I think I know what happened here (someone more knowledgeable can correct
>me if I am wrong)...
> Since there is no Au_LName column in the Publishers table, SQL Server goes
> looking for any other place in your statement that it can find a table
> with a column named Au_LName. Since the Authors column is 'in scope'
> within the subquery, the statement you posted is functionally equivalent
> to this:
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Publishers)
> This in turn is functionally equivalent to:
> --note the cartesian product in the subquery
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
> If the Authors table was not 'in scope' in the subquery, then you could
> not write correlated subqueries, since the outer table(s) would not be
> accessible from inside the subquery. So everything worked as it should,
> but it ends up looking really strange if you are not expecting this to
> happen.
> "DWalker" <none@.none.com> wrote in message
> news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>|||OK, I double checked the execution plans, and I can plainly see that my
second statement is *not* equivalent - just ignore that. However, I appear
to have gotten the first part right, so I am batting .500!
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Correction: that should be "Authors table", not "Authors column".
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>|||Jeremy,
Batting .500...nice...you're leading both the National and the America
leagues. Would you mind batting for the Red Sox this w

end against the
Yankees? ;-)
Your theory makes sense. What really looks weird is when you replace the
Publisher's table with one of the system tables.
HTH
Jerry
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
> OK, I double checked the execution plans, and I can plainly see that my
> second statement is *not* equivalent - just ignore that. However, I appear
> to have gotten the first part right, so I am batting .500!
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in
news:e1hEnxTxFHA.3900@.TK2MSFTNGP10.phx.gbl:

> Jeremy,
> Batting .500...nice...you're leading both the National and the America
> leagues. Would you mind batting for the Red Sox this w

end against
> the Yankees? ;-)
> Your theory makes sense. What really looks weird is when you replace
> the Publisher's table with one of the system tables.
> HTH
> Jerry
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Baseball aside (go Braves!), yes it perhaps looks like a correlated
subquery, but it's really strange if you're not expecting it. But
correlated subqueries at least require you to say which field(s) you're
joining the two tables on.
In that older thread (which I started although it was nine months ago
and I made the same mistake again), Andrew Kelly said "If the column
name in the subselect is not part of that table it looks to the parent
select and if it finds it there it thinks all is well". But if it's
joining the inner select table with the table in the parent select,
you'd have to tell it which field to join on, I would think, unless it
assumes that any field that happened to be named the same is the join
field (or it's a Cartesian product). If you're not joining, then you're
just ignoring the table in the inner select?
While this might be per ANSI standards, I don't see it mentioned in BOL
anywhere.
This example from that thread:
SELECT *
FROM TblA
WHERE TblA.Col =
(SELECT TblA.ColB - TblB.ColC
FROM TblB)
is weird. How can you do the subtraction without knowing an ID column?
Or is it also a Cartesian product?
David|||On Fri, 30 Sep 2005 07:25:23 -0700, DWalker wrote:

>Baseball aside (go Braves!), yes it perhaps looks like a correlated
>subquery, but it's really strange if you're not expecting it. But
>correlated subqueries at least require you to say which field(s) you're
>joining the two tables on.
Hi David,
They usually do, but it's not a syntactical requirement. To understand
how SQL Server finds which column to use if you don't explicitly qualify
it, check out the following example:
SELECT ...
FROM Tab1
INNER JOIN Tab2
ON ...
WHERE ... = (SELECT ...
FROM Tab3
INNER JOIN Tab4
ON ...
WHERE ...)
Let's say that an unqualified column (TheCol) is found in the subquery.
These steps are taken:
1. Look in both Tab3 and Tab4 for a column with the name TheCol.
2. If a column with that name is found in both tables: raise error.
3. If a column with that name is found in one of the tables: use it.
4. If a column with that name is found in neither table, continue to
check Tab1 and Tab2 for a column named TheCol.
5. If a column with that name is found in both tables: raise error.
6. If a column with that name is found in one of the tables: use it.
7. If a column with that name is found in neither table: raise error.
On the other hand, if a reference to TheCol is found outside of the
subquery, only Tab1 and Tab2 are checked, since it's outside of the
scope where Tab3 and Tab4 are valid.
Of course, it's much better to get into the habit of qualifying all
column names whenever you use more than one table in your query!

>This example from that thread:
>SELECT *
>FROM TblA
>WHERE TblA.Col =
> (SELECT TblA.ColB - TblB.ColC
> FROM TblB)
>is weird. How can you do the subtraction without knowing an ID column?
It will only work if TblB has one or zero rows. Otherwise, you'll get an
error ("subquery returned more than one row").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor Raytsin
Igor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
-- SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> > Erlan, I think it has nothing to do with a linked servers it does a
> > creation on the local server and not a linked one. Or if I did not
> > understand you , can you please elaborate the explanation?
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Thursday, March 29, 2012

Can we handle ALL errors within a stored proceudre?

Sunday, March 25, 2012

Can views be used in query builder?

Thursday, March 22, 2012

Can variable be used in SQL UPDATE statement in VB.NET

Can u please check this queary?

Can u please check this queary?

Can u please check this queary?

can u ignore errors in a trigger

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Can torn page detection fire from existing corruption?

Sunday, March 11, 2012

Can SQLPutData be used against varchar(max)?

Can SQLPutData be used against varchar(max)?

Can sqlagent.out be cycled

Can sqlagent.out be cycled

Can sqlagent.out be cycled

Saturday, February 25, 2012

Can Someone PLEASE help! (PLEASE)

Parameterized Query '(@.WorkSheetID nvarchar(14),@.Origin nvarchar(5),@.DealerName nvarc' expects parameter @.Address, which was not supplied.

Friday, February 24, 2012

Can someone explain why this is not an error?

Can someone explain this behaviour?

Can someone explain this behaviour?

sql reports

Blog Archive

About Me