|
can u post the answer..,
am also working with complex quries on database.,
Your Answer may be helpful for me.
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
USE [rhythmhouseDynamic]
GO
/****** Object: View [dbo].[vw_SearchStationery] Script Date: 04/17/2009 16:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_SearchStationery]
AS
SELECT dbo.ProductLine.prodlineID as 'ProductLineId', dbo.ProductLine.prodlineName as 'ProductLineName', dbo.ProductMaster.ProductId, dbo.ProductMaster.ProductTitle,
dbo.ProductMaster.ProductDescription, dbo.ProductMaster.ProductShortdescription, dbo.ProductMaster.ProductSynopsis
,ProductAttributeValues_Brand.ComputedAttributeValue AS [Brand]
----Add New Attributes Here
FROM dbo.ProductLine INNER JOIN dbo.ProductMaster
ON dbo.ProductLine.prodlineID = dbo.ProductMaster.ProductlineID
LEFT OUTER JOIN dbo.ProductAttributeValues AS ProductAttributeValues_Brand
ON dbo.ProductMaster.ProductId = ProductAttributeValues_Brand.ProductId
----Add New Tables And Join Conditions Here
WHERE (dbo.ProductLine.prodlineID = 7)
AND (ProductAttributeValues_Brand.attId = 265 or ProductAttributeValues_Brand.attId is null)
----Add AttributeId Where Condition Here
.............................................
Bye Best Of Luck Keep In Touch
|
|
|
|
|
I would try a natural join first since the fields joining the tables have the same names.
|
|
|
|
|
Hi all,
I have one problem with Identity Specification in sql server 2008,
i.e. Once I am having records in one table and after that if I want to change identity from Yes to No / No to Yes. Unable to do it
Am i missing something or is it bug ??
Any solution for that?
Thanks in advance...
Krishnraj
|
|
|
|
|
SyncFolder is a very easy to use but powerful solution for the synchronization of two or more folders/computers. It can backup all your important data into a zip-archive and has many settings to customize the synchronization... SyncFolder
|
|
|
|
|
Pay for advertising you tight ass.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hello...
I'm using T-SQL. What I'm trying to do is (within a function) read a value from a specified dynamic column, and return that value for usage elsewhere. The following lines would do the trick:
[code]
SET @SqlString = 'SELECT @Value = [' +@ColumnName +'] FROM SettingTable WHERE ID = 50'
exec sp_executeSql @SqlString,N'@Value int OUTPUT', @Value OUTPUT
[/code]
However, I can't use that within a function (Only functions and extended stored procedures can be executed from within a function), even though sp_help tells me that sp_executeSql is an extended stored procedure. Is there some way I should be calling the executesql proc that would allow it? Or is there another way I should be generating my dynamic query that would be allowed within a function, and which will allow me to get an output value from the query?
|
|
|
|
|
If I have the following code
<br />
Exec('Select Count(*) From table')<br />
how can I put the value of the execution into an integer type variable
|
|
|
|
|
|
Hi,
If you just what to store the COUNT value in a variable (@a) you could do this.
declare @a int
select @a = (SELECT COUNT(*) FROM table)
Now select the count value from the variable...
select @a
|
|
|
|
|
Hii,
I have a table in a database called dictionary.
I have four columns namely id, word , keyword and meaning.
I have indexed word, keyword and meaning column in a catalog for full text searching of any word.
I want full text searching of any word in these two column and return meaning of that word found in word and keyword column.
I searched the table for the word using following query :
string wordSearched=textBox1.Text;
"SELECT meaning FROM dictionary WHERE CONTAINS(word, keyword, '+"wordSearched"+')";
But am unable to get the full text search result.
Am i wrong in my syntax for querying full text search.
or may be can anyone help it out giving the proper syntax to query word and keyword column.
Thanks in Advance!
Aspiring Techie,
Vishnu Nath
|
|
|
|
|
Try This
Replace Contains , with instr
Use Instr function
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Hello,
You can use asterisk.
SELECT meaning FROM dictionary WHERE CONTAINS(*, '+"wordSearched"+')
or
SELECT meaning FROM dictionary WHERE CONTAINS(word, '+"wordSearched"+') or CONTAINS(keyword, '+"wordSearched"+')
or containstable sample
select id, f.[rank], [Name], [Description] from eshop_product inner join containstable(eshop_product,[Description], 'ISABOUT(bike weight(.9), pedals weight(.3) ) ' ) f on eshop_product.id = f.[KEY]
or use karsa full text search manager
I hope this help you
Michal
|
|
|
|
|
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
|
|
|
|