Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done ? Here's my stored procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddPerson] @Data AS XML AS INSERT INTO Persons (name,image_binary) SELECT rowWals.value('./@Name', 'varchar(64)') AS [Nam...
This is a follow-up to my previous question (Thanks for the answer, BTW!) If I have two loops: while @@fetch_status=0 begin set y=y+1 set x=0 while @@fetch_status=0 begin x=y+1 if y = 5 'exit the second do while and back to the first do while --> y=y+1 end end ...how can I exit from the...
In SQL declare @keyword varchar(100) declare @val varchar(100) set @keyword = '%asp.net%' set @val = '%c#%' select f_Resume_uid, f_Resume_Title, f_Resume,f_Filename from tbl_resume where (f_Resume like + @keyword + ) AND (f_Resume like + @val +) --select f_Resume_uid, f_Resume_Title, f_Resume,f_Filename from tbl_resume where (f_Resume...
Hello everyone, I am learning and using SQL Server 2008 new Merge statement, merge statement will compare/operate source table and destination table row by row ("operate" I mean operations performed for when matched or not-matched conditions). My question is whether the whole merge process will be one transaction or each row comparison/...
I am looking into a TSQL command called "xp_cmdshell" to use to monitor a change to a the SLK (SharePoint Learning Kit) database and then execute a batch or PowerShell script that will trigger some events that I need. (It is bad practice to modify SharePoint's database directly, so I will be using its API) I have been reading on variou...
A search of SO yields many results describing how to select random rows of data from a database table. My requirement is a bit different, though, in that I'd like to select individual columns from across random rows in the most efficient/random/interesting way possible. To better illustrate: I have a large Customers table, and from th...
I have a schema that includes tables like the following (pseudo schema): TABLE ItemCollection { ItemCollectionId ...etc... } TABLE Item { ItemId, ItemCollectionId, ContributorId } I need to aggregate the number of distinct contributors per ItemCollectionId. This is possible with a query like: SELECT ItemCollectionI...
a) A SQL statement is a single SQL command (for example, SELECT * FROM table1 or SET NOCOUNT ON). A batch on the other hand, is a number of SQL statements sent to the server for execution as a whole unit. The statements in the batch are compiled into a single execution plan. Batches are separated by the GO command So the only diffe...
a) I found two definitions of schema: FIRST - A set of information that describes a table is known as a schema, and schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any). SECOND - A database schema is a way to logically group objects ...
Hi Based on following TableA Data -------- Dummy1 Dummy2 Dummy3 . . DummyN is there a way to generate sequence number while selecting rows from the table. something like select sequence() as ID,* from Data that will give ID Data --------- 1 Dummy1 2 Dummy2 3 Dummy3 .... N DummyN Thanks. ...
I've banging my head for hours, it seems simple enough, but here goes: I'd like to create a view using multiple select statements that outputs a Single record-set Example: CREATE VIEW dbo.TestDB AS SELECT X AS 'First' FROM The_Table WHERE The_Value = 'y' SELECT X AS 'Second' FROM The_Table WHERE The_Value =...
Sorry - my question title is probably as inept at my attempt to do this. I have the following (well, similar) in a table in a CMS pageID key value 201 title Page 201's title 201 description This is 201 201 author Dave 301 title Page 301's title 301 descriptio...
I have datetime value as 2010-04-07 09:00:00.000 2010-04-07 14:30:00.000 how to convert the 14:30 to 2:30 pm ...
I have three tables, Customers, Sales and Products. Sales links a CustomerID with a ProductID and has a SalesPrice. select Products.Category, AVG(SalePrice) from Sales inner join Products on Products.ProductID = Sales.ProductID group by Products.Category This lets me see the average price for all sales by category. However, I only ...
Basically, what I want to do is join 4 tables together and return 1 row for each boat. Table Layouts [Boats] id, date, section, raft [Photos] id, boatid, pthurl, purl [River_Company] id, sort, company, company_short [River_Section] id, section Its very simple as far as structure, however, I've having the time of my life try...
i have created views for my project now i want to optimize them for the speed purpose...how can i identify that the view can be optimize? is index usefull for this.... let's say the following example... SELECT dbo.vw_WebInventory.skref AS SaleID, dbo.vw_WebInventory.lot_number AS LotNumber, dbo.vw_WebInventory.Description, ...
I have a TimeSheet table as: CREATE TABLE TimeSheet ( timeSheetID employeeID setDate timeIn outToLunch returnFromLunch timeOut ); Employee will set his/her time sheet daily, i want to ensure that he/she doesn't cheat. What should i do? Should i create a column that gets date/time of the system when in...
If I have an UDF that returns a table, with thousands of rows, but I just want a particular row from that rowset, will SQL Server be able to handle this effciently? DECLARE @pID int; --... SELECT * FROM dbo.MyTableUDF(@pID) WHERE SomeColumn BETWEEN 1 AND 2 OR SomeOtherColumn LIKE 'this_pattern&' To what extent is the query optimizer c...
Hi All, In my application many I time we use MAX(). How can I write a common function where I can pass table name and column name and get MAX(). I mean single function for any table/field. Thanks, Tanmay. ...
I have a large table consisting of over 60 millions records and I would like to add 2 new columns for data migration purposes. There are indexes on the table and some of them are large. So, by me adding the 2 new columns to the table, will I run the risk of slowing down the database whilst it attempts to add them and maybe time-out? O...