Hey, all. I am trying to figure out if there's a way to identify a "version" of a SP that gets called the most. I have an SP that gets called with a bunch of different parameters. I know that the SP is causing some issues and trying to pin point the problem. Besides capturing calls to the SP and manually sifting through the results, is ...
I have more than one sql query(25 to be exact) and want to return the results as one row of results. for example... --Get the Barcodes that have a EnvPrint Record in the Database select count(distinct jtbarcode) as CountEP from jobtracker with(nolock) where jtprocess = 'EnvPrint' and jtbarcode in(Select lookupcode from data with(nolo...
In my stored procedure I have to pass a table and column name which may change everytime. So I build command and execute it. I want the output in another variable @curr_id. This store procedure will be called by second stored procedure by using @curr_id as input. My problem is populating the @curr_id vriable. It is returned as zero. If ...
Is there a way to identify column-level dependencies within and between databases? I'd like to generate a report of all columns in a database that are unused by anything (views, procs, UDFs). e.g. In database 'DB1', there is a table with a column called 'col1'. How do I determine if 'col1' is being used by procs, views or UDFs in eit...
Here is my table Events Start : Datetime End : Datetime I'm trying to make sure that a new Event does not overlap any previously entered events. I'll admit my SQL knowledge is novice at best. The following is a select statement that gets me close but I can't figure out how to turn it into a constraint (would I use check?) SELECT e....
The setup is like this: Col1 Col2 12345 12 12348 14 20145 16 00541 Null 51234 22 Simplified, obviously. What I want to do is update Col2 wherever it's Null by setting it to the Col2 value for whatever has the closest value in Col1 (so in this example, row four should have Col2 set to 12). This is...
I'm creating a CLR user defined function in Sql Server 2005 to do some cleaning in a lot of database tables. The task is to remove almost all tags except links ('a' tags and their 'href' attributes). So I divided the problem in two stages. 1. creating a user defined sql server function, and 2. creating a sql server script to do the upda...
Hi, I am trying to make a report which will show products bought by customers. I have Products, TransDetails, TransHeaders, Customers tables and i have to pick all products and for each product i have to show sales for each customer. if customer has not bought any particular product it should still be visible on report with 0 sales. user...
I have a a SQL 2000 DTS package that is scheduled to run from a SQL 2005 SQL Agent job. In this DTS there is a ActiveX step that has the following VBScript to call a webservice. Dim http: set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") http.setProxy 2, "http://123.45.67.89:8080" http.open "GET", "http://mywebservices.com/MyWebMet...
I have to write a sql script for comparing the difference's with the indexes of table of SQL Server. ...
I have two tables, one parent "Point" and one child "PointValue", connected by a single foreign key "PointID", making a one-to-many relation in SQL Server 2005. I have a LINQ query: var points = from p in ContextDB.Points //join v in ContextDB.PointValues on p.PointID equals v.PointID w...
SELECT ROUND(123.4567, 2) gives me 123.4600. But I need 123.46. Data type of field is money. SOLUTION <%#DataBinder.Eval(Container.DataItem, "FieldName","{0:0.00}")%> ...
I have table with two columns: ItemMaster (Item INT, Quantity INT) If an item is already there, then I should update the Quantity. Otherwise, I have to insert a Record in this table. Is this possible without Loop? I'm using SQL Server 2005. ...
Hi I am trying to commit the following value ("HatMark K.K.") to the database but keep getting an exception. command.CommandText = "UPDATE tb_Entries Name = @Name WHERE ID = @ID"; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@ID", bc.ID); command.Parameters.AddWithValue("@Name", bc.Name); Exception: ...
Is it possible to get input from a user for a variable? For an example: How do i get the firstname of a user to use it in my script to select it from my employee table. I now how i declare a variable but not how to get it typed in by the user of the script ...
Ok, so I have a query: select distinct(a) from mytable where b in (0,3) What is going to be faster, the above or select distinct(a) from mytable where b = 0 or b = 3 Is there a general rule? Thanks ...
I'm writing a stored procedure and I want to return 0 records when something fails. I can't seem to figure out how to just return 0 rows? I've used SELECT NULL but this returns 1 row with a NULL in row 1 col 1. I have also tried not specifying any SELECT statements in my error code path but when testing the value of @@ROWCOUNT after the ...
I was asked in the interview tell me the different ways of exporting database from one sql server to another, I knew only about creating a .bak file and then restoring it to another sql server which I told them. However, they asked me about a single SQL INSERT command which will perform this task. I have googled it and can not find it....
We have a website under development that maintains a database. This website is replacing classic ASP pages and FoxPro applications along with new development written in ASP.NET 3.5 (mostly VB, a little C# and some AJAX). However, there are other systems that update our database. Because of those systems that do things like updating st...
I have a table that currently is a long list of devices and information about when they were sold. I need to take the table which would look something like this: Item | Time Sold -------------------- A 05/2010 B 04/2010 C 03/2010 A 04/2010 A 05/2010 And then have a table with the item a...