|
Hi I have this code:
Declare @v_update_sql varchar(max)<br />
<br />
Declare @d_Date datetime <br />
Declare @v_target_table_name varchar(max)<br />
Set @v_target_table_name = 'IM_DOC_TRANSFORM'<br />
<br />
Set @d_Date = '2009-03-02 12:46:00.000'<br />
Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'+@d_Date+')'<br />
Exec(@v_update_sql)<br />
<br />
The error that is arising is that of cannot convert to datetime from string.the variable @d_Date is a valid date but still it cannot convert it. That statement works went not in a string variable but in this format it doesn't. Can someone help me please? Thanks
|
|
|
|
|
Hi jonhbt,
Try this, you seem to be missing some qoutation makers on the CONVERT in your dynamic SQL which are necessary for a STRING to be concatenated.
Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'''+@d_Date+''')'
I hope this helps,
Kevin
|
|
|
|
|
Hi Kevin,
Thanks for your help but the same happened. This is the error that I am Having.
Conversion failed when converting datetime from character string.
|
|
|
|
|
OK maybe this will help instead...
Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = CAST('''||@d_Date||''' AS DATETIME)'
If it does not work, do a "SELECT @v_update_sql" immediately after the "SET @v_update_sql..." statement and see what the dynamic SQL looks like with the date string included. You might see an obvious syntax error then perhaps.
Cheers,
Kevin
|
|
|
|
|
thanks again Kevin but its not working either. Creating the select after is not showing the results since its stating an error near the |. thanks agin for your time
|
|
|
|
|
Hi again,
One thing I just noticed is you need a SPACE before the "WHERE clause" in the SQL example code you posted above. At the moment it dynamic SQL command looks like this.
Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORMWhere Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME)
Try adding a space as follows. I have also replaced the "||" with "+" if you would prefer this method of concatenation.
declare @v_update_sql varchar(1000)
declare @d_date char(23)
select @d_date = '2009-03-02 12:46:00.000'
declare @sql varchar(1000)
declare @v_target_table_name varchar(100)
Set @v_target_table_name = 'IM_DOC_TRANSFORM'
Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + ' Where Last_Documented_Date_And_Time = CAST('''+@d_Date+''' AS DATETIME)'
select @v_update_sql
execute (@v_update_sql)
The dynamic SQL should then look like this...
Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORM Where Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME)
See if that works for you.
Cheers,
Kevin
|
|
|
|
|
thanks for your help it worked
|
|
|
|
|
DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<root>
<customer customerid="VINET" contactname="Paul Henriot">
<order customerid="VINET" employeeid="5">
OrderDate="1996-07-04T00:00:00">
<orderdetail orderid="10248" productid="11" quantity="12" />
<orderdetail orderid="10248" productid="42" quantity="10" />
</order>
</customer>
<customer customerid="LILAS" contactname="Carlos Gonzlez">
<order customerid="LILAS" employeeid="3">
OrderDate="1996-08-16T00:00:00">
<orderdetail orderid="10283" productid="72" quantity="3" /> </order>
</customer>
</root>
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<root xmlns:xyz="run:MyNamespace" />'
print @hdoc
when you will execute any sp_xml_preparedocument stored procedure it will go on increas the valu of output variable by 2 with every execution?
Why the output variable in sp_xml_preparedocument is increasing its value by 2 every time when you execute procedure sp_xml_preparedocument?
|
|
|
|
|
Hi,
I am currently using MS SQL SERVER 2008, i am trying to connection to our DB server but i am getting this error.
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Please help me. Thanks 
-Cyrilla
|
|
|
|
|
Well, the error says it all. You are using windows authentication on sql sever and trying to connect from an untrusted domain.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks captain obvious, I don't think I would have know that without your astute insight.
|
|
|
|
|
ap_wooka wrote: Thanks captain obvious, I don't think I would have know that without your astute insight.
1. The error message was so obvious it deserved some sarcasm. It states quite clearly what the problem is,BOL tells you how to fix it, so why post here? Simple laziness probably.
2. What was your point in replying to a post over a year old that wasn't even yours in the first place - unless you are one of those morons who has multiple logins so you can answer your own questions and look good.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
So we found this thread with Google's search engine, Problem is that we do not know what "BOL" is, so please answer the question if you know the solution, or post a link where we can find it.
Thanks - we all appreciate it.
|
|
|
|
|
Hi!
Did you find a solution for this?
|
|
|
|
|
We know there's conditional branching facilities in T-SQL and PL-SQL etc. Is there a similar IF clause available for use in flat SQL statements?
I can't use stored procs for certain restriction reasons, and my scenario is this.
I have one table, with columns [a, b, c, d, e, f]
I will pick rows, where column c = column d.
However, if column d is blank (null), then column e will be used for the comparison, if e is blank (null), then column f will be used for the comparison.
This is easy as pie in T-SQL, PL-SQL etc. Is there a way to do this in flat SQL statements?
Thanks.
|
|
|
|
|
Hi,
you can use AND and OR operators and effectively build a WHERE clause that calculates this (pseudo-code):
c=d OR (d=null AND (c=e OR (e=null AND c=f)))
|
|
|
|
|
Thank you! That was fast.
|
|
|
|
|
You're welcome.
|
|
|
|
|
Yo can also use the CASE statement or (depending on exactly what you want) possibly the COALESCE statement in SQL Server.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Sorry if this sounds dumb, but I thought CASE decision making was only available on the procedure/function side, and not in flat SQL?
If not, can you point me to some sample flat SQL code with CASE usage demonstrated? That'll be very helpful.
|
|
|
|
|
You can use it anywhere, I'm not sure what you mean by Flat SQL - T-SQL is T-SQL, which runs trhe same in stored procs as out of them - the only execeptions are triggers and functions which are more restrictive.
select NonEoM = case <br />
when [Receive side type] = 'floating' and [Receive Convention] = 'EOM' then 0 <br />
when [Pay side type] = 'floating' and [Pay Convention] = 'EOM' then 0 <br />
else 1 end<br />
from table1
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hey everyone,
Just wondering if there's a better way to do this:
SELECT
SUM(LOOOOOOOOONG EXPRESSION) AS EX1,
SUM(ANOTHER LONG EXPRESSION) AS EX12,
SUM(LOOOOOOOOONG EXPRESSION)+SUM(ANOTHER LONG EXPRESSION) AS TOTAL
FROM SOME_TABLE
Can't it just be
SELECT
SUM(LOOOOOOOOONG EXPRESSION) AS EX1,
SUM(ANOTHER LONG EXPRESSION) AS EX12,
EX1+EX2 AS TOTAL
FROM SOME_TABLE
Anyway to make this possible??
Thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
|
Giorgi Dalakishvili wrote: CTE?
Ok, I googled for Oracle CTE and I think you're talking about some Toolbox package, can you tell me what is it and why do you think it should help me??
Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I believe that Common Table Expressions are limited to SQL Server 2005 and higher. I don't know of any way to use the column-alias within the select statement itself.
I are troll
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
