Hi, I'm looking for a script or suggestions on how I can go about sql scripting the dropping of any primary keys (be they constraints or indexes) and/or clustered indexes for a particular table. I would like to create a proc and just pass it a table name and will require this to work on SQL7 & ...more >>

Hi all, This is what I have in a table... Testcol1 Testcol2 -------- -------------------- 1 5 1 a 1 k 1 R 2 b 2 L 2 O 2 P 2 Z 3 c 3 G 3 O 3 R 3 Z 4 C 4 d...more >>

Is it just me, or are messages being deleted. I click on + and get Message is no longer available on the server ...more >>

Hello there I've tried to force Update and delete Cascade on one of the relationships. This is the error i got: Introducing FOREIGN KEY constraint 'FK_LongData_Companies' on table 'LongData' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or m...more >>

My question is how do I or where can I find information on how to create and distribute Desktop Distributable software. My company is just now getting into SQL and we have clients all types of Clients. Ones that have a full IT department and others that what the features of SQL but do not ...more >>

Why is it that there are times when during a dts extraction of sybase data from another server, it shows that it continually is loading records, millions and keeps going on and on ... until you cancel it? We read about this before in a technical article about flaws of the dts. Is there a...more >>

Hi all, for some reason the first post disappeared, sorry for reposting this I need to do two things one create a new column called [newERCPid = ] and two generate a unique random numeric number using ercpid = column [ ercpid =3D a identity column incremented by 1 ] ...more >>

Hi, Good Day! May I know what's wrong with the below sql query SELECT Amount=CASE WHEN=:sSelection='D' THEN 'D_Commission' CASE WHEN=:sSelection='R' THEN 'R_Commission' CASE WHEN=:sSelection='M' THEN 'M_Commission' CASE ELSE=:sSelection='G' THEN 'G_Commission' END, Error message said tha...more >>

I am using asp to connect to my MS SQL Server, there have some problems, could you please help me? 1. take about 5 seconds to make a connection with SQL server: In other projects, this would not happen, it just happened in my project? Why? 2. Select MAX() problem I need using selec...more >>

use Northwind select * from Categories limit 0,5 why the statement cann't be executed? ...more >>

Can anyone help? I have a the following sql statment: Select StoreID,TransactionDetailDate,RegisterID,TransactionNo,CashierNo, SKUUPC, Quantity,ExtendedSellingPrice, ExtendedOriginalPrice, (ABS(ExtendedOriginalPrice)-ABS(ExtendedSellingPrice))/ABS(ExtendedOriginalP rice)*100 Discount ...more >>

Hi all, I need to do two things one create a new column called [newERCPid = ] and two generate a unique random numeric number using ercpid = column [ ercpid =3D a identity column incremented by 1 ] I would later need to be able to reverse using the same algorithm = ...more >>

Hi, I'm constructing an SQL string which has a date comparison in the criteria: I.e DateStarted=08/08/2003 What is the best way to format the date string so that SQL interprets it correctly in British Time? (8th August 20003). Should I always enclose the date in hashes? #08/08/2003# a...more >>

Why did Microsoft pull out the functionality of srv_rpcdb from SQL Server 2000? That is something I need very badly right now and it's no longer supported.. ...more >>

I am attempting to create a Insert/Update/Delete trigger where it updates a table on a linked server. The trigger code below works in SQL Analyzer but when the application tries to do the insert in the first table I get the error message [Microsoft][ODBC SQL Server Driver][SQL Server] ...more >>

I have the following situation. A user can enter multiple ranges of numbers like so: Range1: 123-125 Range2: 126-130 Here is my database structure: ID First_Number Second_Number 111 123 125 112 126 130 I need a query that will ret...more >>

I am trying to import perflog output files into a database and then later analyze it. I am using bulk insert for the import. At the 12th file I got this error: Importing file server_20030924.tsv Server: Msg 4832, Level 16, State 1, Line 1 Bulk Insert: Unexpected end-of-file (EOF) encountered...more >>

I'd created a database in SQL_Latin1_General_CP1_CI_AS. Now I want to store data in another language. Therefore I created a table and the columns are set to "Chinese_Taiwan_Stroke_CI_AI" Collation. However, when I insert data to the table, the records are "???" Can anyone help me? Thanks ...more >>

Table Schema: CREATE TABLE PaperType PaperTypeID INT PRIMARY KEY Description VARCHAR(25) CREATE TABLE Scanned CustomerID INT PRIMARY KEY PaperTypeID INT PRIMARY KEY (FK Relation to PaperType.PaperTypeID) Ok, let's say that there's 3 rows in the PaperType table. I want to s...more >>

Hi All, I want to implement one stored procedure which will be used to search a database on many fields. My problem is that I can not figure out how to properly use wildcards on the in parameters. The way I do it now is by setting the default value of the parameters to '%' and then I use...more >>

I want an UPDATE statement which updates column 'amt' in table EMP, Here are the table schema and sample data TABLE EMP eid depcode amt 100 A 500 100 B 300 101 A 1000 101 B 1200 102 A 2000 102 B 2400 103 A 4000 103 B 4200 TABLE SP_EMP eid depcode amt 100 J 400 100 J 300 102 ...more >>

Hi Guys, Will we be able to execute a function using Linked server. Say I have a function in B_SERVER CREATE FUNCTION DummyInt1() RETURNS INT AS BEGIN RETURN(-1) END My requirement is that I need to execute this function from A_SERVER select B_SERVER.DBNAME.dbo.dummyint1() ...more >>

I have a table 'x' and I would like to ltrim rtrim (there are some spaces) all column without do this manually. Is there something similar sp_msforeachTable ? Any help appreciated. Regards -- == fabriZio ...more >>

Hi All I have 2 tables: Table1 PriKey FK Key Datetime Description 1 2 2248 2003-01-01 00:00:00 XYZ 2 2 2249 2003-01-03 00:00:00 X1Y1Z1 3 2 2257 2003-01-04 00:00:00 X2Y2Z2 4 6 2235 2003-01-01...more >>

Is there any way of putting the result set from an sp into a temporary table. What I need is something like insert into ##MyTable exec select * from MyStoredProcedure but this is not allowed. Any ideas? Thanks Justin Crossley ...more >>

When execute the following command in SQL Server 2000 query analyzer Command: EXEC xp_cmdshell 'dir c:\*.*' I get the following erorr: ODBC: Msg 0, Level 16, State 1 Cannot load the DLL xplog7O.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)....more >>

I know I can join two tables with INNER JOIN, getting all the rows that are in BOTH tables. How can I do a join and see what rows from table A DO NOT EXIST in table B? (I am trying to avoid the syntax "...where not exists(<inner join subquery>)" Thanks Sandro Frattura ...more >>

http://www.key-hole.com/EZSql/...more >>

Hello folks, I am trying to count the last Person seem by a customer against a max(VisitDate) and max(DataEntryDate) but am unable to do so because the Customer can see multiple providers and the grouping on max(Datetime) fields does not seem to do the trick. The results are as follo...more >>

I would like to use a returned columns data as my LIKE patten but find thats its not interpreting the wildcards etc. Any ideas how this could be done? ie @@CardNo numeric AS SELECT * FROM Cards WHERE CardNo = @@CardNo and @@CardNo LIKE Mask (Mask returns '____________99%') ...more >>

Hello and thanks in advance!!! I need to find the name(s) of any index(es) which are on a column by using the sysindexes table. For example (psuedo SQL here....) Select index_name from sysindexes where table_name = 'my_table' and column_name = 'column_1' etc... Thanks.... ...more >>

hi how to upload files to sql table during query , Stored Procedures or C# program ...more >>

Ok I am in over my head. I created a stored proceedure I have included below. I want to make one string to return to the caller. I thought this would do it. Select @lclStr=@lclStr+', '+@OrderString+','+ CAST(@Quantity as varchar(10)) No Dice. any suggestions this PRINT 'qty: ' + CAST(@Q...more >>

Hi All, Does anyone know how I can calculate the exact number of bytes that a particular field/record column is using in the database? I know with certain data types as byte/char/etc this is self explanatory, but I need to do this for data types such as image, text, etc and want to kno...more >>

Hi all SQL 2k dev ed (noob) I got the following 2 tables (DDL below) table 1 (@main) consists of 255 rows and table 2 (@pos) contains just 12 rows. I need to return the word from table 2 where the tbl2.MAX(minPos) <= tbl1 pos returned data: (@MAIN TBL) (@Pos Tbl) ID Pos MinPos Wordin...more >>

WHEN CHANGE is made to the CLSTATUS table, I need to GET the CONTACTID FROM the CHANGED RECORD. I need to RUN something like this QUERY: SELECT ACCOUNTID FROM CONTACT INNER JOIN CLSSTATUS ON CONTACT.CONTACTID = CLSSTATUS.CONTACTID WHERE CONTACT.CONTACTID = "???" Next, IF RESULT IS ABENJ0000...more >>

I have been build an ASP app, and during testing, I entered a lot of rows. I will now get rid of the test info, and want to start fresh. The primary key is an identity field. How can I reset it to start at a pre-defined number, such as 1 or 1000? ...more >>

Let's say I have a stored procedure called sp1, I'd like to accomplish the following inside another sp - this is pseudo-SQL ;) create procedure spTest as execute sp1 'someparamatervalue' union execute sp1 'anotherparametervalue' is this doable? obviously works without the union, I get ...more >>

The following sentence, when run in Query Anazlyzer on SQL 2000 DB returns the expected result. When run in a stored procedure on the same DB, it returns a NULL. Is there something different I need to be doing in a stored proc? Thanks for any ideas. declare @UserNumber int select @UserNu...more >>

Hello again. I posted a message back on 9/4 about a CASE statement and "greater than". Basically, my CASE statement works, except I have come across some values not computing correctly, they show <NULL>. Here is my case statement: CASE WHEN BoltHolesQty > 0 THEN '(' + convert(nvarchar, Bolt...more >>

Hi, I am getting the following error when I tried to execute. Server: Msg 241, Level 16, State 1, Line 1 Syntax error converting datetime from character string. This is the query I'm running: select bustimingsid, d.calc, viarouteid, busdeparttime, busarrivaltime, busduration, isnul...more >>

Hello, I have a simple update I need hep with. I have 2 tables Server and List Server has Server and Status List has Server and Status Server is the PK in both tables I need to update the Server table with status from the list table. Thanks!!!!!...more >>

Hello, I have two Tables: The Table City has the following structure: City Street Street_ID Berlin Berliner Str. 12345 Berlin Wilhelmstr. 23456 ..... Hamburg ..... ..... The Table Street has the following s...more >>

Is there any statistics available on the types of industries where SQL Server technology and personnel are in more demand? Thanks, Frank *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I know how to join two or more tables, and using inner join, get the values that appearin BOTH tables. How do I join two tables and get the values from table A that dont appear in table B? (Id rather not use "....where not exists..." if I dont have to) ...more >>

hello! create table #mytable (item int, data int) insert into #mytable values (1, 5) insert into #mytable values (1, 2) insert into #mytable values (1, 8) insert into #mytable values (1, 0) insert into #mytable values (1, 4) insert into #mytable values (2, 4) insert into #mytable values (2...more >>

How do I get THIS to work ? (Without writing 3 UPDATE's ?) UPDATE #twlShipLines SET #twlShipLines.PackStatus = CASE TRUE WHEN (B.Qty < QtyPickedLine AND B.Qty > 0) THEN 1 WHEN B.Qty = QtyPickedLine THEN 2 WHEN B.Qty > QtyPickedLine THEN 3 ELSE 0 END FROM #twlShipLines I...more >>

Assume the following data: REFNUM NAME AMOUNT 1 Joe 37 2 Tom 54 1 Bill 42 1 Sue 23 2 Sue 27 I want a sql statement to return the person who has the highest amount...more >>

Hi there. I am putting together a script that will either create a new dB table or if the table already exists, just add a new column to it. If I find that the table exists, how can I check if it already has the new column before I try and add it? Any help would be appreciated! Len...more >>

Hello, I have a database that has a very large transaction log. My hard drive is 30GB and the log took 29GB of it. I truncated the log space but I did not see the log space decreasing. I can't run a backup because I am out of space. I would like to clear out all the transaction log space ...more >>

I have a resultset that is produced from a SELECT statement that looks like this: 12 34 55 66 I need to convert this into a comma delimited string so that it looks like this: '12,34,55,66' Can this be done in T-SQL without using a cursor? If so, can anyone give me an idea how to g...more >>

Hello, If I have two tables, one a product table which has approximately 100,000 records, and the other a product being weighed table which at most will have 10 records based on whatever is placed on the scale, and I want to update a field (such as sales order) for all products currently being...more >>

as per subject if i call the following sp from an access adp file or from ado/ado.net it returns no records, if it is run from query analyzer it returns records ok. Any ideas why ? CREATE PROCEDURE dbo.GetSummaryReportMonth @eid int, @cMonth int AS set nocount on create table #MonthSum (...more >>

We have a table with some triggers on it. Those triggers insert/update/delete into another table. Which is also insert/update/deleted directly. The table is now experiencing slowdowns during inserts, I want to determine if the insert in question is via trigger or sql directly into the tabl...more >>

I have a vendor table and 4 product tables (one for each product category offered by the vendors). The vendors periodically log on and update their product listings in each of the tables through web forms. Each time they update a product, the lastupdated value for that product is updated. ...more >>

Hello, I have a database that has a very large transaction log. My hard drive is 30GB and the log took 29GB of it. I truncated the log space but I did not see the log space decreasing. I can't run a backup because I am out of space. I would like to clear out all the transaction log space ...more >>

Hello All, I am sure this question has been posted before, but I am relatively new to SQL. Can someone tell me what will happen if I install SQL 2000 EM Client on my machine while I already have SQL 7 EM Client installed. We have SQL Server 7 and 2000 servers, but I heard if you modify ...more >>

select case when ('09/26/2003 00:00:00' between '09-22- 2003 00:00:00' and '09-29-2003 23:59:59.000') then 1 else 0 end What should I expect as result? HINT: You do not always get what you expect!...more >>

I need to create SQL to get the sum of hours for a person since last sunday when entering timesheets. I need to know when a person exceeds 40 hours in a week so that the correct pay rate applies. I'm sure someone has done this sort of thing before. I have used code to get data for a date or be...more >>

Initially, I was surprised to see the right-most column show in the bottom two rows: -------------------------------------------------------------------------------- use tempdb create table L (L1 int) create table R (R1 int) insert L values (1) insert L values (2) insert L values (3) ...more >>

Hi SQL Gurus, I break a long stored proc into some smaller stored procs. Between stored procs I must pass result. The content of ##t must be visible in all stored procs. In this case, should I use ##temporary Table or Table Variable ? Create Proc Posting AS Create Table ##t (...) ...more >>

Hello, theres I have a question about get the Open, High, Low, Close, Volume for a period of time (say 1 , 5, 10, 15, 30 minute) result like follows ( 1 min) 2003-09-16 09:00:00 4401.20 4401.20 4377.93 4378.49 308074 2003-09-16 09:01:00 4379.05 4379.38 4377.12 4379.38 535052 ...more >>

Hi All, I have a reoccurring problem with the tempdb. Basically I have a loop of multiple DTS's that are very intensive in terms of importing millions of rows from csv's etc. I have developed a procedure that truncates and shrinks data/log files. I therefore run this after each database ...more >>

We have a long stored proc that is run every night. We get the following message when it was run yesterday: "Warning: Null value is eliminated by an aggregate or other SET operation" What statement causes this and should we be worried? Thanks, Suresh Kumar ...more >>

Hi All,=20 After migrating the entire database using attach and=20 detach method from one SQL 2000 server to another one with=20 better prerequisites. The jobs were transferred as well as everything else,=20 however they don=B4t succed anymore and generates a strange=20 error message dipla...more >>

I have a requirement to select a column from a table based on the column name passed as a parameter. Is this posible? Phil....more >>

hi, how can I find the characters that exist in a collation? please help me. best regards, ata giray ...more >>

It seems that the power function only captures precision to 1 decimal place. e.g. select power(8.0, -1.0) yields 0.1 not 0.125 as I would expect. Why is this the case? Is there a workaround? For your information, I am trying to write a simple function to round to a number of signifi...more >>

Everyone, I need to rank matching sales records. I mail customers info and try drive them to purchase a product. Since there are multiple mailings, it's possible that one sales can be attributed to many mailings. using a priority ranking system, I thought I could identify which mailing to t...more >>

My log file on my tempbd database keeps getting to large, what would be the best syntax to use to shrink or truncate the log. Thanks Phil...more >>