It appears that RAISEERROR cannot be called from a constraint? If this is the case, how can I display a custom message when validation fails? I know I can do it with a trigger, but in my case it will be much harder to do with a trigger. It seems an unnecesary restriction that I cannot call RA...more >>

Hello, I am in a position where i need to create a query where i must join two tables on the nearest value. Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in tbl_Time, I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest to, but less than the value of tb...more >>

Hi guys i've a problem a sql server begin the service sqlmail with profile (testing perfect), but when send my email with xp_sendmail or send an email with outlook so: Algunos de los destinatarios no recibieron su mensaje. Asunto: hi, this is a sample Enviado el: 09/07/2003 0...more >>

I'm new to SQL Server programming (I've been using Access) and wanted to know: How do I put an apostrophe in a string, if the apostrophe is the string delimiter? TIA ...more >>

I would really really appreciate help with this issue. I have an accounting database which has tables which have the cpnyid field and the subaccount field. I am trying to update the cpnyid field in the tables based on the first character of the subaccount. For e.g Where the subaccount is...more >>

I am trying to insert a semi fixed length file into a SQL database. Does anyone know of a way to query a fixed length file like you can a csv to only insert the records that meet certain criteria? So far, I have had success using vbscript to open the file and then parse out the lines, but I am ...more >>

The documentation seems missing on this topic: Does a trigger gets called once per each row updated/deleted/inserted, or only once per operation (say if I do a several row deletes on the table within a transaction frame) If so, does that mean that the virtual tables 'deleted' and 'inserted' ...more >>

I have imported some data form XL that seems to have attached .0 to each INT .... is there a way I can replace this .0 with nothing? ...more >>

I'm certain this has been asked 1000 times.. however ... I recently imported some data - all INTs ... from an excel sheet, but the damn thing taacked on ..0 to EVERY value. Is there a quick way to remove the .0 ?? ...more >>

Sorry for the length. Using: CREATE TABLE [EventLog] ( [DatabaseName] [char] (30) NULL , [EventTime] [datetime] NULL , [EventDesc] [varchar] (60) NULL , [EventType] [varchar] (20) NULL , [UserName] [varchar] (15) NULL , [Version] [char] NULL ) INSERT INTO EventLog Values('d...more >>

I'm confused on the difference between RPC and SP. In a trace I set up, I track both RPC:Completed and SP:Completed. When I run an app that makes a stored procedure call, there is a RPC:Completed entry and an SP:Completed entry for the stored proc. However, the SP:Completed entry has nothing f...more >>

When I open a SQL-Server 2000 table in VB60 the recordset-property returns -1 when there records in the table. I don't want to use cursorlocation=adUseClient. because this gives troubles with my floating point numbers. If I use the cursorlocation=adUseClient it works fine, but if I use Access20...more >>

Hi, I am trying to find out an alternate way to implement the First() & Last() functions (MS Access) in the SQL SELECT statement of SQL Server. Here is the actual problem I am facing: I am having a table Table1 Name SeqNo Nam1 4 Nam1 5 Nam1 1 Nam2 10...more >>

Hi there, I have a url_address field (varchar(300)) Some records has a trailing slash (/) , some not. For example: http://www.cnn.com/ http://www.cnn.com Is there a way to UPDATE the table to remove the trailing slashes for the relevent records? ...more >>

Hi all, 1. Select * from table where ADEDATE = '23 June 2003' in SQLServer2000 This one works fine immediately. 2.Select * from table where convert(char(10),ADEDATE,121) = convert(char(10),getdate(),121) This takes a lot of time and also the consumes the complete resources of the...more >>

Howdy, Is it possible to use a table located in one SQL database in another database? I have two databases, DB1 & DB2 DB1 has a table named T1 I'd like to use T1 in DB2, (as well as DB3, DB4, etc...) Is there a way to do this? Is it possible to create a view to do this. Thanks, Ja...more >>

I would greatly appreciate some help, or a point in the right direction, please. I am new to MS SQL Server administration, and specifically scripting for MS SQL Server. I want to create a SQL script that would list the names of all databases to a text file, then I want to use a loop-statement...more >>

SQL Server 2000 If I run the following query using Query Analyser from the 'testdata' database the OBJECTPROPERTY of user tables is 1. But if I run it from another database (which is what I want to do) user tables (and some others) return a null value. select OBJECTPROPERTY(id, N'IsUserTabl...more >>

I have a couple questions regarding understanding Scan profiler output. 1) Sometimes the IndexID data column will contain a value that does not correspond to any index on the associated table. Why is this? 2) How can I tell when a table scan is being done? Thanks very much. ...more >>

Hi ! How to convert decimal value to hexadecimal ? thx ...more >>

Im using a query with OrderBy clause to populate a temp table. The Query works and Order and everything is Fine But when reading the data out of the temp table, some of the rows are positioned wrongly??? ie in the wrong order 75% of the ordering seems fine but a few rows are in the wrong places ...more >>

I have a user defined table with a variety of information with a variety of meanings. Each record has an identifing key from another table associated with it to tell me what the data means. Problem: I need to pull out 5 groups of informaion from the user defined table that links to tabl...more >>

I use OLEDB to insert data into a table, but I get an DBSTATUS_E_PERMISSIONDENIED when inserting data into an identity column. I guess this happens because the IDENTITY_INSERT is disabled (by default) for the table. Not all of my tables use identity columns, so I want to do something like this: ...more >>

How do I reposition a column through SQL (Without using enterprice manager). In a table I have a column ServiceID at column number 5, How do I move it to column 2 Thanks ...more >>

Using the following schema is it possible to write a query that will return this data in a single row? TABLE SCHEMA: CREATE TABLE [dbo].[Items] ( [ItemID] [int] IDENTITY (1, 1) NOT NULL, [Name] [varchar] (32) ) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemAttributes] ( [ItemID] [int] NOT ...more >>

How would I create a system table or stored procedure? ...more >>

UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE I need to update a table with distinct sums from another table. I will obtain my sums from a table dspCDRv1 and the relevant fields in that table are BrandID, xCountryCode, CallDate, Duration. The Table I am going to upda...more >>

While excuting a stored procedure in Query Analyzer I update a table over 2000 times. I have a commit after each record processed. At about row 2000 I box appears stating "Resource low, some results are dropped" What does this mean and what do I do to fix it? Thanks Otto...more >>

We have a process which one run by a only a single client takes 93 seconds. I run this test from query analyzer. The SQL Server is installed locally and is running on Quad processor each of 1.5ghz with hyperthreading. I next create 7 sessions in query analyzer and run this test concurrently f...more >>

Hi ! There is any function in T_SQL to convert values in Hexadecimal to Decimal / Thx ...more >>

Hello, Recently, I am trying to optimize some old stored procedures. In the process, I found out in the online help that a "table variable" is supposed to perform better than temp tables. However, I have found exactly the opposite. Whereas using temp tables, I can obtain query results in less...more >>

I have a query running inside of Coldfusion webpage. query blah cfoutput query blah { query another_blah { cfoutput another_blah { calculations } } } Problem: You cannot have nested cfoutputs. Is there a way in SQL to do those calcu...more >>

Can it have dependences without a relationship among the tables? ...more >>

Do you relate look-up table in sql server 2000? Example: MEMBER TABLE STATE TABLE MemberID StateID State State Would you relate these two table if you only need the state table to serve as a drop down list of states to complime...more >>

Can someone please give me a hand with this... Here is what I an trying to do. I have two tables (Table1 and table2) I want take the data from Table2 and replace the data (row) in table1 but only if field1 matches. It seems easy but I just cant get it. Thanks in advance for any help....more >>

Does anybody have a suggestion for the best way to send email from a trigger? Is it even possible? I'd like to send a notification everytime a new entry goes into a table. Thank you....more >>

Where can we find the index (list) of all the Microsoft KnowledgeBase Articles (KB Articles)on SQL Server 2000. Under the "Search Knowledge Base" link at the support.microsoft.com, we can search on the basis of KnowledgeBase Article ID or Keyword. But this only gives the KB Articles focus...more >>

Dear Friedns We have purchase a new office which is in another city and far away from our office. We want to make the same as our back-office or Branch but we want that all the users should use only one database. Please suggest what is the best way out to achive this target. your gu...more >>

Hi, I have orderTable, where I have orders of products for each country. I would like to create a select statement, which returns the quantity of orders for the current month and current week. Something like that: SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity FROM...more >>

i wanna delete some files in sqlserver jobs which files name are feteched from the database through t-sql store procs. ex: select 'pic'+picIdx as filename from PicData how do i manage to do that? ...more >>

Hi group! I'd like to know the following: If I define a view that JOINs several tables, then query this view and only select a few of the columns so that not all tables are needed, will the JOIN still be done with all the tables? The point is, sometimes I only need a few columns (not always t...more >>

I have a website with some asp pages running off of a MS Sql db. For reasons that I can't explain, whenever querying the database with a SELECT query containing an order by clause, the returned recordset is read-only, regardless of cursor or lock type. This seems to be specific to the host...more >>

Dear Friends My company have started a new office in another City which will work as a backoffice for our comany. Hence we need that all the user of backoffice should work on the SQL database of our server. Or the data from both the end should remain update at any particular time. Ple...more >>

Hi why do I get a syntax error near '[' for this trigger CREATE TRIGGER [t_fox_TIMERu] ON [dbo].[fox] FOR INSERT, UPDATE, DELETE AS DECLARE @foxtimer datetime SET @foxtimer = DATEADD ( hour , 5, (SELECT MAX(TIMER) FROM fox) ) INSERT INTO fox (TIMER, ADDRESS, EMPLOYEE, EMAIL, URL) VA...more >>

Hi, Is there a way to select all user tables? For example, I created 5 tables. In SQL analyer, I want to select table name and strutures of tables that I have created. Thanks ...more >>

How can I make the new column to list number in sequential order based on the value from another column. In other words, I would like it to look like the following: column 1 new column -------- ---------- a 1 a 2 a 3 b 1 ...more >>

I have a stored procedure that calls another stored procedure. The second stored procedure will run and update the correct data - it just never returns and proceeds with the stored procedure that calls it. If I execute the stored procedure through Query Analyzer it will work correctly ever...more >>

Hi, I have a more theoretic question: I have two huge tables (db1.dbo.table1 and db2.dbo.table2). I need to join them. Will the effort to join both tables be the same as if both tables were located in the same database? Maybe it even performs better when I copy db1.dbo.table1 to db2 before ...more >>

Hi, all. Is anybody currently using a web-based tool to add/edit/delete records to SQL Server 2000 tables? I've been writing my own ADO.NET & ASP.NET code, but surely I don't have to do all this myself! I played around with a product named ASPRunner; they're exactly what I need to do, but...more >>

I need to convert a numeric filed NUMERIC(14,3) to a string with a (european) regional currency formatting like "1.234,56". How can I achieve this, please ?...more >>