H I have several batches.If an error occurs in one of these batches during execution,all batches run except the on that error has occured in it How can I force it in the case of error that none of the batches execute thanks...more >>

My question concerns the very end of this post, the select statement.. -- insert data to production tabl insert Stock_Quote_Dat Symbol [Date] [Open] , High [Low] [Close] Volume select Symbol = substring(s,1,3) [Date] = convert(datetim...more >>

is anybody here into MySQL too? am planning a BLOB database and need some assistance ...more >>

Hi all, I have a theoretical question for you SQL design masters... :-) I need to design a schema for the following scenario: There are users, users are uniquly identified by an ID (generated in another system). Users choose personal preferences like favorite sport, favorite food etc fro...more >>

Hello All, when I try to execute the following procedure exec SP_ADDROLEMEMBER 'Public', 'NTA21178\IIS_WPG' I always get an error message Membership of the public role cannot be changed Why is the error message comming? If I add the user to another group like db_owner, then the user...more >>

Hello All, I created a role in my Database called 'web'. For table and stored procedure I create I grant execute and 'select, update, delete, insert' permissions for the group web. now I add a user called IIS_WPG to the group 'web'. When I go to the enterprise manager and view the permi...more >>

Can someone give me a kick here? What am I doing wrong here? I get an error: Incorrect syntax near the keyword 'INNER' I'm trying to update ItemPrice fields with values from PriceReport thanks ---------------------------- UPDATE ItemPrice SET ItemPrice.StPrice = PriceReport.ST, ItemPrice.ProPr...more >>

Hello all I'm having some trouble with an aggregating query and was wondering if anyone here could help me out. I have built 2 individual queries, but want to combine these together (which is proving to be the difficult bit) The queries purpose is to look at the OrderDetails tables, and the ...more >>

Hi Im using ADO's connection object to BEGINTRANS , COMMITTRANS, ROLLBACKTRANS commands to institute an "Atomic ALL OR NOTHING save" . What i do is open my ADO connection object, call ADOCONN.Begintran and then i pass this connection object to every function that writes to my database for ...more >>

On table structure, is there an advantage to having two or more columns making up the PK vs a new identity column for the PK Also, if I have a one to one relationship between two tables (say one table is a subset of the first), should I have a new identity column in the smaller table PK Let me k...more >>

Hello, here's my problem: I have a user defined function in my db - sql server 2000, named dbo.get_Id_Order. I want to call this function from a vb.net subroutine, passing the two parameters: can anyone tell me which is the right sintax? Thanks Francesca ...more >>

Hello All, Is it possible to add a database diagram to a sql server database by means of an sql script? I am having a script which creates the entire db schema. What I would like that script to do is to create a new database diagram as well, so that people can understand the schema easil...more >>

Can someone direct me to a foxpro or DBF newsgroup. I can't seem to locate one.. thanks! ...more >>

I use xp_sendmail to send a link to our clients. But the link is very long. How can I send an html message using xp_sendmail? Thanks for your help, John ...more >>

Hello All, I have created and .sql file to create the database schema. I also have a lot of .sql files to create stored procedures. Now I am writing a batch file which will create the schema and stored procedures on an sql server instance. The file I have written is OSQL -E -Udomain\use...more >>

Hi, I want to check if the user( or group) is existing, how to check? thanks. IF (User is existing) EXEC sp_denylogin 'BUILTIN\Administrators' ...more >>

Can anyone reccomend a tool for comparing the structural (not data) differences between 2 databases? I don't need anything elaborate. I could roll one myself using the sysobjects table and the sp_helptext procedure, but am wondering if there is a simple shareware tool already available chuc ...more >>

Is there such thing as Dynamic SQL in Transact-SQL? Specifically, is there any way to use it in stored procedures? If yes, I would appreciate a sample of stored procedure with dynamic SQL. If no, is there any other way to build a WHERE clause on the fly inside stored procedure for searches...more >>

Hi, Could anyone point me to a link or post the code to do the following?? I would like a script that lists all the tables within a database, but only the tables that have one or more rows. And then the opposite... A script that displays only the tables in a database that have no rows. ...more >>

H i face the following error when trying to restore/attach a db from one sql server to another 916: Server user '%.*ls' is not a valid user in database '%.*ls' Restore databse terminating abnormally i login to the sqlserver with a login of role 'dbcreator' and try to restore the db it woul...more >>

Hi All, I have a table of manay rows with a field contains of A00AA0B000... I convert this field into a final table like belowing idx value 1 A 4 A 5 A .. .. .. where idx is the positions the characters. I solved this by using fetch each row then insert into a table ...more >>

HI all I've been reading through Books On Line and this NG to see if using the CASE statement is how I can accomplish what it is I need to do, but I'm having difficulty understanding it. I have a variable in a stored procedure called @Type. Depending on the value of this variable I want to ...more >>

Hi everybody. I'm using MSDE2000 and I want to use the 'xp_cmdshell' when I'm not logged in as the system administrator. This call will be implemented in a stored procedure and will call an executable on the servermachine that returns some data (not relevant to the post). The problem is, i...more >>

I try to create a function GetDate2(),it's similar to Getdate() CREATE FUNCTION GetDate2() RETURNS datetime AS RETURN ( getdate()) I got error, how can I fix it? Server: Msg 170, Level 15, State 31, Procedure GetDate2, Line 4 Line 4: Incorrect syntax near 'RETURN'. ...more >>

How do I represent a $ amount that has never been provided versus an $ amount that is $0.00? I know the NULL in a numeric field is represented by 0. So, I'm not very clear when we do a calculation how SQL server can differentiate between 0 (no amount entered) versus $0.00. Thanks in ad...more >>

MSSQL 2K (8.00.818) I have a full backup differential backup 1 differential backup 2 I'm able to restore the full backup with norecovery when I try to restore differential backup 1, I'm getting error 3136 Could anybody help me and let me know whats wrong with that backup and how can be ...more >>

I want to save passwords in an encrypted format in my database. Is there a built-in function of SQL Server to encrypt these, or should I use my own algorythm TIA Will T...more >>

I'd like to use a different locking option than I see offered. I'd like to run a select and honor the exclusive/updated locks, but generate no shared locks. Is there a way to do that Thanks Andy...more >>

I am trying to get a date range from the first of whatever month is previous to the current one to exactly one year previous to that date ie 02/01/04 to 02/01/03 to pull the appropriate section of data out... I have tried to use: BETWEEN MONTH(GETDATE()) - 1 + '/' + 01 + '/' + YEAR(GETDATE()...more >>

Is there a way to determine the current isolation level? I know the default is read committed, but we have reason to believe that is not happening. Is there a way to find it?...more >>

Hi. I am writing this message for Connection from my ASP page to SQL Server It works fine normally But when I try to get connection for the first time, it takes about 3 seconds, and after that, it is farely fast. After for a while, it takes time to get connection.... it happens like this. I am so...more >>

My SQL Server 2000 database has a number of user defined stored procedures. I need a C# program (or if necessary, a database script) to do the following: 1. Get a list of the stored procedures. 2. For each stored procedure, retrieve the SQL of this procedure to text format and store it in a ...more >>

For example, when I say "GROUP BY orderdate," what I really want to say is "GROUP BY orderdate BETWEEN orderdate 0:00.00 and orderdate 23:59.9999" The following gives me separate groups for the same day (e.g., 3/5/04 00:00.000, 3/5/04 12:10.110, etc) SELECT * FROM orders WHERE orderdate>'...more >>

Hello All, How to check if a database already exists or not before trying to create it? Thanks for your help in advance. regards, Abhishek....more >>

When querying text fields (varchar or char), is it necessary or customary to use WHERE RTRIM(MyTextField) = 'MyString' I usually don't but just wondering what is the standard practice. ...more >>

Is it possible to retrieve the default value for a column in a less convoluted way than what follows? It works and isn't slow, but it's hardly what I'd call elegant USE pub DECLARE @JobLevelDefault IN SELECT @JobLevelDefault = Substring( t.[text] , 2 , Len( t.[text] ) - 2 FROM sysobje...more >>

Hey, 1) I'm doing a lot of importing (by DTS packages) from commaseparated files into tables where i empty/truncate the table _before_ import of ALL info in the file. BUT - how do I import data from such a file by an UPDATE command... Meaning if the table has an ID col and a NAME col, an...more >>

Hi group, looking at SQL Server Logs, I see an entry for all of the actions of a database maintenance plan other than the optimization. When set up, there is an entry for DBCC CKECKDB which is for the integrity check; there is "Database backed up: Database: master, creation ...." for the db f...more >>

I sthere a way to send the putput of a query to a file like thi select * from tbl output to an excel file thanks in advance...more >>

I GOT a Task for Optimizing Queries Having Problem with one of the S running an SP That has 86 SQL (UPDATE/DELTE/INSERT/DROP) queries in i The query in question is at no 23( 23rd from top Total time taken by SP is 25 Mi The query at No 23 , shows 95.97% as relative to bact So i calculated t...more >>

I'm experiencing some wierdness with a text parameter I have for a sproc. My sproc has a parameter defined like @msgBody tex I add the parameter to my command object as (C++ code) cmd->CreateParameter("@msgBody", adLongVarChar, adParamInput, msg->GetMsgText().size(), (_bstr_t)msg->Get...more >>

I've created a utility that will permit my users to detach their database from one server in preparation for a move to a different server. The detach process uses two commands: alter database BSDI set SINGLE_USER with ROLLBACK IMMEDIATE to close any current attachments and sp_detach_db ...more >>

We have a sproc that is typically very fast, but occasionally slow. Is there a way to log how long a specific sproc is taking to exec, other than Profiler? For example, could I log the exec time to a table by adding some code to the sproc? Or perhaps setup an alert to sends me an email if the ...more >>

i have a scheduled backup that occurs on a nightly basis. the backup file is given a name with a date stamp - so that each day the name is unique. im wondering if it is possible to use a wildcard character when specifying what file to restore for example: restore database spp_temp ...more >>

I have created a View which is Grouping on a text field in the source table, and Summing several numeric fields This View is linked to an MS Access (Access 2000) d/b using ODBC, and any results from Access - whether directly from this linked View or via an Access Query - are then exported to Excel ...more >>

Hello All, I execute this: exec master..xp_cmdshell "net send MYSERVER Testing NET SEND Through SQL Server" I get the following error: An error occurred while sending a message to MYSERVER. NULL The message alias could not be found on the network. NULL More help is available by typing ...more >>

In one of our stored procedures we see the following error for some cursor operation Could not complete cursor operation because the table schema changed after the cursor was declare However if we re-run the same procedure again we do not see this error message. Also the message does not appear ...more >>

I know its not SQL, but does anyone know why the background colour of a cell on an .asp web page doesn't appear on the print or print preview of a colour printer despite apprearing in the browser thanks in advance Carl ...more >>

We have a Third part software which activates 3 trigger for each table, when I restore production server which does not have this application start getting error with these triggers Is there any easy way to remove all of these triggers on backup server? They all start with specific key word (easy ...more >>

Hello MVPs, I want to execute a dynamic sql statement using sp_executesql, but is not permited in functions. Is there any other way to execute my statement without having it in a stored procedure and call that. Thanks in Advance. ...more >>

Hi all I have this table below: I need advice what's best way to do this!! Key, FirstName, LastName, PTNumber GivenDate GivenTime Test Result UnitNum Pre POST 456 FNA LNA 123456A 12/4/2003 1300 HGB 9.8 457 FNA LNA 123456A 12/6...more >>

Hi all, How can I set a smalldatetime var. without time, I only want to save the date part. Ex. set @var = getdate(). Thanks in advanced. S.L....more >>

Is there any other way then scheduling a job to have a live process run on the database a database continually and check the status of data What I am trying to accomplish is to have a process check the time elapsed between certain records so that I can allert users when they need to perform a task...more >>

Hi Is there any function equivalent to Oracle' LTRIM(<source_string>,<string_to_trim> in sql server Thanks in advance for any help Regard Aimmee...more >>

I have a small security problem. I have a number of pages on a server and I want things set-up so as the user can't get into other pages without going to the index page and logging in first. At present the user can easily type in the name of one of the pages in the system and get direct access to ...more >>

Using ADO in VB, I'm trying to execute the following DDL against a SQL Server 2000 database: ALTER TABLE PersonalDetails ADD CONSTRAINT pkPersonalDetails_EmployeeNumber PRIMARY KEY (EmployeeNumber) However, I get an error, "There is already an object named 'pkPersonal...more >>

I have a database which contains 2 char fields LDate char(10) //format (mm/dd/yyyy LTime char(10) //format (HH:MM:SS I want to sort the db sorting by LDate and LTime in desc order, then I used, for exampl SQL:--- Select LDate + ' ' + LTime as LDateTime from Table1 order by LDateTime des But ...more >>

Hello MVPs, How to find which windows user is currently log on the computer with T-SQL? PS: Aren't these: Select SESSION_USER, USER, USER_NAME(), USER_ID(), CURRENT_USER, SYSTEM_USER, HOST_NAME() Thanks in Advance. ...more >>

Can anybody help me figure something out. I'm having a brain fart here Here's what I want. I want to have a function/proc that returns a date that represents the "Week of " date. I have determined that Friday is the last day of my business week. So therefore if I passed in 3/4/2004 I would exp...more >>

The most intuitive way to compare A and B case-insensitively is to compare the upper case version of A and B. If the upper case versions match, then A and B are case-insensitively equal However, sql server's compatibility_130_409_30001 collation does not behave like this According to KB#270042, ...more >>

Hi, I'm having some trouble with a concatenation of a string and a CASE statement. When ever address2 or address3 are null, the select statement doesn't work and all I get is an empty field. Am I using the CASE statement correctly? SELECT name + ': ' + address1 + ', ' + CASE WHEN addres...more >>