Hi, I am trying to set up a job to check on certain updates done on my database, if no updates done, then it will send out email to me use master..xp_sendmail. when I try to assing @message, I want to assign a string to it and a varchar variable after the string, which contains yesterd...more >>

I have a table named Emp as follows: empid int empname varchar(35) mgrid int empid empname mgrid 1 abc null 2 pqr 1 3 lmn 2 4 xyz null empid is primary key. mgrid is self referencing foreign key field towards emp...more >>

What will be the best way to combine several inserts on different tables? I have a table A, when there's an insert into A, I'll need to insert the data also into tables B, C,D,E,F,G & H. Then based on the previpous insert, table I & K have to be populated as well. If there's an error on th...more >>

Why doesn't the query return two rows? create table A (col1 char(1)) create table B (col1 char(2)) insert into A values ('x') insert into B values ('x') insert into A values (NULL) insert into B values (NULL) select * from A join B on (A.col1 = B.col1 or (A.col1 is ...more >>

I have a very strange problem with my application. I am using sql-dmo to build an online enterprise manager. We are currently doing a stress test on it (We created about 250 databases and have some users login using IP). However, we are getting a problem intermittently. What happened is that...more >>

Hi, 1.My application uses sql server 7/2000, I release new versions of the app quite often and they are accompanied with db changes (usually adding new fields and sometimes adding triggers and SPs). What is the best way to send db updates to customers? 2. How to get description of tabl...more >>

bcp "select top 1 [data] from data_table" queryout c:\temp\x1.zip -n -T The above is creates a zip file from data_table.[data]. However the zip file appears to be corrupted (and was valid when I stored it). What is going on and are there better ways to extract data from a text field and ...more >>

Trying to insert into a field that is varchar(20) but I want to pad the entry with "0" on the beginning of it... so if the item to be written is 10 chars long I want to write 10 "0" on the front of the item.. how to accomplish? Thanks -- Austin Henderson <>< Network Administrator ...more >>

Looking to create a flat file from a stored proc that is comma delim.. can this be done? I know I can use DTS but the table is a temp table and that wont work. Thanks for your help in advance. -- Austin Henderson <>< Network Administrator ...more >>

Hi, I'm about to write some IVR scripts where I need to validate some data against a database located on a Tandem machine. My IVR only supports ODBC data access, and the data I need to validate against is only accessible through a SOAP interface ... I do not know anything about SOAP, but I...more >>

I'm having a problem using the USE statement in a script. Here's the code: --Create the database. CREATE DATABASE DIOMS ON PRIMARY (name=DIOMS, filename='C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL$NEWSQL\DATA\DIOMS.MDF', size=10MB, filegrowth=10%) LOG ON (name='DIOMSLog', file...more >>

Hi there! :O) I'm wondering if there is a way to insert a stored procedure into a database using code? We've made a script that generates all our "select" stored procedure since that had a little something special that the wizard couldn't do. Is there a way to insert each stored procedure into...more >>

Hi If I'm using an UPDATE statement to change some records, but the changes will cause key violations, the entire update will fail. I want it to change the ones that can be changed anyway, and just skip over the violating ones. example id1 and id2 together create the primary key Id1...more >>

how to select one column, two rows as a string, without using a temp table or cursor desired output is 1,2 create table #test (f1 char(1)) insert into #test values ('1') insert into #test values ('2') select * from #test ...more >>

Hi folks. I'm having a data visibility issue when using distributed transactions but I'm not sure if this is normal or I'm doing something wrong. I have this object hierachy that is going to be saved as one transaction. Each object has a Save() method that creates a SQLConnection and executes a ...more >>

I've taken over ownership of a database that used to reside on an Australian server, but has since been copied over to one in the US. I added a new table and am trying to run a query that joins an existing one to the new one, and I'm getting this error: Cannot resolve collation conflict for e...more >>

Hi, I'm trying to convert a varchar value to a varbinary and I'm getting some strange results: This works as expected: print cast(255 as binary) returns: 0x0000000000000000000000000000000000000000000000000000000000FF but this statement: declare @test varchar(3) select @test = ...more >>

Hi I have the following query... Declare @sql varchar(1000) Set @sql = 'SELECT a long query... ' Set @sql = @sql + ' more long query... ' EXEC master..xp_cmdshell 'bcp @sql queryout c:\temp\Authors.txt -c -Sxfiles -Usa -Psarules' obviously it is not working...what am I doing wron...more >>

Hello all, I'm having a hard time implementing row-level security in a MS SQL Server 2000 database. I have now implemented a view (with the help of some readers in this forum, thanks!). This view grants the user access to a limited subset of the records in my table, it looks a bit like thi...more >>

I don't know if this is the right newsgroup, apologies if not, but can someone explain to me the following... If I type in... SELECT dbo.umc_Messages.mSender, dbo.umc_Messages.mDateSent, dbo.umc_Messages.mRelation FROM dbo.umc_MessageTransactions INNER JOIN ...more >>

Hi all, I am trying to insert some records into one table from six. How do I update my fact table with the data from the six other fact tables distinctly? The tables will have duplicate data so I'm only trying to get the missing records....more >>

How could you implement the XOR operator in SQL SErver 2000? Rick....more >>

It would be helpful if anybody can provide me a set of standard queries/stored procedures/UDF's to do all standard operations on a table with parent-child format i.e. a single table containing a key_id and a parent_key_id. Some of the operations are: 1. provide all the childs of a selected ...more >>

I create an index on a temp. (#aaa) table but when I tried to look it up in sysindex, it said it did not exist but when I re-run the create index statement, it said, it is already created. Which system table can I use to look up more information about this index? Thanks in advance for you...more >>

Hi, Please Help me ! Very strange situation I'm using the system stored procedure sp_spaceused <<TABLE NAME>> and the procedure is returning me a number of rows wrong ! ! ! for example TABLE - A - select count(*) from a - 3495 - SP_SPACEUSED A 3493 he is calculating a wrong number of rows,...more >>

I'm trying to use DMO's BulkCopy to create a format file so that I may export table data from one instance of SQL 2000 and import the data into another instance of SQL 2000. Everything I've tried will not create a format file; I get SQL-DMO unspecified errors when I spec a format file. I've ...more >>

Hello, I have an association table between my buildings and my occupancy classes because multiple classes can be defined for one building. My table is like this: BldgSec_ID Occup_Class 1 A1 1 B1 2 C3 2 F4 For a report, I am...more >>

Hello, I need to retrieve data from an nText field. There may be no data in the field for one row or there may be several k of data. I was trying to use the Len and Left functions but got an error message that you can't use nText with these functions. Is there a way to retrieve only 255 ch...more >>

I want to write a trigger that modifies the new values (after the updated) based on what the fields contained before and after the update. I know how to select from "inserted" and "deleted", but how do I modify the new values? The "inserted" dataset isn't updatable. Lars Dybdahl. ...more >>

I have a software developed by someone else in VB. It stores the data in SQL Server. On a screen on one field it shows the data in string form. But when I go in the back end (SQL Server) and read that data it shows "binary". It's datatype is binary. In SQL Query Analyzer I want to compare whet...more >>

Hi, I have a table with two columns which is as underL Table Name : test Plant Qty 20 10 30 40 40 50 50 60 ... .. I want an sql that would give an output: Plant Qty Plant Qty Plant Q...more >>

Hi: What is the structure of a SELECT clause where the table is located on an external server? Suppose: Server Name = SomeServ Database = SomeDB Table = SomeTable Thank you. Rick....more >>

I have the following that reads a csv file into a table. How can I add multiple csv files into this one statement. Like for test1.csv, test2.csv, test3.csv...etc. BULK INSERT Northwind.dbo. [acstest] FROM 'c:\test.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ...more >>

Hello, I use Access forms to update already saved data on SQL2k. At 'SAVE', a window pops up stating that an other user has changed the data and suggests to save the changes in the Notepad. But, it doesn't save it to the server. Saving new data into a new row, works fine; it only happens when I'...more >>

I was wondering if there is a way to build a procedure passing as arguments the name of the table and the record Id. Since record id is always in first column can i make someting like the following? ( @tbl nvarchar(20), @rcd int ) As set nocount on select * from table = @tabl...more >>

My database .MDF files is about 38 MB, however my .LDF file is 2.2 GB. How can I 'truncate' the transaction log down from 2 gigabytes. I have tried SHRINK with and without move pages / remove free space. I have done a complete DB BACKUP, no luck... any ideas.. Thanks K ...more >>

Hi, I currently have two tables Table1 and Table2, and two databases Current and Backup. Table2 has a foreign key relationship with Table1. I want to insert some rows from Table1 and Table2 from Backup into Current. I know I can insert multiple rows from Backup's Table1 to Current's ...more >>

Hi all, Does anyone know if there is a program to reformat SQL statements for SQL Server? Thanks Allan ...more >>

I have an sp that returns me a table with 1 column and one or more rows. I pass to my sp a scalar HEX value EXEC ExtractOnBits 0xC00000000000000000000000000000 (very thanks to Dan Guzman to build this sp) returns me: Result ------ 1 2 Now I have a table of HEX values (or better...more >>

Is it possible to get a result set like with SQL: ID Pos Number =20 ------------------------ 344 1 1 456 2 654 3 835 2 1 978 2 from the table ID Pos ? =20 Thanx! // Martin R-L...more >>

Does anyone know what I need to do to make this query return only one value. Thanks in advance for any help. Sandra UPDATE BTR_RENTAL_UNIT SET RU_USAGE_TYPE_ID= (SELECT RU.RU_USAGE_TYPE_ID FROM COM_RU_USAGE_TYPE_LK RU RIGHT JOIN BTR_SAP_TENANCY S ON (RU.RU_USAGE_TYPE_NBR = S.RU_USAGE_TYPE...more >>

Hello, i'm struggling with a stored procedure that will make sort of crosstab from one table an insert into an other. The source table: PK FK VALUE 1 1 aaaa 2 1 aaaa 3 1 aaaa 4 1 bbbb 5 1 cccc Destination table has the folloving columns One row in...more >>

Hi,all Oracle can produce a unique session ID for every connection to database and it can also tell users whether a connection is active by providing a function using session ID as a parameter. So can SQLServer provide such the same function? If can, please tell more detail. Thanks begin...more >>

Hi, I don't want duplicate information in a field of my table. I don't want to make it a primary key. How would I go about denying duplication in that field. Would I create a Rule? If so, what is the syntax to check for duplication? TIA, Eric...more >>

I need some help with a T-SQL statement. Any help would be greatly appreciated. Database has one table with two fields: FileName & FilePath. FileName and FilePath individually are not unique, but together they must be unique: FileName FilePath 1 "DailyReport" C:\......more >>

How can i put this: SQL MAIL Sucks. I never got it to work after many Win2K Svr, SQL2k Svr reload, Office 2000, Outlook 2000, Office XP reloads.....Forget it. Put IIS/SMTP Service secured as a relay from 127.0.0.1 ONLY. IN SQL use: sp_OACreate to create a CDONTS mail object and send away...more >>

I have a table where I have a column type_id with identity set=1. For each s_id,a_id I need to identity should increase by 1 when I run the insert query. For some reason the query take the max(type_id_ regardless of s_id,a_id. Please help me modify query. INSERT r_type(r_type,s_id,a_id) ...more >>

serverA : sql 7.0 serverB : sql 2000 I upgraded dbs from 7.0 to 2000 on serverB. now, trying to run the query (please see below) on serverB is giving me the error message: select a.* from openrowset('SQLOLEDB', 'serverA';'sa';'pwd',' select DISTINCT BUSINESS as [Business Unit],count...more >>

Can anyone point me to information on how to write an extended stored proc using Visual Basic? We have a project which will need to build an interface between an IBM MQSeries queuing server and MS SQL 2k. TIA Ron Lounsbury ...more >>

Is it possible to pass a parameter to a User Defined Function when it is included in the From clause: select cust.* from myCustomerTable cust, MyUDF(cust.customerID) ==== I know that it works in the Where clause: where CustMyDate = dbo.MyUDFDate(getdate()) ==== It also w...more >>

Hi there, I am converting an Oracle system to SQLServer. There are loads on instances where, on an insert, the Oracle system has triggers that set various NOT NULL values depending on certain conditions. The client side does not specify these columns during insert (since it does not know the ...more >>

hello Anybody knows if is it possible to create a relationship=20 between 2 tables on different databases? If it=B4s possible, how can I do that? thanks...more >>

Can arrays be passed to the stored procedures?...more >>

Hi i have created a stored proc but have never created one where two values are available to search by but only one will be used to search the database can anyone see why my code doesnt work please..thanks in advance for any help create proc spsearchsicodearea @siccode varchar @area nvar...more >>

SQLSERVER 2000 Sp3, Windows 2000 AS I have written a stored procedure which uses a while loop to run though a backup file and generates the Restore commands and brings a DR database up to date. The transaction file is appended to during the day on the Production system. The TRN file is co...more >>

Anyone know of good sites for sql batch processing guidelines / batch processing patterns? ...more >>

hey guys can u tell me where should i use stored procedure and where should UFD's? ...more >>

Hello there I have table with data of date customer and money. I need to build query that summarized the total money of each client. If the number of customers is bigger then 10 i need to show 9 customers with the highest money and the rest of then as Other For example: a - 100, b-20...more >>