this is what my query looks like: select top 5 cast(content_html as xml) as [prodxml], prodxml.value('data(ClassTemplate[1]', 'nvarchar(max) ') as prod2 from content where end_date >= getdate() and folder_id != 71682 and i keep getting: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "prodxml" or the user-defined f...
I have a stored procedure that is called by data collection system. The procedure has many parameters that for the collected data. I'm using the INFORMATION_SCHEMA to pull the parameter list into a temp table SELECT substring(PARAMETER_NAME , 2 , len(PARAMETER_NAME ) - 1) 'SpParam', PARAMETER_NAME, DATA_TYPE INTO #tempParam...
SQL Server 2005 supports CLR so it means we can use CLR in backend so how to do that, I have some function in c# which do some complex manipulation with date-time variable now I want to use those functions in SP. First of all IS IT POSSIBLE TO DO THIS. ...
By some setting as using IDENTITY_INSERT we have duplicate entry in identity column, What is the best method of removing duplicate entry. I have a table Details With column DetailID | FKey | Col1 | Col2 | Col3 | Col4 DetailID is "Identity" and FKey is foreign key with another table. Now we have already 240000 record. Some one used "I...
Here is my table. I need a query which returns the shift id for a specified time How can I get the value? shift_ID shift_From shift_To 1 2010-09-21 10:00:00.000 2010-09-21 18:10:00.000 2 2010-09-21 20:00:00.000 2010-09-21 05:00:00.000 Suppose I am giving 02:00:00 as input I need to get the shift ...
I want to create a computed column in SQL SERVER and set the formula to this ([Category] + '.aspx?ID=' + [Post_ID]) Not working though......what am i missing? Category and Post_ID are current columns in the table ...
Hi, I have had several contexts where table names or schemas were not hard-coded, but rather configured by the administrator, or, worse, generated from user input. Since cases were easy (schemas and table names in plain English, without numbers nor symbols), it was easy to avoid SQL Injection by just forbid any character outside A-Z an...
I have an id field (int type) and varchar field. I have to concatenate both columns and store the result to another column with data type nvarchar; Is this possible? ...
I have two databases: DBTarget and DBTest. I've run several custom scripts on DBTest, so now DBTest and DBTarget should be identical in every way (Tables, Values in tables, Columns, SPROCS, etc.) Question: Is there an easy way to compare these two databases? The only strategy I can think of is: USE [DBTarget]; SELECT * FROM tblTableN...
I have a two column view Product Id Tag ---------------------- 1 Leather 1 Watch 2 Red 2 Necklace 2 Pearl I'm trying to get all possible combinations of tags for a product as such: 1 Leather 1 Leather,Watch 2 Pearl 2 Pearl,Necklace 2 ...
i have a query like this SELECT TOP 10 * FROM NEWS WHERE newsid > AAA ORDER BY newsid desc; this query is very slow for some values of AAA for example it is fast for 1,000,000 and 1,400,000 but it is slow for 1,355,316 I am confused!!! ...
I want to create a two stored procedures that will remove records older than N days from a stgging database. Both stored proc will call in a package to cleanup data older than N days. Stored proc A will remove records from table A.Fullimport & stored proc B will remove records from table B.weeklyimport. The same stored proc will remov...
Having the table, defined by script [1], I execute scripts in 2 windows of SSMS --1) first in first SSMS window set transaction isolation level READ UNCOMMITTED; begin transaction; update aaa set Name ='bbb' where id=1; -- results in "(1 row(s) affected)" --rollback and after 1) --2)after launching 1) select * from aaa --de...
Hello, I've got a table in database that looks like this: CustomerId Attribute Value ================================ 30 Name John 30 Surname Smith 30 Phone 555123456 to make some use of the data I need to make it look sth. like this: CustomerId Name Surname Phone =...
Hi all, Have created a stored procedure which is utilised for monitoring purposes of a website. On first run the procedure takes over a minute to execute and if run shortly after this it takes only a few seconds to run. The problem is that the script is scheduled to run at ten minute intervals and each time it runs, it takes over a mi...
How can i convert the datetime format below 2010-10-25 11:13:36.700 into 25-Oct-2010 or 2010-10-25 00:00:00.000 ...
I am trying to retrieve a count of orders for each order type (OrderTypeID) and a count of orders for each order type of distinct products (productID) eg: OrderID, OrderTypeID, ProductID 1, 1, 1 2, 1, 6 3, 2, 6 4, 1, 1 5, 2, 6 6, 2, 6 7, 2, 6 8, 3, 1 Result: OrderTypeID, Count1, Count2 1, 3, 2 2, 4, 1 3, 1, 1 I am currently retriev...
i am trying to do as below its giving syntax error please help DELETE FROM table WHERE col1 = 2 AND EXISTS ( SELECT COUNT(*) FROM table WHERE col1 = 3 ) > 2 ; i need to do a delete only if the cout is greater than 2 ...
Hi I am trying to create a report and am looking to pull out some information from a table 'contact'. I am trying to pull out the 'advisor' relating to the contact and a count of contacts who have that advisor based on a set of criteria. This is fine and works, however, I want to pull out a third column which is also a 'Count' based on ...
Most SQL developers know and use Narayana Vyas Kondreddi's sp_generate_inserts from http://vyaskn.tripod.com/code/generate_inserts.txt Is there something similar for Oracle? ...