Sorry, I've lost my mind and can't remember how to get the least common denominator. I need this to determine how many days it takes for a set number of hours to be a int Day (e.g. 1/1/2001 12:00:00 AM will have 4 instances of an event that happens every 18 hour before it is 12:00:00 AM aga...more >>

Hi, I'm trying to insert a record into a particular position in a table. Is this possible?? It's so I can get records without using ORDER BY - as I want them in an order that is nothing to do with the data! I would put a "position" field in and order them by that, but I'm thinking if I can ins...more >>

You can duplicate this on your machine in about two seconds: To do so, fire up QA and paste the following code and run it. As is, it will choke. Then comment out the ORDER BY line and run it again and it does not choke. USE PUBS CREATE TABLE #TEMP_TABLE ( RowNumber [int] IDENTITY...more >>

Hi, I am currently in a project in which only views are used to retrieve = data from the database, no stored procedure. I have always used stored = procedure as a mean to insert, update, delete, and select data, but this = time there is no stored procedure.=20 In terms of the SQL itself, th...more >>

Please forgive the elementary question... In a fairly lengthy stored procedure, I check for data integrity and business rule compliance. If something does not check out, I "PRINT" an error message and RETURN an error code. When executing the SP through query analyzer, the error code I am ...more >>

In the code below, are the two last AND clauses in the Where clause unnecessary because of the way the joins are phrased? UPDATE SampleSourceProfiling SET CompanyLocationKey = cl.CompanyLocationKey FROM SampleSourceProfiling ssp INNER JOIN SampleSourceArchive ssa ON ssp.S...more >>

Hi, Is there anyway to keep track of generating the sql scripts of the database without using any other tools. need tha sql script to monitor this Aneesh ...more >>

Hi, I have a table of about 1M rows. I need to do something with each of the rows. The traditional "For/Each" type algorithm is what I would like to use. Currently what we are doing is creating a table variable that holds only three columns (the original table holds 6): recID (Identity),...more >>

I want to convert an dynamic sql statement to a stored procedure, but am having problems I need to create a stored procedure that could handle more than one "option". Here is the current sql --------------------------------------------------- sql = "select * from site_details WHERE addresses...more >>

Was trying to run a script as below declare @db sysname select @db='master' select * from @db..sysfiles How can i make it work ? ...more >>

if you query for * and get a rowcount of 50 (no nulls) then query for a distinct column and get a rowcount of 30 I would assume 20 have repetitions. select distinct column from table if you query for the same column and group by that column having a count (*) = 1 shouldn't that work th...more >>

Hi All, I am doing the following ...but it doesn't work unless I stop the query...and at that time there are a zillion grids :( Is there a way to clear the Grids and just refresh it? WHILE (1=1) BEGIN SELECT * from statusreport; --- DISPLAY NOW --- WAITFOR DELAY '0:00:01'; END ...more >>

Hi All, I am doing the following ...but it doesn't work unless I stop the query...and at that time there are a zillion grids :( Is there a way to clear the Grids and just refresh it? WHILE (1=1) BEGIN SELECT * from statusreport; --- DISPLAY NOW --- WAITFOR DELAY '0:00:01'; END ...more >>

Hi all, I have one table. Where : DonorID Int (Identity) Primary Key FirstName Varchar(25) LastName Varchar(25) .... .... I have One nonclustred index on Lastname another nonclustred index on (lastname, firstname). Suppose I Execute the Query: select * from TABLE where...more >>

Hello, I have this: create table test (num int) Now when I do some inserts how can I set that "num" gets a radom number for example from 0 to 5 ? Is this possible to do with sql server 2000 ? Thanks for any help in advance! Andreas ...more >>

Hi All Is there a script or way to schedule to a NEW file name each day a database? Something like the following? BackupSQL Database=Database1 Filename=GetDate() Regards, Mekim...more >>

Hello all, This is probably really simple for you guys. I have a table called orders. When I insert to the orders table, I would like to write the row to another table called user_action. I was trying to do this.... CREATE TRIGGER tr_UserAction_INSERT ON Orders FOR INSERT AS begin inser...more >>

Hi, I tried to look at the BOL about the Graphical Execution Plan. But it only talks about what each graphic means and how am I able to know the queries that I use is the most optimal??? By looking at the I/O cost? By looking at the CPU? By looking at the Cost? Are there any URLs t...more >>

Hello there I'm using Column_Updated() function on my trigger to know if there were any change The problem is that on one of my tables i have 116 fields and the number that should present this the last field is so huge that the column_updated() function return 0 what i need to do in this...more >>

would like to know which version of SQL rotates in the windows 2003 server, because mine didn't want to rotate he/she gave some incompatibility mistakes. ...more >>

Hi All, If I have two tables one with a foreign to the other with ON DELETE CASCADE set, when a record is deleted from the 'top' table, it then deletes the record from the table 'below'. Everything is fine. If I add a third table with a foreign key to the second table with ON DELETE NO ACTIO...more >>

I'm just looking for some good ideas on how to solve a T-SQL problem. I'm sure someone has solved a similar problem before... I have a table that looks like this (a simplified version): create table student_absence ( [student_id] varchar(8), [absence_date] smalldatetime ) I need to find...more >>

Hi! I work on converting Access db into SQL Server 2000 and I am having an issue with Identity columns. I wrote a proc to fill all table in SQL server from access and I don't know how to transfer IDENTITY columns. I have to set IDENTITY property of columns to OFF to insert the data from access...more >>

How can i save the contents of dbcc sqlperf(logspace) into a table ? I thought i could do insert table dbcc sqlperf(logspace) Doesnt work ...more >>

Project Background Information: Ok I have six textbox controls on my web-form that allows the user to enter the numbers 1-47 non-duplicates in each box ("meaning if the user enters 3 in one textbox they can't enter 3 in another"). However, I'm trying to figure-out the best way I could store user...more >>

select 10/100 returns 0 . I want it to return 0.1 ...more >>

I want to run a trace to write to a auditing table each time a table, view or stored procedure gets used. I will just run it a few hours at a time. How do I get started?...more >>

Is there a way to encrypt password column in a SQL database?...more >>

I'm very new to stored procedures! I have an existing stored procedure that is simply a SELECT statement. I am calling it from an asp.net web page. I need to modify it to accept a parameter. The parameter is to indicate what field should be used in the ORDER BY clause. Can someone provide a si...more >>

Is there anyway to search through all jobs, dts packages, etc to see if a stored procedure is being called from someplace?...more >>

I was curious about what happens with time stamps (not the T-SQL datatype, but rather saving absolute time of insert/modification) during the changeover from daylight savings to standard time. Since we "fall back," the one o'clock hour gets repeated, once under daylight savings and a second ti...more >>

Hello! I find strange behavior of CHECKSUM_AGG function. I bring below SQL Script that creates two equal tables and fills it with resembling, but different data (values in 3 columns are the same and in one column differs). CHECKSUM_AGG returns the same checksum. I tested it on SQL Enterprise 2000...more >>

Hi all, I have a query, for 25, 000 records, I need calculate each record with a different value retrieved from a function with parameters (@Site_From, @Currency_Year, @Currency_To) I'm trying to use a function like the following select myrecord * myfunction(a_site_Field, 2004, ...more >>

running dbcc sqlperf(logspace) results in as an example Database Name Log Size (MB) Log Space Used (%) Status master 3.3671875 31.714037 0 What i want to is translate the log space used from percent to actual size and then have another column ...more >>

Hi, This is driving me nuts, I have a table that stores notes regarding an operation in an IMAGE data type field in MS SQL Server 2000. I can read and write no problem using Access using the StrConv function and I can Update the field correctly in T-SQL using: DECLARE @ptrval varbinary(16...more >>

I have a script that I'm using to insert data from one table into another that keeps failing and I can't find the reason for it. The insert statement uses the following syntax. INSERT INTO dbo.databasename1 (column list...) SELECT (column list...) FROM dbo.databasename2 WHERE (......more >>

Hi Freinds, I have a maintanace plan which has a backup job for 15 database. The job starts fine but keeps saying executing for ever, so never the next schedule starts. What is the problem? did you ever see such a thing ? Thanks, Pat ...more >>

How can you open applications like word or notepad using SQL 2000. I know you can do this using links in Access, but I'm not sure what the terminology for this when associated with SQL 2000 is....more >>

Hi, We are in the process of doing a bulk conversion for a client and are experiencing some strange problems. We have two machines. Machine A is a single-processor P4 with about 1G of RAM, no RAID. Machine B is a quad-processor XEON 2.6GHz, with 2GB RAM and running RAID 5. We are using Ma...more >>

Is there any way to communicate to an email server within an SQL query? I want to select clients that meet a certain criteria, then send them a generic email. I can pull the email address from the same table the client information would be in. ...more >>

Hi NG, I'm looking for a SQLDMO JOB example. In BOL there is no example. Can help anybody? Regards Thomas ...more >>

Hi, How do I write this where clause: WHERE table1.ftest = 'S' or 'M'? Thanks ...more >>

Is there a difference between these two statements as far as perfromance? SELECT QuoteId, AppState, ProducerCode, CoId FROM Policy WITH (NOLOCK) SELECT QuoteId, AppState, ProducerCode, CoId FROM Policy(NOLOCK) ...more >>

I am trying to insert a record that will calculate one of the fields to be maximum field value + 1. I want to run this from one statement, not have to do a select to get the max number then do the insert. My insert statment is: ***************************************************************...more >>

Hi, I have SQL Server 2000 INSTALLED ON Windows 2003 Server. Accroding to Database structure they are Indexed with non Clustered. I got 5 Tables As Archieved and Five tables as Live I have more than 500,000 RECOrds in Each table. When i Ran this Stored procedure, it Gone in Infi...more >>

If I am using a float in sql, what c# data type corresponds to this? Double..? Decimal..? ...more >>

I'm using sql 2000. I ran an update statement in QA to update one record in a table. When the query finished, the message tab showed that 25000+ rows had been updated but when I looked at the table only the one row I intended was updated. Why would QA report that 25000+ rows where updated whe...more >>

Today I learned foreign keys, and cascading updates and deletes or no action. I create table A and B Foreign key between them on A.col1 (PK) and B.col2(FK) on update/delete cascade. One thing about this, I udpated the A.col1 and it was fun to see B.col2 changed it's value. 1 Concern, th...more >>

What is the best way to capture errors in a table involving alpha numerics? Example. The column is a string, but it should never have anything but numbers in it. Is there a way to check to make sure only number are there? I mean other than do a convert and wait for an error? Althoug even that m...more >>

Hello, I would like to know if there is any performance degradation by not using joins? What I mean is I have a coworker who prefers to use numerous AND instead of using JOIN. So, her T-SQL would look like this: Select oe.CustomerOrderNumber, cs.CustomerShipID From CustomerOrder oe, Custom...more >>

Hello... Is there a better way to do the following? this works...but it seems silly..double work :-) SELECT DATEDIFF(s, FieldTime, getdate()) AS no_of_days, FieldTime from Invoices WHERE DATEDIFF(s, FieldTime, getdate()) < 10 Why not like the following? SELECT DATEDIFF(d, FieldTim...more >>

I'm having a problem getting this script to work correctly. I am comparing two tables based on a column. If the id of both table matched and if another value that I am searching does not exist in table2 then insert a record for the id from table1. I'm not sure if I explained what I am doing th...more >>

I would like to run a nightly job that would build the "Create" script for a database and all its objects and save it in a .sql file. I have checked Online books but can not find a method. I am sure there is one! Thanks, Michael...more >>

I need to be able to obtain a resultset which comes from an EXEC for further manipulations . Here is what i want to do: i have to run an openquery inside an EXEC because the open query has a where clause that will change every time this routines is called. I know you can use "insert i...more >>

I am creating a job using TRANSACT-SQL that backs up a db as part of a maintenance part. Problem comes after building the command @s string that invokes the xp_sqlmaint stored procedure to do the backup. What is converted by cast(@plnid as varchar(50)) is junk. Whilst I could make xp_sqlmaint ...more >>

I am passing a parameter to a subreport and execute a stored proc in the subreport. When there is no data for the parameter I am passing, I get nothing in the main report for the subreport not even headings. I would like to show the headings of the subreport in the main report. Also, in the...more >>

im not that experienced at sql.. anyways this is the problem im facing .. Im indicating the primary keys in each table along with the table with a '-' ive 1 Main Table(Invoices - InvoiceGID) and 3 Other Tables Companys - CompanyGID; Branches - BranchGID; Orders - OrderGID The 3 Three T...more >>

I have a udf (user defined function). The udf currently takes one string as an argument. Problem: I would like to pass N strings to the udf. Question: How can I pass multiple strings to a function? I had planned to do it the same way we would pass multiple strings to a stored procedure - u...more >>

I'm trying to get either Used or Free RAM on a specified machine. Thing is - I need to do it only through T-SQL (or SQL-DMO) and with no ability to access the machine through the network (which rules out WMI) Is there an option to do this? I've been searching for a while and could not find a sol...more >>

This will be obvious to some - but not me I'm afraid... I am using an SQL data link from my ASP application to a SPROC - this all works fine on standard SELECT statements and JOIN in to a datagrid in my application. Rather than messing around with the code/server resource, I thought I would...more >>

Hi Newsgroup, I have the following scenario: I have database A with table a and I have database b. I created a stored procedure that transfers table a to database b (now called table b). I inserted two more columns in table b. Now I want to create a stored procedure that appends new data f...more >>