Hi, I am importing data from different tables the table don't have any the current days date on them or any date that I can use, what I want to do is assign a date to a field / column for each imported row so that I know what date the data was imported. I did try to use the below code but a...more >>

Hi, I want to insert multiple records (between 5000 -10000) from one table to another. I find however, it takes a bit of time due to the amount of records (this is a web application so time is of the essence). Is there a way to allow for batch inserts (ie, 10-20 at a time) which would speed...more >>

Hi - I am new to SPs (using SQL Server2000 - asp.net), and was looking for guidance of how to do the following (without looping through my ..net page continually sending sql statements to the server): I have a table of resource bookings - with a customerID, and a startdate and enddate fields. ...more >>

Hi, I am still new to SQL Server and I wanted to know if I can execute an insert or an update in the same procedure based on a condition ? Would the best way be to pass a parameter with a value 'INSERT' or 'UPDATE'? if anyone has a snippet they could could post? Moe CREATE PROCEDUR...more >>

Dear all, I guess someone who can deal with SQL Server can also deal with mySQL. My question is how do you insert records (in insert mode) on the end of all records (the last one on the grid). In SQL Server, when you press return on the last field of the record, it automatically jump to the n...more >>

Hi, I have used dbcc sqlperf(lrustats) in SQL Server 2000 to obtain cache info. In yukon, this option is no longer available. Instead, we are to use the Cache Manager object. is the info, provided by the Cache Manager Object, also available in a system view/table?. I know that the ...more >>

I have a query that I currently run in Query Analyzer, it pulls data from three other databases, but in one of the tables the date fields have the time stamps and I only want the date stamp, so what I did was as each table only holds data for the previous day I replaced the date field with:- D...more >>

I'm working on a project that gathers sales data from a table. I need to break the sales in to Price ranges. Currently I use Excel and loop thru each price range. I wanted to make a view that showed the same info. I got it to work for the most part, but if there were no sales for a particular ra...more >>

Hi I use a local temp in a stored proc. In Query Analyser (SQL Server 7 but i use 2k tools) i can see the temp table. Is there no way i can see the data in this table from another connection? I desparately need to see what is going on in this table so i can see how far the stored proc has exec...more >>

What is the correct format when entering a datetime input parameter? When I enter a datetime like 2004-07-07 12:15:00 I get a datetime field overflow. When I enter 20040707 12:15:00 I get Invalid character value for cast specification. Thanks....more >>

Hi, Currently our system only has FULL BACKUP maintenance plan which is scheduled to run on every saturaday at 7:00AM. This plan will backup 4 or 5 databases. the backup files are stored in D:\BACKUP\ for example (file format generated by maintenance plan): D:\BACKUP\Northwind_db_20...more >>

Hi all, Can any of you great guys tell me where can i get study materials for T-SQL. I mean free tutorials or book on the net. thanks Pradeep ...more >>

Hi, I have a view (vwReports) which is UNION ALL of 2 tables. Neither one of these two tables has NULL values in column a (char(1)). However, select distinct a from vwReports returns NULL, among other different values!?!? So, SELECT count(*) from vwReports where a is null returns 0, and S...more >>

Hi, I have a table with only one varchar column and no more than 5 rows. I need a comma separated list of value of each row like: row1, row2, row3, row4, row5 I know that I can do it by using cursors, But I am just wondering is there a shorter mothod for doing that? Something like SUM a...more >>

Hi When you go to the EM, select a db, and click on Restore Database, you get a list of all the backups that you have done. Now you can check the one that you want to restore. 1. How can I get that list from the Query Analyzer? (I need to display that list from a program) 2. How can I se...more >>

Hi, I want to create a central error in my store procedure, Do you think that using GOTO is appropriate solution ? Thank you, J ...more >>

Hi, if I do bulk insert some data to a table, (nonlog operation) then I do a differential backup after it, are those data backup? I am not sure because bulk insert is nonlog operation. ...more >>

I need to be able to specify a date range within a query dynamically. The problem is that the date starts on the last Saturday of one month goes goes until through the end of the last Friday of the following month. Does anyone have any ideas? Thanks for your help, Tim Stanley ...more >>

I need to be able to drop certain fields in a customer created index and then recreate the index with the original fields/properties after a batch operation. I'm currently grabbing all of the index info using ADO and OpenSchema...but I have not found a way to determine the following properties o...more >>

Hello, Is it possible ot have more than one auto increment column in a table? Thanks, ...more >>

DDLs create table test ( id2 int, ename varchar(3) ) insert test values(3,'B') insert test values(3,'B') insert test values(2,'A') insert test values(2,'A') insert test values(1,'C') After deleting duplicate rows i want 'Select * from test ' to display id2 ename 3 B 2 ...more >>

Hi Guys/Gals, Does anyone know if there is a datatype that is bigger than the char(8000) datatype? Cheers Josh ...more >>

Hi I'm trying to produce XML datasets to be used in Crystal Reports, but I cannot seem to view the XML output when produced. It just comes back with errors, when viewed in IE. The error is: **************************************************************************** ******** The X...more >>

Hi, I'm storing data that represents a time duration. I use the word duration, but it's a somewhat arbitrary distinction, because there isn't (or shouldn't be) really any difference between a time and a duration. Examples of my durations: - '1:01:30.5' (one hour, one minute, 30.5 seconds) ...more >>

In SQL 7, I had this command that worked fine: float(10,5) After upgrading to SQL 2K, it won't work. It wants me to have a single number there, such as float(25). What does float(10,5) in SQL 7 translate to in SQL 2000? SC ...more >>

Hi all, I need get the number of days of specific month. How I get this? I try it: SELECT MAX(DAY(GETDATE())) -- 7 (day 7) -- I need : 31 days in month 7 BUT don't work. THANKS!!! 80>) ...more >>

After a bulk insert command (e.g. INSERT...SELECT), I want to get the identity values of all the newly created rows. I know @@Identity can give me last inserted identity value. But, I need *ALL* of them. Is it possible? How to do this? Thanks, Guogang ...more >>

I am having trouble with the where clause of my select statement. I have an application with a datagrid filled with records from a table. On top, there is a filter, so region managers are to filter in only those account transfers that apply to their region (either moving into or out of the...more >>

Hi guys COUNT(column) counts the number of records where column<>NULL How can I get the number of records where column=null using COUNT? Thanks ...more >>

sql2k sp3 Im trying to figure out the rule of thumb for returning numbers to the right of the decimal (0's dont count) when doing caluculations. The first are plain example of what Im referring too. After that is what I can figure out. example /* create table #bla (c1 int, c2 int) ...more >>

I need to know how many transactions there is started on a day… Running: SQL 2000 Know how to see current transactions… but not this statistics Med venlig hilsen RAMBØLL Informatik A/S Klaus Ladegaard Jensen Konsulent...more >>

I used the upgrade ability in SQL 2000 to upgrade a server running SQL 7. All appeared to be fine - no error messages, no warnings, no nothing. However, in attempting to run a report (it's an ASP and Dotnet module that calls a stored procedure), it now gives me an error when I try to run the ...more >>

For SQLServer 2000. Given code such as the following : == create table test1 (pk int, dat image) insert test1 values (1, 0xFFFE7700680061007400) == Assume this sql code resides in a text file, and it is run by passing it to OSQL. Is there a limitation on the number of bytes/characters whi...more >>

Hi, Does anyone have an alogorithm to generate a password using an SP? Thanks. Khurram ...more >>

How I can add a Column in a chart with a store procedure ...more >>

when I right-click on Database1 --> property Size: 36187.63 MB Space available: 0.00MB So I just deleted a big index from a table that costs 1 Gb of disk space. after that, I shrinked Database with default option. (all-tasks --> shrink database --> ok) now I...more >>

Hi, I am attempting to write an SP for an UPDATE that can be called with any number of params from 1 to the max defined. My best effort was to build an UPDATE string based on the params received in the SP and then EXEC the string (didn't work very well btw) but I feel confident there must be a...more >>

Hi all: This is starting to drive me nuts. Here is what I have: three tables: users = user_id, user_name ' user information table issue_users = id, issue_id, user_id ' table relating users and issues issues = issue_id, etc.... (only concerned with id) ' table containing issue infor...more >>

Is it possible to have an entire result set returned using an output parameter? Everything I have tried is returning just the first record of the result set(or failing altogether), though if you run the sp without the ouput parameter it returns many results. Thank you for your thoughts. ...more >>

I have a table with sales information like this: Division Type ContractNo 1 Open 804844 1 Open 847943 1 Open 239048 1 Close 879874 1 Cancel 293482 2 Close 483402 2 Open ...more >>

Guru, I plan to write an osql script so that it will collect the following information of each column in a table. I shall print such information per each table. (Similar information can be obtained via the Enterprise manager , design table mode. ) -column name -data type, - length - allo...more >>

Hi all, Is there a way I can execute a stored procedure every 10 seconds, for example. I don't want to create a Database Job. Thanks in advance Ryan ...more >>

We have upgrade from SQL 7.0 to SQL 2000. After upgrading few reports are running very slow. All these query are dynamically generated using Exec. What could be the reason for such slowness? Is there any configuration setting that need to set? Lalit ...more >>

I have a 3 layer system, SQL Server 2000 database, COM+ dll's (VC++ 6) and ASP frontend, this system has been in production for quite some time using english, spanish and portuguese versions. Now I've been asked to test the system to function with Simplified chinese. The development of the system...more >>

Hi all, I have a maintenance plan created. How would I run this in query analyzer or gui when I want it to run on non schedule time? thanks Gerry ...more >>

Hi Experts, I am trying to make a stored procedure which will search candidates on their skill set. I need to return something like candidates with 100% match, 90% match 80% match. Is there a way by which I can directly put this logic in the Stored procedure rathar than calling the procedure m...more >>

I need to write a osql script for each table: (same information that I can see via the Enterprise manager - design table") - column name - data type - length - allow nulls - any primary key column - any id column - column option showing if replication is allowed or not. Question: What c...more >>

I have a situation where a column defined as a foreign key can be null. Should i even make it a foreign key or just leave the RI off? The column in question is MasterReceiptID. It could be that the system is configured such that master receipts are not created and therefore not row is ...more >>

can i write a sql statement and order based on the sequence of the OR e.g. select * from table where id = 1 or id = 4 or id = 3 result would be returned id value 1 value1 4 value4 3 value3 if another one like select * from table where id = 2 or id = 6 or id = ...more >>

This is an advanced SQL syntax question that I hope someone can answer. I have a select query which has an "IN" clause, and I would like the records to be sorted in the order that the values are listed in the IN. For example: SELECT columnA FROM table WHERE columnA IN ('G', 'Q', ...more >>

We have application that takes customer complaints. Later on, same application (different user - investigator) login and see the complaints assign to them and start resolve them. If the complaints were taken an hour ago and two hours later the investigator login can't see the complaints ...more >>

Gurus, I need to make sure each table (about 500) has one or more primary key. I plan to write an osql script. Question: What is the command / stored procedure that will tell such information in sql statement. Any help is much appreciated. Jenson ...more >>

I am trying to run the UPDATE STATISTICS on each table in the database with a cursor. I received the following error: Incorrect syntax near @tablename' Please help me resolve this error. Thank You, Don DECLARE @tablename VARCHAR (128) -- Declare cursor DECLARE tables CURSOR ...more >>

I am wanting to begin collecting performance data on my SQL server 2000 box. I have done a lot of reading on Performance Monitor and found the counters I want to track. Also, I have learned to run Performance Monitor on a remote machine. Has anyone had success with this? I am using my test s...more >>

Hi, I need to know CPU and IO load inside a stored procedure. How can I find the server load inside a stored procedure? Thanks, Alan ...more >>

Is there an easy way to create a datetime value from integer date parts? I have the integer values that represent the date (year, month, day) and would like to create a real datetime value. Currently I have been converting each to char and concatenating them together with dashes and then conver...more >>

--DDL: Select newid() [ID], 'Something1' Descr, Null Code Into #TempTbl Union Select newid(), 'Something1', Null Union Select newid(), 'Something3', Null select o1.[ID], IsNull(Max(Code), 0) + (Select Count(*) from #TempTbl o2 Where o2.[ID] <= o1.[ID]) GeneratedCode, o1.Descr fro...more >>

IN one database, I have views that depends on other views. If I am to re-create them in another database, I would need to create them in order. I couldn't just export all view to a script using enterprise manager and exepect the script to run properly. What's the best way to move the view...more >>

Hi, We are developing an application on .NET platform, VS 2003 in C#. In this application we have some wizard kind of new entity creation or update windows. We are using transaction to allow the user click cancel any time to roll back the changes s/he made during that session. As the RDBM...more >>

Dear All, Using the pubs database I have the following code: - declare @SQLStr as char(100) declare @WhereClause as char(100) set @WhereClause = ('where au_ID = ''409-56-7008''') set @SQLStr = 'Select au_lname from authors ' + @WhereClause EXEC (@SQLStr) What I would like to do would ...more >>

Hi all, I have two tables in a database, one of which I'm trying to update based on the (non) existence of data in the other. Table one is a staging table containing "real-time" data for a "To-Do" task list, where data is inserted, updated and deleted frequently. Table two contains identical ...more >>

I am trying to do a linkedserver from SQL server to Project database using the following command. But I am getting an error which is also listed below. Can anyone please verify if I am doing anything wrong. Thanks Vinod EXEC sp_addlinkedserver 'LINKEDSERVER', 'Microsoft OLEDB Provider for O...more >>

Hi, does anybody know how the dbcc sqlperf (logspace) calculate the %used? I have logfile with 'unlimited growth' and it is telling me 60% used? How is it possible, the disk is rather empty. Does it mean it takes in account only the allocated space? But the most importand question is: Is ther...more >>

Maybe a weird question, but if I have a table T1, with an update trigger and during that trigger I call a Stored_procedure SP1, which does another update to table T1, does the update trigger fire again? If not is this adjustable with a system variable or just always only fires once ? I am v...more >>

Hello everybody! It looks like I can not move an axis title in an owc11 chart control by using "ChartSpace1.Charts(0).Axes(0).Title.Position = chTitlePositionLeft" so the text is somehow positioned over the series that noone can read it. Does anyone know a workaroud for this? Thank you! Wate...more >>

Hi all, is it possible to use the virtual tabels "inserted" and "deleted" inside of a stored procedure (lets assume, that the sp is call from within a trigger)? Thanks in advance Achim...more >>

I keep getting the "Ambiguous Column Name" error. The problem with this is that I use this exact same script (Script below question) on 10 other DB's and it works fine. What could be the problem? Any assistance welcome. "set @SQLStr = 'Update '+@DatabaseName+'.dbo.Employees set GangID...more >>

I need to get a rowset query result into a only one row with n fields. EX: select myfield1, myfield2 from myTable returns: myfield1 myfield2 0000001 0000002 0000003 0000004 0000005 0000006 i need to get: myfield1 myfield2 myfield3 myfield4 myfield5 myfield6 0000001 0000...more >>

I have 2 tables CONTACT1 and CONTSUPP. CONTACT1 stores customer information and CONTSUPP stores all kinds of details of customers in CONTACT1. Both the tables are related with the ACCOUNTNO column The requirement is that I want to select all the customers, with their email addresses The Re...more >>