I know how to use it marries in the query but that is not getting, if somebody can me to help, I thank!!! IIf(IsNull([campo1) Or campo1=0,'teste','teste1') AS msg ...more >>

Looking to find all records in one table that are not in another table. What is the most efficient way? ...more >>

How do I pass a variable into a query and return a result set which will give me all of the parents and its chiildren as they are tied together in one table? The structure could be as folllows Table ( MenuID as int, ParentID as int, Description as varchar(25), Text as varchar(...more >>

Hey EB Can any one replace the sql update sentence below, using a positioned update with the cursor that is already in use?? I can't get the right syntax for using the 'WHERE CURRENT OF' clause.. TIA Rea //////////////////////////////////////////////////////////////////////////// /////...more >>

Hi can someone please offer me some help with the following. I have a DB which has a Document table and within that a text field called 'Document'. I want to created a new Field called 'WordCount' and create a script to calculate the number of words (roughly) in each document and place that nu...more >>

I want to learn about Stored Procedures. Please give me some good web sites from where I can learns....more >>

What is recommended/prefered way to create global variable in SQL Server 2000 database?...more >>

I have deleted all records in a database that contains a numeric field with an identy increment (an autonumber filed). When I add new records, the field does not restart at 1. How do I reset the field so after deleting all the records, a new record starts with the field at 1 Thanks...more >>

Can anybody help me with this one? I decided to use a binary_checksum column with a list of column names in order to quickly lookup data during the build of a dimension (as suggested in a MS paper). Selecting the distinct list of columns and the distinct checksum column gave different resul...more >>

Hello All, I am trying to compare the inserted and deleted tables and return a string for every row as to what was modified. The actual work is done by a stored procedure which is called from the trigger. When I try to update three columns in a table with 30,000 rows , it takes 18 minutes. ...more >>

I am trying to use sp_xml_preparedocument however the text that I pass into it has an additional ' (apostraphe) which forces sql to bomb out I get the following error messag [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio The line that causes the problem is as follows. <tb...more >>

Suppose I have the following 2 tables Patients PatientID --- 1 2 3 Medications PatientID Med --------- ----- 1 A 1 B 2 A I would like the query results to be: Patient MedCount (plus many other fields from the Patient table here...) 1 ...more >>

Is there a built-in SQL function that will convert a money amount (10000.0000) to a string like this: '10,000.00' ? Something similar to the VB FORMAT function? Thanks in advance, Deborah ...more >>

Hi Is there a function to pad a result with zeros. I have data stored in a table as 00515 but when I do a select I get 515. Is there a function to pad zeros to it. The length is always 5 so if i have 15 it should add 00015 Thanks...more >>

It appears that tables published for replication cannot have their triggers disabled with ALTER TABLE <Table> DISABLE TRIGGER ALL What would be the best way to disable triggers on tables published for replication. ...more >>

Using DTS to connect to a MYSQL database I need to pull only yesterdays records. I am using this query, but it's not working...any ideas? Thanks! Select name, address, phone, enrolldate from People_tbl WHERE where left(`enrolldate`,8)= DATEADD(curdate(), Interval -1 DAY)...more >>

Environment: SQL Server 2000 SP3a on Windows 2003 Server I'm selecting data from mutiple tables in DB1, joining them to tables in DB2 (not the IBM product), and inserting them into DB2. The databases are on the same server. I wanted to compare the use of OPENQUERY vs. using 3-part object nam...more >>

In Books Online under Referential Integrity which has 3 bullet points linting only 3 of the 4 precluded actions. Can you tell me the missing fourth user action wich is prevented by RI....more >>

Hello. This is a query, with, I believe, a twist. It is a running total scenario, but with the additional requirement that the running total must not be less than zero. Given the following data: CREATE TABLE rt ( i INT NOT NULL PRIMARY KEY, amt INT NOT NULL ) INSERT rt VALU...more >>

I'm currently working on helping port an existing app to SQL Server 2000. The databases that we've worked with before have been case sensitive when it comes to values, but not names. Is there an easy way to replicate this behavor? Or does it require what I'm afraid of - setting the collate valu...more >>

Hi all, I have a SQL Server job running and I altered one of the stored procedures it is running. But I cannot see the effect of the change. I checked my stored procedure and it looks correct to me. So I am guessing that the job is still executing the old version of the stored procedure. Is ...more >>

DTS Transferring data from one table to another using 'Data Driven Query' task and transformations in VB script with DTSDestination("MyDate) = DTSSource("MyDate") both fields are with datatype 'DATETIME' After the transfer, I noticed - Destination table field (MyDate) is WITHOUT millisec...more >>

We're experiencing a problem on SQL Server 2000. We're doing a very simple query on an unique index field results which performs bad because the Optimizer decides to use an Index Scan instead of an Index Seek. The query we're testing is below: SELECT * FROM hugetable WHERE unid='abcd...more >>

Hi and thanks for reading: MSSQL 2000 Given a table Table A id integer x,a,b,c float x is calclulated from the values a,b,c of the rows < id id is the primary key Up to now I calculated x at runtime using a function: select id,a,b,c,f_func(id) as x f_func(id) basically is a select...more >>

Hello, I am relatively new to programming. I am attempting to teach myself SQL Server, IIS Management, and web-programming. To accomplish this, I am developing a financial database application that will track among other things, stock market and company performance info. Things like: --- d...more >>

Hi All, I'm looking to retreieve only the top 1 record that matches a certain criteria. For example: UserID Name Age 1 Dave 10 2 John 11 3 ABC 12 2 Jack 13 2 Jill 14 1 George 15 I basically want to select the 1 entire row for each User ID (the one with the largest age) Select * ...more >>

I am trying to write a SP that creates a record in one table, and then updates all the records in a 2nd table where the key is in a list. eg. >>>>>>>>>>>>>>>> Begin Tran Insert into Table1 (etc) Values (etc) If @@error <> 0 goto AbortTrans Update Table2 Set field = value W...more >>

Greetings, Hierarchic query in YUKON. Will SQL Server development team(read Microsoft) make a simple statement for hierarchic queries (something like CONNECT BY PRIOR in Oracle ) in new version of SQL SERVER (2005) Regards Jure, MCDBA 2000 OCP9i ...more >>

I am converting an Access mdb to an Access Project accessing an MSDE 2000 database. One of my queries uses a function in the modules section of the front-end. As I understand it, queries cannot access functions not known to the server. I therefore need to convert this VBA function to a UDF o...more >>

This statement does not work as I expect it to work. There are almost always several SD rows for each of the HT rows. The result I expect is a concatenation of the attendance codes for each day of the week. What appears to happen is the HT rows never accumulate. Each SD row is evaluated against...more >>

I have a CompanyTable that contains a field BegDate and EndDate with only one record ( ID,CompanyName, Address, BegDate, EndDate,FiscalYear, etc.) I want other views to extract history records from other tables (ie. pickedItemsTbl which containes the dates an Item was picked), but I want to lim...more >>

Hi, I'm trying to do a left join to a table and I'm just interested in the top record of the join. In this case a story may have several pictures, but I want a list of stories with the top priority picture. This is want I want : (SELECT 1) SELECT story.id,story.title,picture.source F...more >>

Hi All, I am developing a set of stored procedures that suppose to work in a distributed environment consists of many SQL Servers that can have different collation settings. From a main SQL Server, I set up linked servers that allow my stored procedures query data from remoted SQL server us...more >>

This was the best example that I could come up with that programmagically relates to my specific problem. I have two tables below, EmployeesWorkHistory and EmployeeWorkDays. EmployeesWorkHistory stores any given day that an employee works. What I want to accomplish is to be able to update the...more >>

I am trying to run: select * into table_a from openquery(oraclesrv,'select * from table_b') everytime I run this it locks down the database where table_a is located and nothing else can be ran there? Any help?...more >>

Hi I have certain stored procedures that are reponsible to create tables or otherwise some code that have to spit out the information. I want to make sure that everything worked as coded and if not I need to get an automated message indicating the error... Question is whether I can use...more >>

Hi Al How can i Call a SQL 2000 DTS Package from VB .NET 200 Thanks in advance...more >>

I have 2 fields in my table which I want to append into one in my SQL statement. Kind of like this: SELECT [ABC], [Field1] & "," & [Field2] AS MyFields FROM Table; How can I do this? I am using SQL Enterprise Manger with an MSDE database. Thanks! ...more >>

I have a schema in place and I'm starting to think that it must be a bad design because I'm having the hardest time organized my results so that I can present them the way I want. Here is the situation. Our website has a support section that is organized by product, so each product has suppor...more >>

...more >>

I would like to know what programs or tools I can use to edit SQL tables directly. Thanks Peter...more >>

I have 2 Tables Table1 ProductCode OptionNum Line Table2 ProductCode SelectionNum SelCriterion I am trying to Select records in table1 where the ProductCode = ProductCode and OptionNum <> SelectionNum and Line <> SelCriterion. When I try to do a simple select statement I get rec...more >>

Hello I need to get something done is t-sql that is a bit strange. I need to do a group by on 2 levels in 1 go. an example SELECT A_ID, A_DATE, SUM(A_VALUE) FROM A_TABLE GROUP BY A_ID This goes into a view, so that our reporting system can add a select on A_ID and a between select on...more >>

We reset a server yesterday and have put MSDE 2000 A on the W2K server. I cannot see the server from my laptop (dev box). But the client tools on the server that I loaded from my developer copy of SQL show it running. I can also use the data in my .NET app. Any ideas? TIA --Stephen Rus...more >>

I know you can use enterprise manager to see the "formula" for a computed column, but how do you do it using TSQL? I have looked all over the place and cannot find how to retrieve this information. Thanks, Earl...more >>

We pipe all the source files for our procs through isql to compile We also use AWK to do substitution from an include type file. eg: awk.exe -f \rio\awklib\defines.awk < %1.sql > %1.pql echo. echo Compiling... isql -n -E -SDevServer -dproduction -i%1.pql A new proc contains the UK pound...more >>

Hi Everyone, I am not contradicting anyone's opininon here but I always here the term "there is always a set based solution" and one does not need to use cursors. However seldom we find ourselves not using cursors because of the data we have or its that just we need to do a row by row operatio...more >>

What are the pros and cons of modifying a data type of ntext to varchar (7000)? The table that contains the varchar is just a audit table and will only be used for internal reports. Thanks in advance. -rob ...more >>

I am using a query that contains an aggregate calculation: SUM(Sales * [%Br Incr Sales Selling] / [%ACV]) Some of my fields have zeros. Thanks, Greg...more >>

I need to return the results of a query adding a new field that indexes the results. How should I modify my select statement to make this happen? Example of query results now: DATE ACT AMT 1/1/04 5521 43.22 1/1/04 7561 98.11 1/1/04 2293 76.18 .... Example of desired query result...more >>

I'm fairly novice-level when it comes to SQL, so bear with me. I'd like use UPDATE to change some rows and report the number of rows that are updated. I imagine I can do it using something like PRINT SELECT COUNT(*) FROM MyTable WHERE Status='A' UPDATE MyTable SET Status='I' WHERE Status='A...more >>

Hi, I am trying to create a stored procedure which does a SELECT * FROM sometable. Next I want to reference certain of the variables in the result set. How does one get at the data in T-SQL? Do I need a cursor or a table or something? I'm looking for something like a recordset in ADO th...more >>

I read a great Microsoft Knowledge Base Article recently (http://support.microsoft.com/default.aspx?scid=kb;en-us;243586). It's about stored procedure recompilations, some of which are caused by temporary tables Today I was reading an old article by Joe Celko (http://www.dbmsmag.com/9809d06.html)...more >>

At present I have been designing a lot of stored procedures which are inserting data into a number of tables. However by doing this i am taking up a lot of space on the server and have been told to design temporary tables instead. My stored procedure is below can anyone tell me how I can create te...more >>

Hi, I want to create a stored proc that will allow me to search for words that are similar to a given word, specifically for abbreviations, i.e. MHz would bring back MHz and Megahertz MegaHertz would bring back MHz and Megahertz Is there anything built into TSQL that will do this for me o...more >>

Books online says I can create a view over two different databases if I have a view created on one of them, how can I do that? ...more >>

Hi Guys, from sql server 2000 (desk top) i created a link to oracle 7.3 and created a procedure which will update sql tables and insert into oracle table. from query analyser i can execute this procedure. but when i configure a job to execute this procedure it is failing? any idea pls advi...more >>

Hi ,I have a table called Functional Suitability belo Trust Site OFA F 5A1 5A11 20000 23. 5A1 5A12 34567 45. 5A1 5A13 14000 12. RR8 RR81 0 RR8 RR82 0 RR8 RR83 0 I am using Select statement below to aggregate some of the columns.However,since there are some zero fields in OFA,I am havin...more >>

I have used the sp sp_settriggerorder to set the trigger order for a specific database trigger that I had created. How can you read the current trigger order for a given table ? There is no coresponding sp_gettriggerorder regards Andrew Barnes Leicester...more >>

Is it possible to use the "ConnectionID" Property in SQL Server V7.0 and SQL Server 2000 to identify what Application or bespoke MS Access Database is attempting to connect to SQL Server? I want to achieve this in order to block users from connecting to Databases other than by using recog...more >>

Can anybody advise I have got a script which extracts the columns/tables in a SQL Server database that I recently designed. I do not know how to extract the Column Description that I have assigned to some columns through Enterprise Managed ...more >>

CREATE TABLE Col1 varchar(25) Col2 bit Col3 datatime The above table can have matching pairs of Col1 & Col2, e.g. Dog/0 and Dog/1. I need to be able to index the first column but restrict it so that there is 0..1 row of Col1/Col2. I wanted to create a composite PK but can't because the Col2...more >>

Is it possible to use convert in xp_sprintf? Im doing something like the code below, but I get a syntax error near the keyword convert. Any pointers on how to get this to work? Thanks, ccallen. declare @out_string varchar (255), @param_one int xp_sprintf @out_string OUTPUT, 'param one: %s', ...more >>

Please tell me is there any difference between AFTER trigger and FOR trigger in SQL server 2000? Are following SQL statements identical: CREATE TRIGGER name ON table FOR UPDATE ... CREATE TRIGGER name ON table AFTER UPDATE ... MSDN is not clear about this......more >>

Consider following table: CREATE TABLE tt ( c1 int, c2 int, c3 int, c4 int ) Suppose we have index on c1, c2, c3 columns: CREATE INDEX idx1 ON tt (c1,c2,c3) Is there any reason creating another index looking like prefix of first index? I mean indexes like: CREATE INDEX idx...more >>

Hi I have this problem: declare @oMail int --Object reference: message declare @oConfig int --Object reference: configuration EXEC @resultcode = sp_OACreate 'CDO.Message', @oMail OUT EXEC @resultcode = sp_OACreate 'CDO.Configuration', @oConfig OUT .... EXEC @resultcode = sp...more >>