Hi, I'm using the basic functions of XPSMTP http://sqldev.net/xp/xpsmtp.htm - and it's a nice tool, however, I would like to send emails to people (emailaddresses) from out database, but I can't see how to set it up. The company I work for sells hardware over the Internet and every evening I...more >>

Hi, What I would like to do is use the FileSystemObject to get the size of a file on my server, and then update a table with its value. I think I have got the VBscript part sorted out, but I have never used VBscript in a SQL Server DTS package to update a table. Has anyone got any examples ...more >>

hi al Is table variable able to join a temp or regular table in sp or udp? the following script gives error message as " Line 14: Incorrect syntax near '.'. use northwin g create function testfucnt( returns @rettable table (orderID int, Quantity int a begi declare @t table(orderid int i...more >>

H I am trying to to a conditional calculation. What I am trying to do is- if the variable = 0 (zero) then answer = 0 (zero) else perform a calulation. I keep gett ing erro Server: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered Hope somebody can assis Cheer Please...more >>

I have 2 sites on different shared servers. Each has an SQL account. WebsiteA has SQLA WebsiteB has SQLB When on WebsiteA, I need to access the data from SQLB. I have a DSNLess direct connection. I have no problems accessing data. I do have a problem creating a temp table and reading from it ...more >>

Can somebody help out a newbie? I need to update a column in table "A" from a column in table "B", and then delete the record in table "B". Can this be accomplished without a trigger? Here's what I have so far: USE [MyDatabase] UPDATE tableA SET tableAcolumn = tableBcolumn FROM tableB ...more >>

I have a program writting in VBScript running under Windows Scripting Host accessing a SQL Server 2K database. All of the database calls are set up using ADO Command objects with SQL commands as the command text. I have the Prepared = true flag set on all command objects. The program was r...more >>

Hi, I have a file called Customer, and in it is the field "TheDate" Basically I need to say, "Select * from customer where the TheDate >= Today-6 months" How? Thanks James ...more >>

Please let me know if this is not the appropriate group. I have an application that polls the db every few seconds to refresh the data. Would like to change so that the db sends a message to my app when something interesting happens (insert/update etc.). I've looked into sp_addmessage, raise...more >>

trying to duplicate an access application with sql. the access code: Format([decimal field], "0%") takes 0.01500 and makes it 1% then takes 0.0455 and makes it 5% what sql code can be used to do the same thing? my code rounds both up, but the 4th place after the decimal seems to ...more >>

A colleague of mine found a clever way to calculate a total based on two possible columns. One column contains an additive value while the other contains a multiplicative value. Only one of the two columns is populated for any given record. example: ADD MULTIPLY ---- -------- 10 nul...more >>

Hi all. I'm getting an error message trying to drop a foreign key and I can't quite figure out what it is trying to say. My Alter Table statement says: ALTER TABLE dbo.INVENTORY_BALANCES DROP CONSTRAINT FK_INVENTORY_BALANCES_INVENTORY and the error I am getting is: Server:...more >>

Hi, I am trying to set up database users by using scripts from the command prompt, like: sql -E -i 03_Pics2Share_IntegratedSecurity.sql but I got an error message: 'sql' is not recognized as an internal and external command. What can I do to make the 'sql' be a command? Thanks ...more >>

query analyzer : generate sql is there any way to stop QA from generating the SET statements ? ...more >>

Say one has a table with 2 columns Region = varchar(20 Sales = intege If I wanted to select the regions that together produced 80 percent of my sales how can I do this. Basically I'm trying to apply the 80/20 rule, where aprox 20 of the regions will provide 80% of the sale Is there an easy SQL...more >>

Where can I find .chm files for ADO 2.8? The ones I get with Visual Studio is 2.1, and when I install the new MDAC i don't get new help files. Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo msplival@jagor.srce.hr...more >>

Hi all, I am grouping a result set be week (datepart) which is fine. However, I need to now find the week commencing value of each week, I guess by using @@DATEFIRST. My week commencing must always be a Friday - Thursday. I've tried lots of different datetime calculations and cannot work it ...more >>

I would like to add an additional column(row_order) to my query, so I can use it to assign the result. It look like this: First Age Row_Order Mark 45 1 Mark 55 2 Scott 50 3 Micky 13 ...more >>

Hi, I have a small problem. I need to get a number of day (I mean 1-7, Mon-Sun) from a datetime value. For example, if today is 10th March and it is wednesday, I need to return number 3. Can anybody help? Thank you Tom -- Using M2, Opera's revolutionary e-mail client: http://www.o...more >>

I can I get OPENROWSET to recognize @cProFile??? @cProFile = 'PS000018#TXT' Select * Into #TempTxtFile From OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Text;HDR=NO;Database=\\MS000APP01\AFS\000_PicuPro\', @cProFile) Thanks, Lynn....more >>

Which SQL Query can I use to calculate the recordcount of a recordset in case that the recordset has been created already ? I know that "SELECT count(ID) as AMOUNT FROM tblEmployee" would do the job, but in my case the ADO recordset has been created (for example rstQ) already and I want to fin...more >>

I have a performance question somebody told me a left join is a lot slower than an inner join to avoid a left join that guy creates a "0-row" in each table (PK id = 0, all fields empty) is this true? does anybody have links to articles talking about that... Dominique ...more >>

Hi I've got the following query select x.a * x.b as total, total * x.c as total_c, total * x.d as total_d from theTable as x it does not work, i have to use select x.a * x.b as total, x.a * x.b * x.c as total_c, x.a * x.b * x.d as total_d from theTable as x is there a workaround?? th...more >>

I have a stored procedure which dynamically creates a distributed query and then runs it. The user has to supply the linked server name and database. The problem is that if a server is specified that is not the server on which the stored procedure is being run, this error appears: "Heterogen...more >>

I'll use sports as an example. Suppose we have 2 tables: Team and Player Team has 3 fields: TeamID int IDENTITY(1,1) TeamName varchar(50) Roster varchar(8000) Player has 3 fields: PlayerID int IDENTITY(1,1) TeamID int PlayerName varchar(50) I need to concate...more >>

Hello, I have a table called accounts, and a second table called agreements related to it with a relationship based on the accountid. Accountid is an identity field in the accounts table. I want have a trigger that when ever an account row is added, it also will get a related agreement row in ...more >>

Orginal output year ------ 2004 2005 2003 2008 2006 2007 how do I convert to such an output : c1 c2 c3 c4 ---------- --------- -------------- ------------ 2003/2004 2004/2005 2006/2007 2007/20...more >>

Hi, I am attempting to write a procedure whereby I can get the maximum numeric value in a varchar column (which may contain a mix of words and number strings). I'm trying to use: SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0 end ) FROM table However, given ...more >>

I am needing to run a SQL Query against a database - I'm looking for a listing of patient names (and various other things) that meet a specific medical test criteria. The query returns 2 rows for each patient with the same information. I want the TEST DATE to appear only ONE TIME/patient. ...more >>

Hi all. Can anyone tell me how can i get column descriptions from SQL Servr 2000 using ADO? I tried ADOX but no success. Then ADO OpenSchema would do the same thing and it does if source is Access for example but SQL Server returns empty string even though there is a description defined. ...more >>

Hi All I have been trying to do this for quite some time now and could really do with some help. I would like to know if it is possible to insert many records into a table using a select statement with joined tables. For example, say i want to create an empty note in tblNotes for every company...more >>

SQL Server 2000 Windows 2000 I would really appreciate it if someone could explain a strange error that appeared on a customer PC. We have an application that loads data from an Access database into a SQL Server database. To start the application you have to "sign in" to the SQL Server dat...more >>

Does any one knows to resolve this problem, please ??? This example giving me zero result: (SQL 2000, field NbDate = int) *** In QueryAnalyzer "SQLSumday" statement, giving me corect result *** ***This I use in ASP*** SQLSumday = "BEGIN " & _ "SELECT days - COALESCE((SELECT COUNT(*)...more >>

Is it possible or advisable to share tables between two SQL Server databases on the same server? One database is restored every night from an offsite source. Thus, any objects created in the local version are lost after the restore. Is it possible to create a new database locally which accesse...more >>

Hi there, Does anyone know how to calculate the logarithm of a given number to a given base? SQLServer only provides mathmatical functions for natural logarithm and base-10 logarithm. I'll appreciate any help. C ...more >>

I am trying to deconstruct a faulty SQL statement written by a predecessor. It seems very straight forward, except I don't understand one line; WHERE @@ROWCOUNT!=0 BEGIN I would expect the line to read; WHERE @@ROWCOUNT <> 0 BEGIN Is this just another way of putting it? Thanks ...more >>

Hi declare @c_dt char(12) select @c_dt = (SELECT CONVERT(char(12), GETDATE(), 10)) print @c_dt go declare @c_dt char(12) select * from name s1, company tk where s1.name = tk.name and s1.status_flg = 'N' and (CONVERT(char, s1.ex_dt,10)) = @c_dt order by s1.ex_dt,s1.na...more >>

Hi, The situation is as follows: - I have a stored procedure with parameter @ID_DOC Char(36) OUTPUT, --for uniqueidentifier (and other paramters) - the first line in procedure is: select @cnt = count(*) from docs where id_doc = @ID_DOC - the transaction begins - the parameter @ID_DOC...more >>

I have timestamp field in table T1. In that table I have only inserts, not updates. Than at night I would like to copy all records inserted during a day FROM table T1 into my other table T2. Unfortunately, I have only read permissions on that table and I can't add getdate() field into both tabl...more >>

Hi, When caracter ' \ ' precedes CR LF, does not occur the line in addition. How to skirt this problem? Jefferson Test: print 'jasdfjkasd \ jkljkljkljl\' print '' print '' print '' print 'jasdfjkasd \xc\c jkljkljkljl\' ...more >>

Don't know if anyone has encountered this before. If you are using SQL's t-sql function "getdate()" and comparing it in its numerical format to the ASP function "Now()" in its numerical format, the two numerical values will be TWO WHOLE NUMBERS OFF. Try this in a SQL Query window: Sel...more >>

Hi! I need your help in writing a trigger for the purpose of storing history information. what I want is every time the "CodeInfo" table updates, the information stores in the "Code_Hist" table. The "Code_Hist" table must store the oldCode, newcode, userid(who did it) and modificationtime (w...more >>

I get a "Type mismatch: 'FormatNumber'" error on this line of ASP code.. <%=FormatNumber(students("GPA"),4)% The "GPA" field is a number coming out of my stored procedure. I need to format it to get 4 digits to the right of the decimal Any ideas on why the FormatNumber function doesn't work? ...more >>

Hello all, I have a query as below which which is a result of 2 data- sets. One generates the common ID's between the 2 data- sets and another is the larger registration dataset count. Thus, the common ID's form the numerator and the total from the 2nd dataset is the denominator. How do I ...more >>

Hi All, I need to run a query following some rules. I am kind of stuck as I was trying to use the logic from earlier help in the NG after a few conditions have changed. DDLs: CREATE TABLE [dbo].[Table1] ( [ClaimNumber] [varchar] (16) NOT NULL , [SupplierID] [varchar] (10) NOT NULL , ...more >>

Hello, I have a next kind of problem: I have a date field which format is(yyyy-mm-dd HH:MM:SS). Now i need to get only the time from that field. I have tried "right(datetime, 8)" and it works fine otherwise, but it drops the zeros off. (example 08:45:33)shows 8:45:33 in result. It would be ...more >>

Hi, I wonder if somebody can help me out here. I am trying to diagnose why a given stored procedure (listed below) persistently recompiles. The stored procedure definition is as follows: - CREATE PROC MyProc @Value1 VarChar(5), @Value2 Integer AS SELECT * FROM dbo.Table1 WHERE Col1 ...more >>

Hello, Data is broken on inserting unicode string into nchar-type column. i'm using ms-sql2k, db-library 6 and vc++6. ANY idea how I can deal with? Thanks! ...more >>

Hi all, I'm sure there is a (simple) solution for this.. I am trying to return a percentage based on two table columns, and am getting a 'division by zero' error where the divisor is exactly that. Is there a simple construct that is commonly used? My Example; SELECT SUM(COLA), SUM(...more >>

Is there a way for me to tell from my VB6 program when an update that I've made to a single record in an SQLServer 2000 database has completed? I'm writing a program that needs to query a single table about 40 times a minute (the response will always contain a single record) and then it needs...more >>

In a table called Status, I have a column named dbInstalled, which contains Yes or No values. I would like to create a trigger which sends an email with SQL Mail whenever a new record is inserted that contains a No value for that field. Here's what I have so far: [CODE] USE FSStatus I...more >>

I bring up my issue again, Please help COLUMN_NAME gives the alias name, how can i get the real column name (OrderID) using T-SQL or executing any extended procedure?. USE Northwind GO CREATE VIEW my_view AS SELECT Top 1 OrderID AS my_alias FROM Orders GO SELECT * FROM my_view ...more >>

How do I do the following 1) Do I put the 0700 in quotes or not IF DATEPART(hh,@start_date_time) >= '0700 2) I want to change the time portion of the @start_date_time var to 0700 ...more >>

Hello: I'd like to know if it's possible to execute a DTS package from within a stored procedure. I've searched Books Online but haven't found any solutions. Any comments or suggestions would be appreciated Brennan...more >>

Hello I simply wish to update a date field in a sql database table to a new date. the column is of type smalldatetim I have tried the following and it just updates the date to 1900-01-01 which is not good Update mytable set MyNewDate = 27/03/200 Where MyIndex key = 66 Any ideas in t...more >>

Microsoft acknowledge that there's a problem when BCP parse a format file that as white space in column name. There workaround is to manually edit the format file to omit all white space characters from the last column in the format file (that is, characters 0x09-0x0D, 0x20). This is my form...more >>

im running sql agent job that backups database with "full" recovery model. the last step of job is : backup log <my database> with no_log. the job return success but still i'm getting an error in the windows event viewer: "Database log truncated: Database: <db name>." error 18278 what ...more >>

I am begining to learn how to use Stored Procedures and was hoping someone could tell me what the use of N' is for. I see this in many examples. Thanks!...more >>

Hi I have a web page which stores search results in temporary tables (##temptable). Each temp table may have a different structure and therefore different column names At some point I will be accessing these temporary tables and so need to know the column names. I retrieve the column names with ...more >>

Greetings I am studying the database and my group and I are researching whehter SQL 2000 is a re-entrant coded ad-hoc query database software system. My understanding is that re-entrant means a file can be opened in read/write condition by multiple users simultaneosly. Would this be correct Also...more >>

hi prasad, Thanku.. Can u give brief in how to write the base procedre: (u mentioned below).. Funtion to get server name - @@servername Function to get login user name - system_user Funtion to get database name - db_name() Thanks. ...more >>

Hello Experts, Is there any way to Parse all my objects at once or within a loop using T-SQL? Thanks in advance....more >>

Hello Experts, I have a View. Can I get the alias name of a column using T-SQL or an extended procedure? Thanks in Advance....more >>

hello experts, Few r responded my prvs mails.. "Write the stored procedure that retrieves the userid, domain, password from the windows or sql server profile and pass it to the "exec MASTER..xp_cmdshell'dtsrun /sbiztune- 3 /usa /padmin /npkg1' "(This is hardcoded).. I created the proc: ...more >>

Hello! I'm using Sql Server 2000. I have problems creating a stored procedure that copies data from DIST_INDATA to DIST_ORDER. Here are my tables: DIST_INDATA distID int 4 (Primary Key) Data varchar 255 Klient varchar 20 Status int 4 Klockslag tim...more >>

hai prasad, Thaku for ur seggestion..so u mean to say that there is no need to run the pre scripts. while running the query analizer..When i log on to windows or sql database we have to execute the procedure and gv the inputs right..System will not takes the server,username,password auto...more >>

Hello ALL I got strage problem. While perfoming long update i got error 8646 (The index entry for row ID was not found in index ID 2, of table 641098466, in database 'MyBase') At microsoft site i found: 'FIX: Error 644 or 8646 May Occur During a DELETE or UPDATE Against a Table That Contains a ...more >>

hai thax for ur help. when i run this procedure in sql analyzer it is asking inputs.as per inputs the dts pkg file executing perfectly. this means: "retrieves the userid, domain, password from the windows or sql server profile and pass it to the "exec." statement" Is it right..or can we...more >>

HI FOLKS! I 'm new to Triggers, and I like to ask what is the problem with my trigger I 'd like to create. I have got 3 tables one is USERS the second one is REPSTOCK the third one is PRODUCTS . The PRODUCTS table containts 'WC_PRODUCT_CODE' ( this is a key ),'WC_AU_PRICE' ( Australian pric...more >>

This question is sort of a follow up on an earlier question. Suppose I have these rows: 3/4/2004 17:52:00 3/4/2004 17:18:00 3/4/2004 17:16:00 3/4/2004 17:54:00 3/4/2004 17:55:00 3/4/2004 17:50:00 3/4/2004 17:15:00 3/4/2004 17:31:00 3/4/2004 17:30:00 3/4/2004 17:27:00 3/4/2004 17:57:0...more >>