I have a small question. I am recently started using 2005 and I want to assign results of following query in to a variable of any type. select top 1 * from dbo.authors for xml auto Please let me know how do I achieve this. Kishor...more >>

I've posted this to the .NET groups... but I thought maybe some people in the SQL Server forums could also give me some feedback. Thanks : ) Hi all, We're looking at upgrading our VS.NET Crystal Reporting solution with something more powerful and user friendly. The reporting solutio...more >>

All, Any one knows how to find out the table creation script - means,you have a table name and you are required to see the texts which created the table.For example, MySQL SHOW CREATE TABLE <table_name> It returns the script. ORACLE SELECT dbms_metadata.get_data('TABLE', <'table_name...more >>

All, An up gradation is going on in my project. Here, I have some tables in a project specific database and the same (w.r.t name) set of tables (but the modified one) in a different database. We are using sqlserver2000. Now I need to find out what are modifications- means the name & featur...more >>

Hi, I have the following statement: select dbo.udfCustomerName(CustomerID) , Count(*) from Orders group by CustomerID If Orders table has 1 million rows, does udfCustomerName get executed 1 million times? Or does SQL server do the grouping in CustomerID first then call udf ...more >>

Recently i saw sysprocess due to high CPU usage n i find out that some process are shoing login_time which is 2-3 hours old. Does this mean that the process is runnig for 2-3 hours ? ...more >>

Hi All, I need to write a udf which has to get the numerator and denominator from a decimal value. I have to get the nearest values of numerator and denominator. For Ex: ..66 = 2/3 ..33 = 1/3 ..5 = 1/2 Could anyone please help me in writing the UDF. Thanks in Advance ad...more >>

G'day Mates, Is there anyway I can do an UNPIVOT in Sql Server 2000? Regards, Nitish ...more >>

Hello all, I try to execute the code below. Whatever I try I get the error: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dc_temp_text'. This also happens when I put the owner and the database name in from of the table name. Can anybody tell me what it is th...more >>

Here is what I want... is there ANY way to do it? (without using temp tables) ------------------------------------------------------------------------- Create Function dbo.MakeThisWork(@TableName varchar(300)) Returns varchar(8000) AS Begin Declare @S varchar(8000) Select @S = (Select Descrip...more >>

Can any one point me in the right direction on how to insert an image into SQL server using ASP ?I was thinking a long the lines of using the ADODB.Stream as I cant use any upload activeX components or any other third party components, but cant seem to find the info I'm looking for. I don't want...more >>

Hi all, I know it is possible to send an attachment thru XP_Sendmail. The file name will change everday due to the date in the file name. so I created a variable to hold the file location and name. when I run the below I get an error: Msg 18025, Level 16, State 1, Line 0 xp_sendmail: failed...more >>

View or Select Query which will work faster while querying from multiple tables Regards Imtiaz ...more >>

Hello, I have a column (AccountNo per the below create script) that displays properly with leading zeroes to fill a 22-character column while in SQL. However, when I use a DTS export to a standard text file with no transformation, it left-justifies. Could someone pls advise how I can get it ...more >>

Hi, How do I (programatically) find the last date/time the database was used. Even better would also be to find how much time the database has/is being used. Some background: I'd like to write a custom tool to clean out some of our development database servers. These constantly get fille...more >>

Ladies / Gentlemen I have several different programs that Insert/Delete/Update data in a MS SQL2000 database. I currently have an audit trigger on each table. I have added a field to the audit file named 'UpdatingProgram'. Now I need to know if I can fill in the Name o...more >>

If I execute one SP within a parent SP and want to trap and return an error code from the child SP, can I just use RETURN @@ERROR in the child SP rather than capturing @@ERROR in a local variable first? Or does the successful completion of the RETURN statement set @@ERROR back to 0 by the time it...more >>

I am trying to trucnate all user tables from the db. The code does not seem to be working though. Any idea? Thanks for your help. declare @sql varchar(500) set @sql = 'select ''TRUNCATE TABLE dbo.''+ name+ char(13) from sysobjects where type=''U'' AND objectproperty(id,''ismss...more >>

Hi I was wondering is there any way to determine Terminal Session Name in stored procedure/functon. Please don’t confuse my question with result of HOST_NAME(), I’m not looking for Terminal _SERVER_ name i’m looking for a name of current TS session. I would highly appreciate respo...more >>

create table my_table ( col1 char10, col2 char10, col3 char 10) select col1, count(*) from my_table group by col1 --Now, from this group by results, I want to eliminate all the records where col2 is Null. Can I accomplished this in the same group by query above? Remember, I want to el...more >>

create table my_table ( col1 char10, col2 char10, col3 char 10) select col1, count(*) from my_table group by col1 --Now, from this group by results, I want to eliminate all the records where col2 is Null. Can I accomplished this in the same group by query above? Remember, I want to el...more >>

re: Warning: Null value is eliminated by an aggregate or other SET operation I have read Microsoft's article 317312 and think I understand what is happening and why I can ignore it. My understanding is that because the subquery is returning a null value (because the subquery returns no recor...more >>

Hi, I have info about my customers and when they place their orders. I am trying to get a report that will tell me the sum of their orders for each month for the last 24 months. The problem I'm having is that certain customers don't have order in every month so I'm only able to query on what...more >>

I am creating separate report for persons participating in atheltic events in Chicago this summer. Each event has its table & 1 or more columns including the participant's ID. The report needs to turn the col names and data for a given record into a vertical 2 column list. I want a gener...more >>

Hello there I've created store procedure that returns list of all views on the system according to depedent tree. (if view2 is based on view1, view1 will be before view2) Now i want to get the script of each view, so i can build script of all views by the dependent tree. The option of...more >>

The secrets of making customers HAPPY to pay HIGHER prices. Raising your price will have a HUGE impact on your bottom line. 12 "invisible" ways to raise prices. Do you offer coupons? Rebates? Discount promotions? "Tweak" what you offer - so you can price higher. "Packaging" changes that increas...more >>

Hello What is the best way to large text fields in a table, where the text fields are upto 5000 characters ? Regards John ...more >>

The statement below is actually what I am trying to accomplish: I have two tables usr_inventtable, inventtrans i need the calculated value of the fields (costamountsettled/qtysettled) in the inventtrans table put into the field lastcostprice in the usr_inventtable, but i need only the l...more >>

I have a sproc that does this (among other things): insert #tbl exec linked.db.dbo.sp @beginDate, @endDate, @salesperson, @customer I'm not sure what's changed, but SQL Server started complaining that [OLE/DB provider returned message: New transaction cannot enlist in the specified transa...more >>

Hello there I'm imporing data from csv files. one of the field with data lile '20060105' should be converted to date. Vbscript isdate function cannot convert it to date, but sql server can convert it. Does someone knows whay? ...more >>

this is my code and i keep getting an error ( ',' on line 4) and I cannot figure out what is wrong with this code. Sql 2000 server INSERT INTO USR_COSTPRICE (ITEMID,DATEPHYSICAL,QTY,DATEFINANCIAL,QTYSETTLED,COSTAMOUNTSETTLED, VALUEOPEN,DATAAREAID) SELECT ITEMID,DATEPHYSICAL,QTY,DATE...more >>

Hi, I have the following proc which works with EXEC and I now woauld like to replace EXEC with sp_executesql and fell to do so. The sp compiles and in run-time I get the bellow error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. ALTER PROCEDURE RawChannelRate...more >>

Could anyone help me with an update query? I need to populate Table 1 (bookings) with data from Table 3 (defaults), via a joining field in Table 2 (enquiries). All fields are of type INT, using SQL Server 2000. Table 1 (bookings): id, t_val, q_val Table 2 (enquiries): id, booking_id, enq...more >>

Im noticing that whenever I use a temp table, whether its a regular temp table or a table variable, my record set in ASP comes up with this message: ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. Is there a work around for this...I really need to be...more >>

Hi Guys, I've got an issue adding a parameter to a function, when I do using it an alter statement SQL Server 2000 complains that incorrect number of parameters are specified. I remember reading a while back that you can't use alter like this if the function returns a table, since then I've a...more >>

Hello all. I'm not highly experienced with SQL programming. I must export to contents of a table to a formated text file which is then send to the customer. Is there a possibility to do this from within a sql statement? TIA Ronald ...more >>

We've found something that seems odd for which we'd like an explanation. We have a table defined as follows: create table tester (col1 int, col2 char(2), col3 int) Our script executes something like this: begin alter table tester add id int not null constraint df_test default 0 a...more >>

I wrote a program with 546 lines. When the code was run to line 406, it took 2 minutes and 9 seconds. When it ran all the way through, I stopped it after 9 minutes. I ran just the section from line 406 to the end and it ran in 20 seconds. For some reason, when all of the code is run togeth...more >>

SQL Server 2K SP1. I'm trying to put the results of a Table function into a Table variable for use in a Select. My code is: DECLARE @LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo)) SET @LoanTable = UDF_GetLoansInDeal (1, 8) SELECT LoanNo FROM UV_Tran016 AS t16 INNER JOI...more >>

I have very big tables. And I need to some computation for each row. Which one is the best/fast way to implement it? 1. bcp the tables to text file. C++ code parse the csv file row by row. Write results to text files. Then bulk insert back to Sql server. 2. C++ code use oledb/ado to get t...more >>

hi. i don't have much experience with locking using lock hints so wondered if someone could help me with usage of ROWLOCK. i am writing a number of procs which will perform validation on data prior to performing updates. i need read consistency for the duration of these procs whilst guaranteei...more >>

Hi all A collegeue recently found an item on http://www.sql-server-performance.com/stored_procedures.asp (do a search on the page to "fully qualified name") In theory this does make sense, and I can understand what the item is getting at. I work with a team of 20 developers, and we had n...more >>

Hi All I want to blow up the database. Say I have 10 records in a particular table and I want to increase it to 100 records for performance testing purposes. Is there a way to do it in SQL Server? Thank you Viccu....more >>

Hi, I have a content based site with over a 1000 downloadable word docs which are currently stored on a server filesystem. however these are split in muliple folder paths for different clients and i am trying to find a more efficient way of doing this. i have been looking into storing the fi...more >>

Hi, I have a job scheduled, it executes a stored procedure which in turn contain other stored procedures and some sql that gets executed. The job takes approx 5hours to run, but when I run the stored procedure in sql query analyser it takes only 20 minutes, why the extra time when run in a jo...more >>

Hi, I'm trying to troubleshoot a slow-running query which is part of a stored procedure. When I run the query as it is - it takes 53 seconds to run. ------------------------------------- Example original query: SELECT DISTINCT SP.SP_ID FROM StandardProject SP INNER JOIN Classes cls ON SP...more >>

Hi, i have a code that uses makewebtask and it works fine in development enviropment (SQL SERVER 8.00.760(SP3)), when that code is uploaded to production it doesn't works fine(the tags where data must be inserted, appears in blank, no data is inserted in template file). production server ...more >>

Hi, There is a select query which returns a count as below: select @count = count(typecode) from tbl_Types where typecode = @typecode depending on the count, this is what needs to be done... If there is a count of 1 to 26 then I would like to use A-Z and then anything from 26 o...more >>

Please enjoy and feel free to adjust to your specific needs! If you find a way to optimize the function further, just reply with your code fix. CREATE FUNCTION dbo.fnSeqNumbers ( @LowLimit INT, @HighLimit INT ) RETURNS @Values TABLE ( Value INT ) AS BEGIN DECLARE @T...more >>

Hi, I have a table with (sports) results, containing an userID, EventId and a Time for each result recorded. How do I select a list ordered from fastest to slowest containing the fastest time for each userID recorded ? The challenge here, that I don't understand how to do is to not get a li...more >>

This is my sample text file Axxxxx, Lxxx 11-02-48 58 S1111-02 1-Feb-06 d22 mfc 203.3 12455 124-42-1245 f 401 Taaddd Dr xxxxx Cxxxxx xx 8xxxx XX Bxxxxx, Lxxx 11-03-48 58 S1111-03 2-Feb-06 d22 mfc 203.3 12455 ...more >>

Hi, I am writing a sql query for something like a data entry address search. One of the fields is Postcode. What I am trying to get working is the spaces that the users can enter between the postcodes. For example, a postcode in London is like: TW12 YRZ Notice that there is a space. But anoth...more >>

First of all, I sincerely apologise for posting this question again - I normally wouldn't post a duplicate, but I submitted this question yesterday and only got one reply; however, I desperately, desperately need to find a solution for this as I've been banging my head against a brick wall for s...more >>