Folks I have a typical Parent / Child table relationship. I have a field that is always present in Parent but only sometime in Child (the idea is to override de Parent value for a specific Child line). Now I'd like to query Child and, when the said field is null, have it's value replaced...more >>

I hav a very small application, which insert some date related data in sql server. But tht application while inserting date time values. on some server gives error. because datetime format of machine/server is different. I want to make my application compatible with any date time format on...more >>

Hi, I've created a trigger to capture 2 fields (f1 and f2). I did and check by using If update(f1) or update(f2) then only firing the trigger to create old and new image. But, this trigger will be fired eventhounght the f1 and f2 value did not change. How do I check the changes before fire...more >>

Hi all, In our development centerwe do have a development database, Test Database and a release database,the development database goes frequent changes , creating new tables,new fields, new relationship, as it is spanned among 20 developers, we are not able to track down these changes ef...more >>

I am trying to execute a DTS package from vb.net. On my local machine I had no issues, but as soon as I uploaded to my company's server I received the following error "Login failed for user 'NT AUTHORITY\NETWORK SERVICE'". I searched on google and found someone's solution: I executed this sta...more >>

Using VB6, SQL Server 2k, ADO 2.8, I am trying to implement an error handler for ADO CommitTrans. I pause in the middle of transaction, pull the network wire, then write more records and call CommitTrans. It completes without error even though the data was not written. I have checked the Er...more >>

I have a stored proc that can take 30 minutes to run. In QA it takes only 30 seconds. Once complete, however, the execution plans are essentially the same. My other similar SP's don't have the issue and I've dropped and recreated the SP just in case. Any reason a SP would be slower than QA? ...more >>

I have a system which is going to be talking to other sql servers and am looking at various options on transfering data realtime. Mainly this is going to be for changes in the database (a persons name or status as well as periodic updates that may affect a couple hundred records). At the m...more >>

Hi all, I am trying to generate a list of all available SQL Servers (named instances and all) on a network. I have seen over and over again to use SQLDMO or isql -L. The problem that I am having is that these methods only seem to want to return one instance from each computer. ex. Comp...more >>

Let's say I have a column that is a varchar(3).=20 What is the best way to pad result with 0's where length is < 3. Column Result ----------- --------- 1 001 12 012 123 123 I hope this makes sense. Thanks, Yosh...more >>

I am trying to execute a DTS package from vb.net On my local machine I had no issues, but as soon as I uploaded to m company's server I received the following error "Login failed fo user 'NT AUTHORITY\NETWORK SERVICE'" I searched on google and found someone's solution I executed this statement...more >>

In my table I have a column that is MODIFY_DATE. I'm trying to track when the row was last updated. The default value on the column is getdate() so I do know when the row was inserted. I wrote a trigger like this: CREATE TRIGGER [MODIFY_DATE] ON [dbo].[cp_Exceptions] FOR UPDATE AS UPD...more >>

How do I test if a varchar column contains a numeric value? ...more >>

Below is the code for my SQL View. I am trying to compare the actual amounts in a table with a calculated amount to find differences. When I run it, the column named MedDiff is giving 9.99999E-03 as a result. Do I need to use CONVERT or something so that I can compare the result to get all n...more >>

I have a scenario where I have 3 tables joined together. The first is the "main" table that will define the result set. The second is a lookup table; the main table has a foreign key to it. The third is a detail table; it has a foreign key to the main table. Both the second and third table...more >>

Greetings, I have a stored procedure that I would like to use. It currently gives an error message and I am stumped. Would any one be able to help get this procedure to work for me. CREATE PROCEDURE reports.mp_getuserfpsfigures @ReportInfoType varchar(10) = 'ALL', @ReportBreakdown varch...more >>

Hi, From Invoices table I want to select those rows where Doc_no in those rows is same. What will be the query for this? Thanks -- pmud...more >>

I am using the following --COPY DISK DECLARE @FileName varchar(200), @bcpCommand varchar(2000) SET @FileName = REPLACE('C:\OUTPUT\'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-') SET @bcpCommand = 'bcp "SELECT Column COLLATE SQL_Latin1_General_CP437_CI_AS FROM civ..SALIDAserfi...more >>

I need to create a stored procedure across multiple databases for a Crystal Report;Crystal version 8.0.1.0. Even then, I don't know if the 8.0 version of Crystal will enable me to use a stroed procedure across databases. Can anyone enlighten me about how to go about creating a strored procedu...more >>

Hi everyone, This might be a tough one, I don't know if this is doable. Basically I want to create a stored procedure to return estimated size of a table. because I don't know how many rows it will have in the future, I want to calcuate how much disk space it takes to have one row, ...more >>

I am sure many of you folks are already subscribed, but for those who are not, here's a story of some DBA job interviews of candidates that should just be summarily shot: (This will wrap) http://www.sqlservercentral.com/columnists/sMcCown/howdoyouspellsql_printversion.asp -- Peace & h...more >>

Hi, I've noticed in SQL Server Management Studio (under types branch), the existing of new object type "User-defined Type" Is there any difference between User-defined Data Type UDDT and User-defined Type UDT ? Thank you ...more >>

What is the procedure for getting a list of users that have rights to access a database? I'd like to get a listing back of anyone who has any rights to a specific db (sorry still learning the backend dbo type commands in sql server) also any good articles on security permission settings i c...more >>

We have a process that accepts a small XML message from an external application and passes into SQL Server. Our database used sp_xml_preparedocument and sp_xml_removedocument. We pay close attention to make sure that all of our handles are "removed" After this process runs for sometime, w...more >>

How can I return boolean value to the client. This doesn't work: SELECT column1=case when exists (select ...) then TRUE else FALSE end,.... If I return : SELECT column1=case when exists (select ...) then 1 else 0 end,.... then client gets the int32 value regards,S ...more >>

I want to be able to set the default value of a column to be the next number available, ie. max(MyColumn) + 1 (?). Ordinarily, you would use an identity field for this, but a) we already have one (primary key) and b) this value will possibly change such that several rows will have the same ...more >>

I have the following case statement: CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos) WHEN 0 THEN 'md2' ELSE CASE i.idxhstRSXOLastAct WHEN 1 then 'mdG' WHEN 2 then 'mdR' WHEN 3 then 'mdG' WHEN 4 then 'mdR' END END as rsXOxoflg It works ok, but i'd like t oknow if there is a...more >>

Hello, I need sample code (VB) for using System.Data.SqlClient.SqlTransaction Thanks :) ...more >>

I was looking at Northwind and noticed that some of the tables have the same index, but different names on the same table. For example, on the Orders table you have the following script: **************************************************************************************** CREATE TABLE [d...more >>

What is the difference between decimal(9) and decimal(9,0)? Thanks!...more >>

I have a weird issue. I need to be able to see my transaction log for SOX reasons. We have purchased Log Explorer from Lumigent to do this. However, on different databases, we see see differing lengths of data. Some we can see back several months, while our most important, we can only see a co...more >>

I have a SELECT INTO [#tmp] statement. Can I add an index to this = temporary table after it was created by the SELECT INTO? Thanks, Yosh...more >>

I have a table whcih contains order Id (orderid_c), and order date (orderdate_d). Is there anywhere I can program to count the number of order from Monday to the day the report is run, for example, when I run the report on Wednesday, the report will cover from Monday to Wednesday and when I r...more >>

say tbl1 is a data pickup table and tbl2 is the archive table. tbl1 and tbl2 have almost the exact same structure (except tbl2 has an identiy column and tbl1 does not) - these are denormalized tables - to ensure that all the data is retrieved - errors/duplicates and everything (cleanup after ...more >>

I have a common stored procedure that is called from any number of Jobs and I'm looking to determine if theres any way that the stored proc using SQL can determine the ID of the job which executed it so I can go out to MSDB..sysjobs and get the job name and insert it into any error messages ...more >>

I export a Table containing numbers to a fixed-width field text file using the Import-Export Wizard. The numeric fields are automatically left aligned just like the char fields. Is there a way to right align the numeric fileds and keep the char ones left aligned?...more >>

Here is the scenario , I need to pull data from several tables and there will also be a sub query in one of the joins that does summing of an amount column, then I need to perform 3 separate Inserts into diff tables with the data from this query. I was going to use a cursor and then perform t...more >>

I have created a SP that will generate information for the week leading up to the time it is run. The buttonpushes SP needs two values a begin date and an end date. It works fine when I put in the date for the week but when i try to use the variables so I will not have to change the dates ea...more >>

I have a log file in my sql 2000 database that is over 1gb in physical size. The recovery model is "simple". The transaction log is not backed up. Implicit/explicit checkpoints are supposed to truncate inactive portions of the log. After, I issued DBCC SHRINKDATABSE and SHINKFILE commands ...more >>

I have been looking at the OLE automation procs (ex. sp_OACreate, etc. ) and am wondering about their scope. Does the OLE process have scope on the executing transaction? Or must the OLE object establish its own database connection? For example, I have an example using 'SQLDMO.SQLServer' an...more >>

I need a query to return rows that will be used in an insert statement. I think the easiest way to explain is with a diagram: Table1 contains the following rows: SubjName FromName ArchviedDate -------- -------- ------------- Subj1 From1 1/1/2005 Subj2 From1 NULL Subj1 From2 ...more >>

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on all indexes in a database that have a logical fragmentation percentage above a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run this proc with DBREINDEX instead and I want to schedule it weekl...more >>

I'm need a query that takes the number from the identity column, then uses that for the next routine in a range....something like; Select IdentityNumber From TableName Where LastName = 'Somebody' (Then it takes that IdentityNumber, say row 100, an uses it to grab the rows on both sides of...more >>

I have created SQL using a Derived Table. Obviously, there's a method for my madness here and it has been a while since I've used a derived table. I am testing portions of my query and I am having a problem with the following SQL...getting the following error message... Server: Msg 8155, Le...more >>

hi all, given that i have the following tables and data: mst_locs.locid ctl_loctypes.loctypeid [description] 1 1 [plant] 2 2 [hub] 3 3 [warehouse] intersect_loc_loctype locid loctypeid 1 1 1 2 1 3 ...more >>

I'm just looking to get suggestions as to what the best way to handle this key structure is, in terms of performance. At present, the current pk is on an identity value. I 'inherited' this and am seeking to change post haste for many reasons. These three columns combined equate to the prima...more >>

Hi I'm having trouble listing the contents of a directory on my local pc using XP_CMDSHELL. I'm executing the following from Query Analyser to read directories on my local pc so there shouldn't be any access issues (I have admin access to pc and in explorer it is set to show hidden files). ...more >>

Hi; I have a requirement where data is stored like this: TableName.ColumnID 1 2 3 4 5 I want to create a query which will return the result like this. Result1 Result2 Result3 1 2 3 4 5 null I have done this using Temporary tables and other T-Sql co...more >>

I know that my request is beyond of these newsgroups but I beg you pardon for that. Unfortunately (or not) I am from today working with Oracle and I would like to know if anyone here knows works Oracle with 'synonyms' and objects. I've created a table from an interface app (very similar to ...more >>

Hi, I have a procedure which was working fine until a computed column was added to a table and indexed. The updates now fail, indicating a problem with ARITHABORT settings etc. I have attempted self-help and read the following articles among others:- http://msdn.microsoft.com/library/de...more >>

When I try to get the week I get the wrong answer. Anyone than know why? declare @week int select @week=datepart(ww,getdate()) @week = 34 when it should be 33!! /Nettan...more >>

Hi, I have a procedure which was working fine until a computed column was added to a table and indexed. The updates now fail, indicating a problem with ARITHABORT settings etc. I have attempted self-help and read the following articles among others:- http://msdn.microsoft.com/library/de...more >>

Hi Gurus, I have the following simplified tables: Products Id | Product | Weight | Length | Rate 1 | Oranges | 12 | 0 | 1.20 2 | Sausage | 0 | 10 | 7.34 Measures Id | Measure 1 | Weight 2 | Length Now I basically need to work ou...more >>

I have three tables: 1 - people 2 - peopleAddressLinkingTable 3 - Addresses I want to get all people and their street name. I thought that this would be simple and thought that the following query should work: select * from people, addresses.street from people left outer join peopleAdd...more >>

Hello, How can I get real value by select statement to some simple function as follows select 3/8 The result is 0 Can I get the value (with 3 digits ) : 0.375 (or specific digits, i.e = 2 digits) 0.38 (rounded up) Thanks :) ...more >>

RESTORE FILELISTONLY FROM DISK = @path LogicalName PhysicalName -------------- ------------------------------- MyDB_Data C:\MyDB\DATA\MY_DB_Data.MDF MyDB_Log C:\MyDB\LOG\MY_DB_log.ldf Is it possible to exec this sql command and the capture the result(logical, physical name) into varia...more >>