I have a web app (ASP) that does all updates, inserts by calling transaction-supported COM+ components (with the transaction started in the ASP page, i.e. transaction=required) that use ADO to call stored procedures (that usually involve single tables). If there is any error (missing SP, paramet...more >>

I have an INSERT statement that breaks when I try to use an apostrophe s. INSERT INTO tblProduct (name) VALUES('John\'s Store ') name is varchar(20). What is the correct formatting to produce the text John's Store ? Thanks. ...more >>

I have the following setup: 1. ASP.NET 2.0 web app hosted on a web server (inetpub directory hosting physical files of the web site on a different drive name, E: than the SQL Server Instance, which is on C:) 2. SQL Server on the same server, with security settings enabling ASPNET group and grou...more >>

I've got a field that contains a Name and Type seperated by a slash. The only consistant "rule" of the EXAMPLE RECORDS is that each record is seperated by a slash. As you can see, some contain numbers and some don't. I'm failing miserably because of lack of knowledge of string functions. Ca...more >>

When viewing an instance's Replication properties in SQL 2005, clicking on the "Snapshot" icon in the left side shows the options for the "location of the snapshot files". The location can be a default folder or a designated folder. Such as C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL...more >>

Hi, In my table I have a "PaymentDate" field which is used to store payment schedules for our clients. I want to add a new field in the same table and call it "UpdatedPaymentDate" field as most of the time our clients don't stick to original payment schedules. How do I make the default v...more >>

Below I'm returning the decimal duration [decDuration], a HH:MM:SS format without "padding" 0's [realDuration], and a HH:MM:SS format with "padding" 0's [realDuration2]. If you run my EXAMPLE, in RESULTS below you'll see that [decDuration] and [realDuration] work fine, but [realDuration2] l...more >>

I have a process in my application that should only be performed by one user at a time. Some folks have suggested that the way to do this is to have a table in the db that has a column to store the name of the user that's running the process (a flag of sorts) so that if another user tries to run...more >>

Hi, How do I copy the rows in my table into a .txt file comma delimited. I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ',' but it does not work TIA Roy Gourgi ...more >>

Hi, i do a create: CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL) and now i want to remove the IDENTITY definition from colum id using ALTER myTable ALTER COLUMN id ... ?? so that column id has definition as when i do a: CREATE TABLE myTable ( id int NULL) How can i do this? th...more >>

I encoutered a strange behavior using the exec command and I could repreduce the behavior with the print command: the command: print '1 2 3' is doing it's jub, but if i add go inside the string I get the floowing error: print '1 2 go 3' Server: Msg 105, Level 15, State 1, Line 1 U...more >>

Hi, im hoping someone could help me with this problem. I would appreciate any help at all: SQLite said that it supports current_timestamp but it doesnt work when i use it as a Default value, for example: (Im executing these sql statements in sqliteqa) 1.drop table sample_table; 2.create...more >>

I must have SET ANSI_WARNINGS OFF because the program reads return value and it happends to be ANSI varning instead of return value. But If I include SET ANSI_WARNINGS OFF into my procedure, I'm getting an errors because of indexed views. Is there any solution to this? regards,S ...more >>

Hello: This is probably a very elementary auestion, but I am new to SQL and Crystal Reports. We use a 3rd party SQL application. This app uses stored procedures and Crystal Reports. I've copied and modified one of the stored procedures to add an additional parameter and copied the Crys...more >>

Hello, I have a stored proc that has started behaving strangely a few weeks after upgrading to SQL Server 2005. This problem cannot be replicated in the test environment. The stored proc responds within 1 second in the Production environment until it slows down (not sure why) and takes abo...more >>

Security applications such as namp and ethereal are appearing that run straight from a thumb drive and packet capture, detection and injection tools no longer require the installation of WinPCap or other third-party packet capture drivers. Link: http://ttcom.blogspot.com/2006/04/list-of-hacki...more >>

Consider the rows for a given extract: DeviceNumber Type Sequence1 Sequence2 90001 1 A 5 90001 1 A 6 90001 1 B 4 90001 ...more >>

I'm using server cursor for generic paging. Interesting part is this: FETCH RELATIVE @StartRow FROM cur WHILE @PageSize > 1 AND @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cur SET @PageSize = @PageSize - 1 END Instead of single result set with N rows, this returns N result sets with 1 r...more >>

Hi, Do we have Hierarchical queries in SQL Server 2000 (like that by using start with....connect by prior... in Oracle)? If someone has worked on some work-around to do so in SQL Server 2000, pl. let me know. Thanks in advance. Regards M. Subbaiah ...more >>

Hi, I am in a situation where I have an array of userIDs in VB .Net. For each of these UserIDs I need to query the database for the fastest time for each user and would like to fill a dataset ordered with fastest to slowest time. Is there any way I can manage this in T-SQL and submit an ar...more >>

Hi, In my application i am having the situation that, the select query will returns Column Name + Value. Ex. Subject Author ------------------------------------------------ Subject - VisualBasic Author - BalaGuruSamy Subject - C++ Aut...more >>

This works: set @sql=N'DECLARE C_LOOPR CURSOR FAST_FORWARD FOR '+ 'SELECT v.ING_ID,v.staID from v_predRez v WHERE v.ING_ID IN('+@sIngIDs+') ' EXEC sp_executesql @sql OPEN C_LOOPR .... ..... If I create temp table, doesn't work any more: set @sql=N'declare @skl table(TX_SKL va...more >>

Hi, Can any one tell me the script for following task: I am working with a parent table contained with 90 rows and child table with 50 rows(suppose it has no duplicate master value) , I want to write the script for find that unmatched 40 rows from parent. Parent table's fields : empmast_i...more >>

I am using SMO to restore a database. My question has to do with how to specify where the .MDF and .LDF files should go. If I do a restore manually via Management Studio, a default location is provided which is 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'. Is there someway in SMO that I...more >>

The title of this post is a little cryptic but hopefully I can explain well enough. I asked a similar question a while back, didn't get usable answers and then thought I'd solved it on my own. However, my current approach to the query is running way too slowly. In this case I have a stati...more >>

I'm building a vb.net Forms project that is getting data from a SQL Server database. One of the main goals of the project is to be really responsive to events, such as textbox change events. I have a textbox for searching, a listbox to display the searched results, and a big textbox (memo) to ...more >>

Does anyone know what table, view, etc., contains the SQL Server 2005 server collation (not database collation) value? When I use SQLDMO it returns an empty string, yet when I view the SQL Server 2005 instance properties in SQL Server Management Studio, it correctly shows as SQL_Latin... etc. ...more >>

Does anyone now the minimum permissions to debug SQLCLR. I have a friend trying to develop as a non-admin and he finds that it doesn't work even if he launches Visual Studio to run as an admin account. Is it possible? Are there certain rights required? Simon Sabin ...more >>

Two things are bugging me: 1) The bug where you get an partial crash in the "Summary" window if you do something the underlying ListView control doesn't like; apparently it's an issue MS knows about but when will it be fixed? This isn't supposed to be Beta anymore! Anybody know more about this...more >>

This seems like this should be easy but I'm struggling. I have an Items_Sold table with columns for Loc_id, Item_no, Qty_sold, and Date_sold. I also have an Item_Inv table with columns of Loc_id, Item_no, Max_qty, and many other columns. I need to update the Item_Inv table and set the Max_qty...more >>

Hi I have an app that executes a stored procs and writes out the data to a file. Currently uses ADO recordset and goes through each record. This is slow. I would like to use bulkcopy - however have the problem that the app resides on a client machine - so will not have SQL server installe...more >>

Hello NG We have a production database that we have moved the tables to SQLServer and I have been creating stored procedures to replicate some of the processes done on the access side - the server does processing extremely faster than access - Here is my problem I am currently pulling info fro...more >>

Howdy all. Somewhat new to stored procedures... Have a crystal report that uses a stored procedure but appears to be missing data... Background - the original data table was generated through a several Paradox queries which are now being converted to SQL. Have done some readings on sto...more >>

Hi All, I need know: what is best choice: Surrogate or Natural Keys? I need understand, what is best? Thanks ...more >>

Hi! I have a consultant onsite at a client, and she's sending back pictures of the query plan for a script with a performance issue. These are useful, as they're turning out somewhat different from the development and test machines. But, is there any way we can pass the query plan so that I ...more >>

Greetings and Salutations on this most beautifull Friday... I have 2 tables that are joined as such: INNER JOIN dbo.tblSettles ON dbo.aaaBODPNL.ExchangeLongCode = dbo.tblSettles.trDelta Is there a way to join it so that if there is not a match to join up to on the trDelta Table, it return...more >>

Hello, I was wondering if some one can direct me to a link where code samples are posted or syntax checkers. I've taken on this report project using stored procedures, the request is base on inventory commission for sales reps. The rep will be paid commission on a $ amt base on the invoi...more >>

Hello NG I have been working with Access for a Long Time - Am just now starting to get my feet wet with SQL Server 2000 - We have a production database that we have moved the tables to SQLServer and I have been creating stored procedures to replicate some of the processes done on the acces...more >>

What is the program microsoft has developed to help optimize Stored procedures? (I am not talking about the quesry analyzer). Sean ...more >>

I do not understand what I am doing wrong. I have no problem getting the value into @tot but I cannot get the value assigned to @item_cost. When I use the debug mode, it says @item_cost is null before the start of the debug and after the debugging is done. CREATE PROCEDURE test ...more >>

will there be any difference in query performance between statement1 and 2??? How sql server 2000 treat them ??? do they run in the same manner? DECLARE @FDOFMONTH AS VARCHAR(30) DECLARE @FDOFYEAR AS VARCHAR(30) DECLARE @TO DATETIME DECLARE @FROM DATETIME DECLARE @YEAR VARCHAR(4) DECLARE @...more >>

I recently had a routine using a table variable involved in a fairly complex query using a number of fairly large tables which ended with the following error. Server: Msg 1101, Level 17, State 10, Line 40 Could not allocate new page for database 'TEMPDB'. There are no more pages available in...more >>

All, I am trying to figure out how to convert this particular query, and am stumped... SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Category, First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%], First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCate...more >>

I created an SP for searching rows in a table. Create Proc stp_search @recID varchar(100) = '%' As Select * from tbl1 Where recID In (coalesce((select * from dbo.udf(@recID)), recID)) The SP works if I pass in only 1 recordID, or if I don't pass in anything (default val of %). But ...more >>

Hi, All I have banged my header to the keyboard for 2 days Suppose I have a log table as following pointA, pointB, eventTime, event 100, 200, 2006-04-28 14:15:15 Terminate 101, 200, 2006-04-28 14:13:15 Rejected 101, 200, 2006-04-28 14:11:15...more >>

Is there a way in an SQL Select statement to set up something similar to the IIf command in Access: Select LastName, IIf(CodeA = "Y","Yes'","No") From NameTable...more >>

I need to know if I can do this with a CASE statement: I have a column (Name) in table start that I want to compare to another column (ProperName) in table finish. If the Name matches any ProperName then I want to display TransName from finish. Can I do this: CASE WHEN start.name = fi...more >>

create proc stp_errorTest as select 1/0 if @@error <> 0 RAISERROR ('my custom err message', 16, 10) go I purposely generate the 'Divide by zero' error. When I invoke the SP in QA in get the 'Divide By Zero' message along with my custom message. Is there a way to overrid/bypass/t...more >>

Is there a Tsql function that returns the count of the occurence of a char in a string? I looked at string functions and various functions return the starting point of a char or set of chars. Example of what I am looking for Declare @s varchar(100) Set @s = 'abc, def, ghi, jkl, mno, pqr) ...more >>

I have a temporary table that includes worker names, titles, departments and data about meeting our service level agreements over various incident severities.. I have included some the DDL below... The problem I have is that the report must also sum (for the counts) and average (for the percen...more >>