Any idea about order in which values are set in an update query e.g. field1. = value1 , field2 = value now if field1 is set first and then field2 (logically yes but I have seen varying patterns This sequence is particularly important....more >>

Hi, I use c#, xml and asp.net to write a search engine to retrieve data from multiple tables in a sql 2000 db. I need to convert those data to access and excel for internal use. How can I do that? I searched msdn and found upsizing. But, it seems not suitable. Thanks for any ad...more >>

I have a real-time application that sucks data from heavy equipment every second, then inserts it into a SQL table via a massive INSERT statement. Presently it inserts about 800 fields per second. I am interested in making this as efficient as possible, to lessen the load on our SQL Server (becau...more >>

I have written a C# .NET application that uses Decision Support Object Type library 5.1 to access an analysis server. The problem is that when the application ends (and all processes complete normally), I get a fatal error: "The instruction at 0x.. referenced memory at 0x... The memory coul...more >>

I have a Delphi application with one connection on the datamodule to the server and many TQuery connected to that connection. Now when a query does an insert I use anonther query with this statement: select scope_identity() as id to retrieve the identity. I tihnk this should work but it al...more >>

Hi I have a table with a varchar field (not datetime) where I store dates (and other values) I may have something like this: PropValue ---------- 12/10/98 test data 10/10/2003 1/2/91 test2 8/11/1999 I would like to run an Update command that converts that to this: PropValue -...more >>

Hi all, Anyone know how to select a number of days either before OR after GETDATE()? The following works for a positive number of days (days after GETDATE()), but not for negative number of days (days before GETDATE()): 'WHERE days BETWEEN GETDATE() AND DATEADD(day, -30, GETDATE())' T...more >>

Can any of you who have started or evaluated usage of SQL Server reporting services please share your experiences. How come there is still no thread on microsoft.public.sqlserver.* on Reporting services will it be implemented at some stage. Regards Simon...more >>

Can you please share with me if you are aware of any good books on SQL Server reporting services. Many Thanks Simon...more >>

Can anyone show how to test for the presence/absence of "hits" from an sp_executesql command? Failed idea shown below; the exec returns a result set, but SQL won't allow testing for it as if it was just an "if exists (select ...)" execution. Thanks for your help! USE Northwind DECLA...more >>

I have table:DateTable with startDate and endDate columns. Here is the view, which returns all free time periods: CREATE VIEW FreePeriods (start_dt, end_dt) AS -- free time between periods SELECT MAX(D1.endDate), D2.startDate FROM dateTable AS D1 INNER JOIN dateTable ...more >>

Does anyone know if there are resources from MS or white papers or anything regarding 1. SQL Server audit trail functionality, an 2. SQL Server compliance with FDA reg 21CFR11?...more >>

I'm trying to order a result set of addresses by the digits of the home address on that street. For instance: 245 Main Street 123 Elm Street 876 East Street would be returned to me as: 123 Elm Street 245 Main Street 876 East Street At this point I'm not concerned about what may fol...more >>

Below is an SQL statement I am putting into a string and using the Execute method in VB. When I run it, I am getting "Incorrect syntax near the keyword 'INNER'." Can anyone help? I cannot see anything wrong. Thanks. David ...more >>

2 Machines not part of a domain. They are running under the default WORKGROUP MS SQL 2k SP3 MS Win 2k SP4 All scripts that rely on the use of the SQL Server Agent Proxy Account are now failing on both machines. The account under which the Proxy was using is a local account (since I am n...more >>

Hi, how can I remove IDENTITY property from a column using SQL script? Thanks JP ...more >>

I need to access a table located in one database from a stored procedure located in a different database. I do not have the same user name on both databases. ...more >>

I need to simple call an SQL stored procedure from VB6 that doesn't return records. The stored procedure looks like this: CREATE procedure addtoaudit @user [nvarchar] (15)=null,@date datetime=null,@timeoday [varchar] (20)=null,@text [nvarchar] (120)=null as set nocount on insert into audit...more >>

Hi, I want to change my user account from Windows Authentication to SQL Server Authentication, but SQL Server Authentication option and password is disabled, I can only choose Grant Access / Deny Access. I tried it under my own account (system administrator role) and sa, both have the same ...more >>

I have the following pseudo sproc: DECLARE @ERR1, @ERR2 BEGIN TRAN DELECT FROM myTABLE -- discard unneeded records SELECT @ERR1 = @@error INSERT INTO myTABLE(FIELD1, FIELD2) VALUES (1,2) SELECT @ERR2 = @@error IF @ERR1 = 0 and @ERR2 = 0 COMMIT TRAN ...more >>

Hi all, How do I Bulk Insert and skip the the first Column in SQL table? The = first Column is IDENTITY Column. I have read alot but I'm confused about a format file. = Ok, if I need to skip the first Column how do I make the format file? I also read = about Bcp Utility, If using this what ...more >>

Please Help! I just ran an update statement against a table of approximately 9000 records. I forgot one entry in the WHERE clause and it updated the wrong records! Is there any way to roll back the transaction even if I didn't use BEGIN TRAN / COMMIT TRAN? This is an emergency, so an...more >>

I have a SQL database on my local machine that I'm using to develop a process. The process works great, but in order for us to get it running on the main server, we are going to have to do some reconfiguration that will take a while to complete. In the meantime, I would like to be able to run a s...more >>

When I used "SELECT INTO TEMP", it does not work. But if I used "SELECT INTO #TEMP", it works fine. Why? Thanks in Advance, culam ...more >>

I have a data access page created in Access that uses a SQL Database. When the page is launched it requires parameters to be entered for 3 fields. Is there a way to have % signs be used automatically if you don't enter data in the value field? Thanks...more >>

Compiled a stored procedure containing a select statement that points at a view on a linked server (link using sa). The select statement works fine in QA outside of the SP, but when I try to execute the SP, I get the "remote access not allowed for Windows NT User activated by SETUSER" error. MS ...more >>

I'd like to include a boolean/bit column in a recordset that compares two dates (after some manipulation). I get syntax errors (around '>') when I try something like this: SELECT DocName, (GETDATE() > DATEADD(m,9,AuthorDate)) AS TimeToDeleteMarker FROM Documents WHERE ... How can I do some...more >>

any examples of stored procedure to import delimited text file without the use of bulk insert or DTS ...more >>

Sample Data ------------ RID GID STUFF ------------------- 125 100 abc 125 101 def 125 102 ghi 125 103 jkl 126 100 abc 126 101 def 127 100 abc 127 101 def 127 102 ghi 127 103 jkl 128 100 abc 128 101 def ...more >>

if I have a varchar(255) column, and I want to search for any of a few string combinations (words)...how can I do that? For instance, if the item I am looking in in "abcdefghijabc", and I want to look for "ab" or "d" or "z"...it would return true. If I searched for "z" alone, it would...more >>

Say I have a table TestName with a column Name Table : TestName Name ----------------- David Jimmy Mathew Kemp Justin I need a query to combine the first character of each name and display in one column. (using a single select statement) For e.g. the desired output from above tabl...more >>

There is a limit on being able to quickly edit the likes of varchar(8000) fields in EM - 1024 characters I believe. I would simply like to remove this limit - how can I do this? BTW I've seen the postings with the advice that you "shouldn't" use EM this way. However I fail to agree with tha...more >>

Hi SQL Gurus, I have an application where the end user insist that there should be nine(9) databases, same server within the system. There are 'shared tables' on one of the database where any insert/update/delete on those 'shared tables' must appear immediately on other databases. In this ...more >>

Hi All, I have been asked to update the status of tickets in our sql database automatically when an email (meeting certain criteria) is received a specific email address. I have never done anything like this. If anyone else here has, I would be very grateful for some assitance with how and where...more >>

I would like to have a current timestamp or datestamp inserted whenever an update is done to the record. Is there a data type that I can use to give me that value automatically or do I have to write code to do that? If there is no default data type for that purpose, what would the easies...more >>

Hi all, I've made two user-defined function to encode & decode some values according to my needs. This function are rather simple, basically it's all about storing a value between 0.0 and 1.0 into a smallint : encode : Convert(smallint,Round(@p * 65535 - 32768,0)) decode : (Convert(floa...more >>

How can I round the time of a datetime column to display in 15 minute increments? For example: 02:55 AM = 03:00 AM 03:06 AM = 03:00 AM 03:08 AM = 03:15 AM I don't care about the seconds/milliseconds portion of the column. Ideas? Brandon -- "In the beginning the universe was create...more >>

Hi, I'm creating a script that checks to see if a database exists, and if it does, changes to it, removes the primary user, then changes back out. Problem is that I have a "USE" statement in there, which SQL server seems to parse before running, so the query always errors when the database doe...more >>

Is there a way to insert data into a table on SQL server from an Excel spreadsheet using OPENROWSET, if so do you have an example? ALso what if we are talking about 10k records or more, will this be a very slow process? I don't want to use DTS packages because i would have to create them ...more >>

Hi, I have a couple of sql statements which updates rows in tables. The statements take pretty long as I am trying to update millions of records. Is there a way to find out which the sql statement that is currently executing? If I do sp_who it only shows me that the command is an updat...more >>

I had about a week worths of diagram work, then I did a backup of my database, and moved it to a new server, and on restore I see they are missing, and all the built constraints are gone with them. Is this expected behavior? Or did I do somthing wrong? JIM ...more >>

Please give me some good site to refer for basic to complex programming of SQLserver issues. thanks...more >>

Has anyone built a simple interface to monitor and run SQL Agent Jobs from Microsoft Access? I have built several SQL Agent jobs and would like the ability for operations to be able to easily kick these jobs off if needed and monitor if they succeeded or not. Spencer Tabbert...more >>

Can anyone say if it is possible to set up and use a variable as a table (or procedure) name in a query per the failed attempts below? Like so much else, seems like it oughtta be supported, but... declare @facilities_table_name varchar(30) set @facilities_table_name = 'inventory.dbo.faciliti...more >>

Hi, How can I find the maximum value in a table? i.e. suppose I have a = table, table1 which has 3 columns all of type integers. I want to find = the maximum integer value in the whole table. TIA...more >>

All, I am new to triggers hand have just written the one below. Can anyone see any obvious 'newbie' mistakes or inefficiencies? The Header should explain the function. /*************************************************************************** ** @BusinessLogicTrigger: bl_UserProfile_...more >>

I have created a stored procedure as described in article Q312839 and it works. However the message body is truncated.It seems that the message body is limited to only 256 characters. Is this correct? Is their something I'm missing or is there a work around. Thanks Bill C....more >>

Is it any way how read result set returned by SP? Thanks ...more >>

I have an application that has a table that contains multiple small image files. This table now needs to have it's images archived, but I wish to have the rest of the record remain. I have set the image field data to 'null', and then proceeded to shrink the database. No additional free space was ...more >>

What is the best/fastest way to convert computed columns to regular columns thanks ...more >>

hello all I want to create a webpage of list of items, each one is a link to its details. I mean that the one reading the page can click to view the details of each item. any solution for me using sp_createwebtask, is there any other tools that can help? knowing that I need to generate my html...more >>

We are using SQL server 2000. We are trying to use the stored procedure debugger in Query analyzer. When attempting to run we get the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified ...more >>

hi, i have seen many posts saying that you should not program with cursors because they are slow and klugy. but to me a while loop looks like the same things as a cursor. is this true? AP!...more >>

Any info on the order in which upadtes in update query takes place ...more >>

There is a requirement for me to convert ansi data into Japanese code page ... I have one logic to do this as follows . /********************************************************************* CREATE FUNCTION dbo.ansi2uni_fn (@AnsiStr varchar(8000) RETURNS nvarchar(4000 A BEGI ...more >>

Hi All, I want to send Alert msg to the client when ever there is a new record added to the Table. I create an Alert with Error No 50002 and running it by using Raiseerror in After Insert Trigger of the Rable. But the message window is appearing with all informations like error no etc. How ...more >>

How can i separate this string into individual strings "abc,def","ghi","j","k,lmn,",",a","b","c" abc,def ghi j k,lmn, ,a b c appreciate any help thanks...more >>

I have a varchar column that has a lot of multi-line entries. I was wondering if it was possible to strip the hard returns out of the values to make them all single line. Any help would be appreciated. Thank You, Alex Marram ...more >>

When i try to start debug in Stored Procedure SqlDumpExceptionHandler: Process 71 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. Somebody help me ?...more >>

There is a requirement for me to convert ASCII data into Japanese code page ... I have one logic to do this as follows .. /*********************************************************************/ CREATE FUNCTION dbo.ansi2uni_fn (@AnsiStr varchar(8000)) RETURNS nvarchar(4000) AS BEGIN ...more >>

Hi Is BINARY_CHECKSUM function returns negative value? Unique checksum value is return by this function, if i remove the minus from the value, is it cause problem in uniqueness? Thanks & Regards Raja Balaji...more >>

There is a requirement for me to convert ASCII data into Japanese code page ... I have one logic to do this as follows .. /*********************************************************************/ CREATE FUNCTION dbo.ansi2uni_fn (@AnsiStr varchar(8000)) RETURNS nvarchar(4000) AS BEGIN ...more >>

In a SAP MCOD installation several installations of the SAP products are done into the same database. You can see which product is involved by looking at the owner of the objects (this is the name of the instance). If I want to delete a SAP installation all I have to do is delete all objec...more >>