Hello, I just implemented log shipping on SQL Server 2000 Developer edition and I need to keep all modifications to DTS packages on Primery Server to be replicated on Standby Server. Could you please give me a hint how to do that? Thanks, GB ...more >>

The SQL 2005 Books Online says the following: ------- The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server. Important: This feature will be removed in a future version of Microso...more >>

Hi All, If this: DECLARE @LastRunDate DateTime SET DATEFORMAT DMY SELECT @LastRunDate = LastRunDate FROM TaskLog WHERE TaskName = 'Update All Products Data' SELECT DATEDIFF(hh,@LastRunDate,GETDATE()) Returns 0 Then how comes the code after the IF statement in this code, runs? (I woul...more >>

What's the best SQL statement to use to detect if a Key Exists in a particular table? I had been using SQLDMO within a VB app to access possible keys in the table and then find if one matches what I'm looking for: For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableName) ...more >>

Hi All, I'm trying to put an if statement in my SP, which incidently is comparing two dates, thats by the by, because I cant even get this simple if statement to run with out error: IF (9 > 8) THEN PRINT '9 is greater than 8' END IF Also tried: IF (9 > 8) PRINT '9 is greater than...more >>

Hi, I'm using MS SQL Server 2000 I have 2 Northwind databases on the same server (local) NwindA and NwindB In NwindA I have vNewView How do I transfer this vNewView from A to B (just the view not the data) ( I have hundreds of view that I need to transfer) Thanks, Oded Dror ...more >>

I have a series of tables already containing data. The first table is a list of employees. The next is a list of team names. There is a Primary Key for each employee which relates to a foreign key in the team names. The employee table would have pkid FirstName LastName ...more >>

I looked all over the internet and can't find a straight answer, so hopefully someone here will know the answer for a fact. Question... Can the rank values of multiple containstable queries in different columns be compared to each other in order to achive a cross column ranking? If not,...more >>

Hi Can anybody tell me how to take database back up using query analyzer in sql server 2000? i dont want in enterprise manager i wnat to know how to do it with query analyzer. can you tell me y i'm getting this error when i'm trying to open enterprise manager "SQLDMO has not been registe...more >>

Just wondering if anyone has tried the following KB article with SQL 2005? http://support.microsoft.com/?id=889696 Doing a diff between the sproc in 2000 and 2005 there are no changes except in the comments, so I'm pretty sure the above KB's modified code can apply to 2005; however, in 2005...more >>

Hi folks, I have a huge problem calling a stored proc with a datetime parameter = using ole db (CDynamicParameterAccessor). I used the sample from MSDN = (HOWTO: Execute Stored Procedure Using CDynamicParmeterAccesor) as a = starting point and modified the sp "sp_getStudent" so that it gets = ...more >>

i need to back track data should there be changes for the last 30 days. help! thank you!...more >>

I'd like to build a stored procedure that would allocate a single value among records. If there was a rounded remainder I would like to randomly pick one of the records to receive the "extra" rounded amount. I'd like to pass my procedure 2 variables: @AccountType and @Amount. The amount allocate...more >>

Hi, I'm putting together a system and one of the requirements is to have a searchable CV function. I've got all the code to load the files on to the image fields, I've indexed and got it mostly working. Before I go to far down the road what is your opinion on having txt files instead ...more >>

Hello there I wanted to know if anybody has an example of an activex task in a dts that will query a soap service ? I never queries a soap service from vbscript so I guess i need to see an example to start ? please Thanks ...more >>

Hi, I'm trying to retrieve data of previous day from my 'sales' table. Sales table has: order#, order date, cust#, sales, qty I have a metadates table: one colummn date my script is select sum(sales), sum(qty) from sales where order_date=(select top 1 date from metadates order by...more >>

Hi, I think to get some suggestion regarding how can implement a system that flexible enough to switch between MSSQL 2005 and XML data storage for windows application. What I think to do is a create a abstract dataprovider class for my SqlDataProvider and XmlDataProvider. The insid...more >>

I have created a link between server A and server B (sever A contains the link info) I set the default database of the user on server B to be the database I want to link to on server B. Inside Enterprise Manager I can view the tables on server B via the link icon on server A. When I star...more >>

My company as a sql server 2000 transaction log file that is too big. I need to truncate the size of the log file. I am relatively new to a small company that does not have an assigned DBA since there are only 5 programmers. I have been working with sql server 2000 for about 6 months. I hav...more >>

All: I have a table called Users and there is a column called ParentID that either contains 0 or another valid userID. This is what I want to do: When I have a userID, I want to retrieve all UserIDs until ParentID for a searched user is 0. Script to simulate the environment: create ta...more >>

What's the best SQL statement to use to detect if a Key Exists in a particular table? ...more >>

Greetings.... Question for you regarding the Case statement.. The more and more I get into SQL2005, the more i love it. Kudos to those who put it together. Question regarding the Case Statement and using it. Now, in the past, i've used the case statement to convert the IIF statements ...more >>

I have a single row in a table that represents all purchases at a gas station for an entire day. That record has a link to another table that has details about each transaction. The transaction records have transaction line items, the line items have individual properties. I have cascading del...more >>

Hello. I'm really quite inexperienced with SQL but do have a basi understaning of most general steps. Unfortunately I need to merge tw tables together and it is beyond my skill currently. Let me describe the 2 tables first and then my ending need. Table and Table 2 have about 5000 and 600...more >>

Hi all. Why doesn't this query return number of affected rows? UPDATE tblOrderPackages SET packageIsSend = 1 WHERE id = 134 SELECT @@ROWCOUNT AS rowsAffected Thanks, Sharon. ...more >>

Sort of new to this, hoping for some help here. I've got a table which you insert Scores and comments for Different Issues. Each User may only input ONE score, but many comments per issue. I'm attempting to run a trigger which looks at the distinct users (in the table) and then will either al...more >>

I've got a table which you insert Scores and comments for Different Issues. I'm attempting to run a trigger which looks at the distinct users (in the table) and then will either allow the comment AND score to be Inserted if the User hasn't put in a score for that issue yet. OR if the User alr...more >>

Hi all, I have a two text columns in my table with more than 100,000 rows. I want to create a third text column with the data from text column 1 + text column 2. Is there an easy way to concatinate two text fields? Thanks Raju ...more >>

http://www.thedailywtf.com/ Look at "Hacking the jProject" -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a murderer. Kill many, and you're a conqueror. Kill them all and you're a god." -- Dave Mustane ...more >>

Hi all ! I have a stored procedure quering a simple query against some tables in a linked server (on same server). It was running fine with no problem but after we installed SP 4, I often get a timeout when calling the procedure. If I run exactly the same query direct in Query Analyzer i...more >>

I am getting this error on my insert statement, do I need to do something for my datetime fields? Here is the statement INSERT INTO tblCalendar ( adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType, ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel, DateRe...more >>

I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag field in a table called SVCrossSales for each set of records keyed by the CSNum field. The first record in each group would get a CSFlg value of 'N' and the subsequent records would get a 'Y'. Here's a sample of what the C...more >>

Hello, I am using a DTS package to putt information from a Windows 2003 Active Directory into SQL 2000. The DTS package connects to AD, which is set up as a linked server, and reads several attributes of user accounts, including the accountExpires and lastLogonTimestamp columns. The a...more >>

I have a table with 5 million rows. This table has 25 columns. I am writing a view which use 7 of its columns in JOINs with other tables. To speed up this view I am thinking about creating index(es). Should I create one index which has those 7 columns or 7 indexes for each column? Do my joins ...more >>

Hi All I would like to make a small unique identifer for a session. This will be passed into an "extended stored procedure" as a kind of handle so as to maintain internal context inside that DLL as various sessions repeatedly come back to it. I could use just the SPID which would be perfect...more >>

Hello, I need to pull data from one table into another table for a 2 month period, but it is not a 'rolling' two month period, it is and accounting 2 month period. So this being December, I want to pull the records for the months of October and November based on the last successfull ETL lo...more >>

from example a do this in Vb. scColor.CommandText = "Update Product Set Color" & WColor.Trim & "=" & WcolorValue & " Where Product='" & VarPubProduct & "'" In a Trigger how can I know wich column is update Color01 or Color02 or color03? thanks ...more >>

I know that you can use a default value to replace NULL values in a select statement: SELECT ISNULL(price, 0.00) AS Price FROM titles This will give you all the non-null values and '0.00' for all NULL values. I have a database table that contains an INT field that is NOT nullable....more >>

Hi, We developed a server application in VS2003 running in .NET 1.1, working with MS SQL 2000 server. The application works with the standard DB provider - no hacks. All the DB code in written in C# - no stored procedures in the DB. We want our application to work with SQL 2005 Express DB, ...more >>

SQL 2000 CREATE PROC My English is pool. I want to create a proc that could add notes to table,but my programming is not right. Please help me ,thank you . £­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­£­ CREATE PROC PROC_ JGSZ_BMDA_ADD ( @si_Id_b...more >>

I know this is more of a front-end issue, but I need to know how to do it in TSQL (sql 2k). I have a field that I need to convert to mixed case. The field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to read "Law Offices Of John Doe". I'm sure with some work I could come...more >>

I asked a question earlier about how to tell of a field exists. Now I'm needing a query to tell if a particular index (index name) exists. ...more >>

I have this update trigger that is basically logging changes to a table. Of course I'm getting an "Subquery returned more than 1 value" error when I try to run a update query that updates more than one row. How can I modify this to handle update statements that update more than one row at a t...more >>

I have a situation where we are converting our Access databases over to SQL server. The VB conversion app i wrote successfully converts all the schema, data etc. But the need is now to be able to use Access as an interface to SQL server so that records can be modified by our tech support peo...more >>

I'm looking for the best way (and most efficient) to check to see if a field exists. I have the following, but am not sure if it's the best way. IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename') AND Name='Fieldname') Is there a better way? Also cur...more >>

Does anybody have an example of the T-SQL code to setup a linked server between MS Access 2003 and SQL Server 2005? Thanks, Ron ...more >>

I am trying to determine a difference btwn today's date and a hire date in a query. I am using the DATEDIFF function and I need Today's date to be typed in as a parameter. I have tried several things and I keep getting errors. I have tried using GETDATE() but I can't seem to get anything to wo...more >>

hello everyone. i have a table of images like so: create table images ( imageid uniqueidentifier primary key not null, galleryid uniqueidentifier not null, caption varchar(100), ordinal int not null) the ordinal value governs the sequence in which the user wants the images to be prese...more >>

What's the SQL for adding a field to a table? And can you direct me to the documentation. Is it an ALTER TABLE?...more >>

Hi all! I have 2 tables to confront. is there a method that avoids the using of a cursor? that is: for each record in the table a i have to update a set of record in the table b that respect the first record. I'd like avoiding the cursor... any idea? thanks milly...more >>

The following post is similar to one that I posted without feedback a few days ago. Please advise me if there is a better newsgroup to post questions on SMO to. I am attempting to disable a job using SMO with the following function: Public Shared Function DisableJob(ByVal JobName As St...more >>

Hi Everyone! I have a problem that is basically a database issue in SQL Server 2000 compared to Sybase 12. I am working on a application that should support both SYbase and SQL Server as backend. My code works fine in Sybase but not in SQL Server. Following is the query that will give a an ...more >>

I have the follwing tables which contain error records and error reasons. There are two erros entries for every error reason. DDL if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[INPUT_ERROR...more >>

Hi All! i want to update a column based on another column and using following quey but its giving me error any help update hospital1 set hospital1.father_name = temp.father_name join temp on temp.id = temp.id and hospital1.father_name <> 'Not Available' thanx! Farid *** Sent vi...more >>

Hello, how can I lock a record? it must be modified only for one user at the same time. Is there a SQL sentence to do this? Thansk in advance. William GS...more >>

Hi, There are two queries as follow: Original Query Result select * from Customers CustomerId Name OrderId ----------------------------- 11 Mike 101 22 James 102 select * from OrderDetails OrderId Product Quantity -------------...more >>

hi is there a way to do the following on the database itself? whenever a table is created (perhaps with a certain suffix) a back up table be created with the same fields as the primary table with any contraints? don't forget that i'm new :) riyaz ...more >>