Hi all, I'm having an issue with the following code in SQL Server 2000, SP3a (@@VERSION reports 8.00.818). I was hoping somebody else can confirm this (esp somebody from Microsoft). -------------------------------------------- create proc sptest as insert into #tbltest exec('select 1') ...more >>

Let's say I have table Customers and Orders. I want to let the users to be able to change the Customer record (name, address), as long as the customer has never made an order. In other word as long as that particular CustomerID doesnt exists in the Orders table as a foreign key. So I need a q...more >>

2 questions: 1) Until very recently, I thought that table variables were always stored in memory, whereas temp tables were stored in tempdb - so the method for the former resulted in better performance. However, some information on MSDN made me wonder...the link is... http://support.micro...more >>

Hi guys The only difference between the following 2 queries... Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans Where UnitID = '1720200022285010001407' Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans with (nolock) Where UnitID = '172020002228...more >>

Hi All, I have a trigger that does a cross database insert. Is there a way to optimize cross database transaction performance? It takes about 16 times longer to write to a table on another database (on the same SQL Server instance) than it does to write to the same table structure on the lo...more >>

Hello there, I am using the same UDF in a select query, with differing parameters, for example: SELECT A.Something, dbo.fnFunction(A.Field1, A.field2, 'ChangingParameter1') AS Answer1, dbo.fnFunction(A.Field1, A.field2, 'ChangingParameter2') ...more >>

Hello there, I am trying to write trigger that basically inserts a record into an activity history table with relevant user details, record IDs etc. I have this working which is fine, but I want to put a check in to determine if any details have changed during the update so that I am not a...more >>

Hi, How can i Search a string ("ABC") with in All Stored procedures in a Database ? Thanks, ...more >>

Hi, I have an sp, that worked till now good. Then I gave two more parameters, and now I get the error message in my application: Procedure or function XXX has too many arguments specified. The sp has now 46 parameters. Is it too much? How many parameters can an sp have? Another sp of the sa...more >>

When I execute this in QueryAnalyzer I expect to get the current WeekNumber according to Norwegian (ISO) rules: set language norwegian set datefirst 1 select GetDate() as TodayDate, DatePart( Week, GetDate() ) as TodayWeekNumber Result: 2005-01-12 16:24:52.450 3 Since last yea...more >>

Hi all, I'm wrestling with a new system design and am looking for opinions on where to draw the line with normalizing the database. I'm in early design and proof-of concept so have started with a very broken out set of data. I have identified a number of cases where a many-to-many situation ...more >>

Hello, Is there a way to output the result of dbcc checkdb into a file or table? I am trying to send an email to users when there is an error found in dbcc checkdb. Any ideas? Thanks again ...more >>

Is there anyway to login with SQLServer authentication and issue the following ddl statement: create table some_table (id integer) go and have it owned by dbo, (that is without having typed "dbo.") Thanks. -- Galen Boyer...more >>

Does anyone know how SQL-DMO determines when a Database object has a true SystemObject property? Or can anyone point me to a script that programmatically determines if a database is a system database? I have had no success looking in system tables and system stored procedures short of sp_d...more >>

I have a table that contains a number of items. Each record has a primary Key and a Foreign Key to itself representing a parent/child relationship. For example: pkey fkey title 1 null Line 1 2 1 Line 2 3 1 Line 3 4 2 line 4 5 2 line 5 6 4 line 6 etc... What I have spent a long tim...more >>

Hi all, Ahh, the on going saga of moving databases... Ok - having installed SQL Server 2000 on the new server and copied across approximately 10 database I've since learnt that we did NOT use the default collation on the original server, instead we have case sensitive, accent insensitive...more >>

I have 2 stored procedures that are launched through ADO calls in a LabView application. When an error occurs in one of the procedures, I have no way of knowing this, since I can't see the errors through the ADO. Is there a way to force any errors that occur in a stored procedure to be logged? I've ...more >>

I am provided a txt file every month. I need to append records from this txt file to a table, but I only want to insert those records that are non existing already in the table. For example: TABLEA Extract_Date Emp_id Name Status_CD Status_Start_Date Status_end_Date 20040...more >>

Hi, After I backup the log file, the log file size is down from 1GB to 100MB, I would like to know what is the syntax to truncate the physical file as well. I mean to release about 900 MB back to the operating system. Thanks Ed ...more >>

I have a sproc that has a complex select statement consisting of many joins and some joins over Linked Servers. The sproc takes about 45 seconds to run the first time in query analyzer and then 1 second subsequent calls. This would be fine if it only happened when the sproc got changed, but ...more >>

Regarding check constraints, Books Online has this to say: "A CHECK constraint specifies a Boolean (evaluates to TRUE or FALSE) search condition that is applied to all values entered for the column; all values that do not evaluate to TRUE are rejected." However, in SQL Server 2000, at least...more >>

Hello, I'm using the following statement to add 1 day to the current date and get the result with time 00:00:00 SELECT CAST(CONVERT(char(12), DATEADD(d,1,GetDate()),1) as smalldatetime) When running this statement on a Japanese server I get an error telling that it can't convert a char to s...more >>

Hi, I'm tryning to exceute some select on some table which I want to pass a variable (a follow) DECLARE @MyVar sysname SET @MyVar = 'Test' SELECT * FROM @MyVar. Each time I get '@MyVar' is nt declared'. If I do the following : DECLARE @MyVar sysname DECLARE @Statement varchar(25...more >>

Hi all, I'm having some trouble finding out why an update statement that runs in less than 2 seconds from Query Analyzer takes no less than 24 minutes when in a stored procedure. Here are the important parts of the stored proc: CREATE PROC MyProc AS -- Create temp table, fill it with start...more >>

Hi, Anyone know if it is possible (anything's possible??) to perform reverse dns lookup in a stored proc (or scheduled job if easier??) ie convert a passed IP address to hostname?? Thanks for thinking about this if you did... Arth ...more >>

i have one column in the table have xml data. i want output in plain text format. which procedure or function is the best. Thanks, John...more >>

Got a question for y'all. In the code below @status is being passed into a proc. If it's 6 I don't want the condition in bold (event.status) to evaluate at all (select all Status'). Other than putting an if around the whole thing and splitting out two different statements is there any other ...more >>

SELECT 22/7 results 3. I need a real value like 3.1428571..... I tried following too: SELECT CAST(22/7 AS DECIMAL(10,7)) and DECLARE @TEMP_NR DECIMAL(10,7) SET @TEMP_NR = 22/7 SELECT CAST(@TEMP_NR AS DECIMAL(10,7)), @TEMP_NR No luck so far. ...more >>

Hi: In code I am using this: DECLARE @WeekEndingVar datetime SELECT @WeekEndingVar = '12/7/2005' SELECT DISTINCT @WeekEndingVar AS WeekEnding, ....... ______________________________ To assign a week ending date. Basically, the 2nd line needs to be replaced with something that fig...more >>

Hi, I'm trying to test whether one value is in another by using the like clause and concatenating the wildcard character to a variable. My code looks like this: declare @name varchar(20), @name2 varchar(20) select @name = 'test', @name2 = 'testing' if @name2 like '%' + @name + '%' ...more >>

Is there a way to query a field to determine the unique charcters in that given field. For example: COLUMN1 apple bat car The query should return a p l e b t c r Thanks...more >>

I'm setting up DTS jobs to move data from a legacy system to SQL Server 2000 and getting what I think are incorrect results from the insert. The insert checks to see if one field (hp_number) exists in the current table, and if not it should insert the record, but the 'NOT IN' doesn't seem to b...more >>

Hello, we know that one of our sp runs into an error due to an corrupt xml doc. The problem is, in that case we can not call sp_xml_removedocument to release the xmldoc handle. Is there a way to release this handles without closing the connection? E.G. save the hande in a table than re...more >>

I am using Access to report out of my SQL Server database. I am building stored procedures and then using them for the reports. Everything is working fine, but I have hit a snag. I need to build a report to show employee information. I have the following SP, CREATE PROCEDURE spEmpInfo ...more >>

I will try and explain my situation the best I can. Here it goes. Due to the size of our data we have it partitioned into separate databases for each year. Every so often we update the view structure and want to keep the structure the same across all databases. I have a procedure created t...more >>

Hello All, I have so far come up with a partial solution . but it's far from complete. Using the Cursor Object In SQL Server . It allows me to loop through the records in the Table and insert them into another Table.The only problem is that I want all my records to be on one row in the dest...more >>

Does anyone remember if their is a limit to the number of nested transactions SQL 2000 can reach? I was thinking it was 32 but that may be just stored procedures....more >>

I have stored files in an image field of a table. Can I use xp_sendmail to email them? MS ...more >>

I have a tbale that is getting copied via a DTS package from one server to another, straight copy. I need to modify it about so it can do this. Source table contains a column with a URL, that will somtimes contain a URL in this format "http://ourserver/course/ECON101-12345. Not all URLS are ...more >>

this may seem to be a strange question.... Since its the inverse which is the popular implementation.... Our app runs against SQLSERVER, yet we are creating a demo of the app for which we will be using Access. Does anyone know of a way to recreate as much as possible our sqlserver db as an acc...more >>

Is there anyway rather than manually extracting all index's as a script so that they can be created elsewhere? Thanks, Anthony Nystrom...more >>

Hey, Could someone fix up this if-statement block for me, or tell me what is wrong with it, I am sure it is simple, but can't seem to get it working correctly? IF @Requestor='All' then select @s=@s+','+requestor from requests set @Requestor = substring(@s,2,len(@s)) ELSE IF @Client='...more >>

We have been considering using SQLserver as the backend for a large business application developed in Delphi. We intelinked an ADOconnection, an ADOtable and a DBgrid. The table is about 200,000 records. No matter what options we enabled or disabled on the ADOconnection or the ADOtable, the M...more >>

My current system was designed under Perl, there're formulas for fee calculation just like: IF(ANA<500000000) (ANA*(125/10000)/DIY*DIM) ELSE (0 ) + IF(ANA>500000000 AND(ANA<1000000000)) ((500000000*(125/10000))/DIY*DIM + ((ANA-500000000)*(120/10000))/DIY*DIM) ELSE (0 ) means : 1st $500mm Av...more >>

I have a table in the database in which the fields in one of the columns (say status) needs to have a value which present in a pre-defined set of possible values. (suppose I have defined that status can only be "running", "walking" ,"sleeping"). What is the best method to store the set of pos...more >>

I have the following table: Create table #Tree (TR_ID int, TR_Parent int) insert into #tree (TR_ID, TR_Parent) VALUES (1, NULL) insert into #tree (TR_ID, TR_Parent) VALUES (2, NULL) insert into #tree (TR_ID, TR_Parent) VALUES (3, NULL) insert into #tree (TR_ID, TR_Parent) VALUES (4, 1) ins...more >>

I have this update statement in a trigger for insert / update , I fire the update statement following the trigger code and it gives me a error, i am not sure if the update statement is right update AtsAnnuityCurrentValues set mnyPersonalReserveRemaining = inserted.mnyPersonalPreTaxRese...more >>

I have some strange effects occuring within a sub-query that I have created. I have a table of information that I wish to collapse subject to a datetime cuttoff, so I have used the code: select type1, type2, sum(amount) from table where [date] <= @date having defined @date...more >>

I'm trying to import an SQLServer table into Visual FoxPro (I'll also post this in the VFP forum). I need to test the Date portion of a SQL Server DateTime field against a user-entered Date. I tried the following: m_getagsc = SQLPREPARE(gnConnHandle, "SELECT * FROM AgentStateChange "+; ...more >>

I am evaluating SQL server and therefore I made a benchmark comparing the search speed between Access (which we are using up to now) and SQL-Server. I created 2 identical tables on the same Server, once as Access database file, once in SQL-Server with one Counter as Primary Key and Index and...more >>

I'm in a problem with T-SQL Debugger.. I'm DBA on a development environment... Most users use Query Analyzer to Debug procs, and some machines work, and anothers not... The problem is the following... The permissions are ok (EXECUTE on sp_sdidebug, dbo of the current Database)... The Debug...more >>

Hello, This may be a strange request, but I am going to ask about it anyways. Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this: NUMBERS 1 2 3 4 How could I use a select statement in a way that a comma would sep...more >>

select, update and delete Ok. But when I want to insert a value into a table Msgs 7343 returns and operation fails. Source and linked servers are SQL Server and are linked by OLE DB Provider For SQL Server. Thanks in advance...more >>

Hi , Is there any way to get the sql statement that fire a trigger ? Thank you in advance , TLV ...more >>

Hi, i've a simple question (guess it's simple) regarding the MAX function in MS SQL Server 2000. I'll use the example of the according help topic (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ma-mz_3h6g.asp?frame=true) cause it's very similar to my problem ... USE pubs GO SELECT MAX...more >>