Hi folks, Currently I have data file and transaction log file on the same drive. I want to move the transaction log file to a different drive without any outage to the database. What is the best way to do this. Thanks in advance. ----- *** Sent via Developersdex http://www.developersde...more >>

How would I group records by date from a table where each record has a smalldatetime field called CreationDateTime that is auto-populated using GetDate(). I need to produce a trends graph and want display the number of records created per day. Obviously, my CreationDateTime field stored bot...more >>

Hello everyone, I need to create a table to hold 70,000+ zip code records. The supplier of the database provided the following schema. ZIPCode char(5) ZIPType char(1) CityName varchar(64) CityType char(1) StateName varchar(64) StateAbbr char(2) AreaCode char(3) Latitude decimal(9, 6) ...more >>

Four table: Securities, Positions, ExchangeList and ExchangeListMember Securities has an SecurityID field and the exchange it is traded on. Positions has a SecurityID and an Account. ExchangeList is used to define the exchanges that an account is permitted to own securities on. The ExchangeLis...more >>

I would like to do a query that would list all of the user tables and give me counts for columns and rows. Someone probably has already made that query. Thanks! -John...more >>

Hi all i know that this has been asked many times.. but i can't find a solution i am trying to write a stored proc that will allow me to search my data, but of the 3 search fields the user may only pick on, how would i create such a sql statement. apologies for no sample data.. woudl...more >>

I have so-so SQL skills...just starting learning stored procedures a little over a month ago. Now, I am required to make updates to some existing stored procedures that are WAY over my head. Does anyone have any advice on how I can learn what is going on in a quick, easy way? I've found seve...more >>

I have a table similar to the below: Field1 Field2 EXE1 Age EXE2 Child Under 10 EXE3 Not a citizen WEB1 Cares for a child under 10 WEB2 Be a US Citizen and I am getting the totals for th...more >>

I am trying to extract the value of the lastLogon attribute for users in Active Directory by using LDAP ADSI. Setup is SQL Server 2000 with a linked server named ADSI, using Query Anaylyzer. I run the query SELECT [name], lastlogon FROM OPENQUERY( ADSI, 'SELECT name, samAccountName...more >>

I followed the tutorial instructions came with SQL 2005 enterprised edition about the Notification Services, but it failed at the final step when I started the Notification Services. The Generator and Distributor won't start (enable pending, then timeout). I did everything as the database sysa...more >>

Patients may be assigned up to 4 diagnoses for an encounter. I need to select patients (by their unique ID) when any one of the 4 diagnoses falls within a range. So I wrote the SELECT as below. Now I am curious if there is a more concise syntax using IN. Here's what I did: SELECT PatUniqueI...more >>

hello, im having a problem with my sql statement. i have an insert statement that inserts new "comments" into my database, but every so often, the data that needs to be inserted is the same "comment" that is already in the database. this produces an error. i want the statement to add a new...more >>

Hello. I have a SQL table (TableIn) that has 6 columns (col1 thru col6). I want to insert col1 thru col3 from TableIn into TableOut1. I want to insert col4 thru col6 from TableIn into TableOut2. I only want to use a single "SELECT" statement. E.G. Insert Into TableOut1(Col1, Col2, Col3), Ta...more >>

1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ? For example: The column dTime is of DateTime datatype and has the value of 12:26:05 AM. When I do the following sql: select CONVERT( CHAR(10), dTime, 8) from myTbl it returns 00:26:13 instead of 12:26:13 AM 2. How can...more >>

We are currently running SQL 2000 and have a data warehouse and heavily use DTS. We are planning on moving to 2005 sometime mid next year. To prepare I am interested in learning as much as I can, but because it is such a change I'm kind of at a loss as to where to start. Obvioulsy I will ne...more >>

Hello, The following question applies to SQL Server 2000 SP3 and SQL Server 2005: We are in the process of investigating international support for our application and part of that will likely require changing all of the char/varchar columns in our database to nchar/nvarchar. From what I...more >>

I want to select 3 columns so that the result set is sorted descending on each column. But this doesn't seem to get me the results that I want: SELECT col1, col2, col3 FROM tbl1 ORDER BY col1, col2, col3 DESC And this does not work (syntax error): ....ORDER BY col1 DESC, col2 DESC, col...more >>

This is so basic it is confusing the heck out of me... select * from tableA where tableA.custnum not in (select tableB.custnum from tableB) Basically, I am looking for all records from tableAwhere there are no matches to the same type column in tableB. I was able to finally get the...more >>

Hello... Is there a way that I can script out multiple jobs at once? I know in Enterprise Manager it allows you to select multiple jobs but when you go to script the jobs out.... it only scripts out the first job in the list. I have about 70 jobs I need to script out. I am using SQL Serv...more >>

Is it possible to create a stoted procedure that has an optional parameter ?...more >>

HI, Is there any way or any undocumented SPs out there that I can use to read the transaction log backup files to find out what LSN the file starts with? Thanks, Td...more >>

I am trying to use xp_sendmail to send the results of a stored procedure via email. I am having a problem when one of my sp parameters contains a '\' For instance, this works: EXEC master.dbo.xp_sendmail @recipients = 'joe@xyzcompany.com', @query = 'usp_specproc BTUSE, aguirrema, aguirr...more >>

I am using ADO to connect to SQL 2k db. We have converted it to merge replication and now I am not getting identity field value after I use the Update method. For example: rsPayInfo.AddNew rsPayInfo("CheckAmt") = 100.00 rsPayInfo.Update lngCheckID = rsPayInfo("CheckID") The variable lng...more >>

Hello, I am using Host_Name in some of my SQL queries to find the user that is running the report. The report works find when developing but when publiched to the report server it fails. I am sure it has somthing to do with the report being run remotly on the web server. How would I ...more >>

After our Daily backups are complete, I would like Sql to Open a Zip File. Is their a command to do this within SQL? -- Eddie...more >>

Hi there, I am having problems generating the Geomean using "EXP(AVG(LOG()))" in SQL Server 2005. I have looked at the common causes of this, namely LOG(0) returning NULL and hence causing the problem, but I have queried this out of the test data and all values being used are greater than 0...more >>

We have extensive table moves from version to version, and I was wondering if there is a way that I can generate the table scripts through query analyzer. Currently I do so by going through EM, and picking each table.. Looked in Help but could not find anything about this.. Thank you for s...more >>

We have a Peoplesoft application built over MS server 2000. The peoplesoft application has created a table in ms server via its application designer where the field types, sizes, indexes are automatically generated from the data dictionary and search keys etc. What I would like to know is h...more >>

Good morning, We are porting a legacy VB6 user interface application that stores data in binary text files to SQL Server 2000 and C#. The VB6 user interface hooks into backend C/C++ dll's to pass VB6 "Type" data into the C code that writes to the binary files. We have to preserve this s...more >>

I want to get the max lenght of a column on a table in anther database. I have a linked server pointing to that database. INFORMATION_SCHEMA will not work as it reports only works on the current database. How can I do this. Thanks kevin...more >>

Hi, Has anyone out there worked out a way to determine object creation order using sysdepends. I'm working on a customized database scripting machine and would like to automatically script objects in order of dependence. Thanks, Robert. ...more >>

Hi, I am trying to convert a nvarchar type date of 01112005 to a datetime type, I have tired various cast and convert statements and the results come back but do not let me run any analysis, something as simple as ordering the dates would be a start but I cant seem to get that to work. Any...more >>

QtyPcntCompleted should never exceed 100 but I have no control over what is in PostedQty or ForecastQty. So if the calculation exceeds 100 I set it to 100. What I'm wondering is can the nested CASE be replaced by a function? --------------- QtyPcntCompleted = CASE WHEN ForecastQty = 0 T...more >>

Am coding a calculation to determine a percentage of late shipments. Sample code below. SQL 2000 DECLARE @PercentLate AmountType, @LateShipments int, @TotalShipments int SET @LateShipments = 11 SET @TotalShipments = 21 SET @PercentLate = 100 * @LateShipments / @TotalShipm...more >>

I'm in a situation where a user should be able to choose what data to retrieve from a table. The criteria is not constant, sometime it is included, other times not. The problem I'm facing is how to create a dynamic WHERE clause. I would prefere to avoid client side embedded SQL. My first id...more >>

Is there any simple way in EM to put an audit on a field to see when and what is updating it ? Thanks ...more >>

Hi everybody, I need to validate the constraint that it is necessary to use both transport and dialog security in order to establish a communication between 2 SQL server machines via Service Broker. Thanks....more >>

I am trying to query a table (a.tbllastcarddata) with fields named providerid (varchar(32)), siteid (int) and visits_1_year (int). This table has an entry for each patient and includes how many visits the patient had in the first year. I need to add up the number of visits that each provider ...more >>

Dear gurus, I can't work out with a problem using our dearest statement, i.e BCP. Well, I'm trying to import a .DAT file to a table from a DOS session and I obtain the following error: C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01 -Uusrdts -Pdts Where the ...more >>

Hi, This is a problem which is causing a few difficulties. We have a time recording system which just tracks what people are working on. The system basically keeps tracking of how much time is spent by a person on a certain type of work. The problem is when we group the data by different...more >>

Hi, i have created a stored procedure and its running fine. i am sure that sproc doesn't have any problem. after that i integrate this sproc to an application. whenever i execute sproc from query analyzer. its running fine and doing expected job. but whenever i am calling it through applicat...more >>

Hi, Is there any method by which when we create view depended on a table, it should not make changes on base table. ...more >>

Hi, I am using SQL SERVER 2000, I want to know if it is possible to put a case statement in update, something like this. update table1 case when <some condition> then set table1.column1 = 'XXXX' else set table1.column2 = "YYYY" end ...more >>

Hi I am having major problems with a particular sproc giving me primary key violations. A VERY simplified version of my sproc is as follows..... ---------------------------------------------------------------- CREATE PROCEDURE dbo.MY_PROC @field1 integer AS SET NOCOUNT ON IF ...more >>