Now some records have been locked by one user and the others select these records at meanwhile. So the others how to know these records have been locked? The locked method I used like this: Begin tran select * from table with (repeatableread) --commands commit tran -- marxi...more >>

I have a list of values from an online survey. Customer Item ---------- ----- 1 A 1 C 2 A 2 B 3 A 3 C 4 A And would like to return a list based on varying criteria, for exam...more >>

Anyone have a idea about what is meant by below? /****************************************/ Cannot roll back ken. No transaction or savepoint of that name was found. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 65, curr...more >>

Hi; im really new with programming SQL server, i just create my first Trigger (not an easy task, but it works)... now i need to create a trigger or store procedure (im not really sure), to compare the sum of two fields and check for a value on another table, before allow any insertion, ...more >>

Hi All, In MSSQL 2005 to view the server roles of the user I did the following: Enterprise Manager->Click on server->Secuirity->Login then click on user and then properties to bring up the "SQL Server Properties" for the user and in this window I can click on Server Roles to view the server ...more >>

Select into will recreate the base table. Select *, '' as newcol, null as newcol2 from sometable will create the original table with two new columns. newcol will be varchar(lengthofstring) not null and newcol2 will be an int null. Is there anyway to control, I'd like to create a varchar(25)...more >>

How can I catch and set the error message. for example I got system error message like " Msg 208, Level 16, State 6, Procedure sp_RunDBSqlScripts, Line 124 Invalid object name 'dbo.sp_RunDBSqlScripts_sqldev5. " set like set @errormessage = " " -- message from above. ...more >>

Is there any way to find if certain query make error or not without execute? because I plan to write record into table about the query such as "insert, delete, update". However I don't want to effect to the tables which the queries executed into. I used the rollback function to make execut...more >>

Hi All, Can I get a count base on some condition from three fields at the same time? Tks JFB --- one at the time ---- select count(field1) as expr1 from addresses where field1= 'Lima' --looking for select count(field1) as count1, count(field2) as count2, count(field3) as count3 from ...more >>

I have a drop of data from a client that stores their date fields as a numeric. The format is yymmdd. The problem is that they do not have strict date rules for this field and there can be "garbage" contained within it. I want to convert/cast this data to a SmallDateTime field but it chokes o...more >>

I know how to use EXEC across servers: EXEC myserver.mydatabase.dbo.myproc But if I am on instance myserver AA and I need to execute something on instance myserver BB? EXEC myserver\BB.mydatabase.dbo.myproc doesn't pass syntax Anyone! ...more >>

Is there a way to combine the functionality of IN & LIKE? I would like to write: SELECT * FROM Table WHERE Col IN ('%pat1%', '%pat2%', etc...)...more >>

I'm rewriting SQLGuys's backup script as a detailed excersize and would like your thoughts on Logical Log backup placement when a full backup is being preformed. Under light insert/update/delete conditions, this doesn't matter. However, under extreem conditions, it might. When the progra...more >>

We have a table (TableB) which we use to archive records accumulated for the day from (TableA). All live applications are using TableA (1000 queries a minute) When we archive the data from TableA to TableB we use this: Insert into TableB select * from TablaA with (nolock) where Create...more >>

Hi, is it possible to reuse a calculated fieldname (in the example sum1)? example: select (f1+f2+........fn) as sum1, (sum1*1000) as myvalue from my table thanks ...more >>

What is the best way to pass a date from a Microsoft Access form to an SQL Server Stored Procedure? I'm using an ADO command object to pass the date in the text box to the parameter in the stored procedure. Passing a date in the YYYY-MM-DD format returns more records then passing MM/DD/YYYY....more >>

Hi All, I have been trying to attach the database but the error message come back is "Could not attach the database, the files are readonly" Have you have this problem before and how to solve it. Thanks in advance, JP...more >>

Hello, i want to get an overview information over one select command that displays me an information like: From 1000 questios there are 500 resolved and 350 escalated and 250 open Detailinformation:Table structure country varchar(2) questionId(int) questionstate(varchar(50) exampl...more >>

I have a database table that has data constantly written to it. There is a need to have a daily job that purges the data from the previous day, but this could be a considerable amount of data, possibly hundreds of thousands of rows or more. I am worried that just doing a DELETE FROM <TABLE...more >>

The problem that I have seen is when executing the following statement on SQL 7, SQL 2005 and SQL 2005 Express, appears to be that SQL is adding 2 days to a DateTime datatype value when put into a database through the SQL Insert Statement. Going into a MSAccess database works fine. Does ...more >>

From multiple records in a table, for a datetime field I am trying to get the start/stop time for each range of times that doesn't have a gap of 15 minutes or more. Example Output desired from the example records below Start: 05:57 End: 05:57 Start: 07:09 End: 07:15 Start: 09:06 ...more >>

I have a number of table in a database and all the tables are named as following format: Table1 Table2 Table3 Table1x Table2x Table3x etc. I need a script that will: 1. verify if Table1 exists and then verify if Table1x exists; if yes then delete Table1 and then rename Table1x to Tab...more >>

Hi. Can anyone explain how to parse out the months within a date range? For example, if I have a start date of '1/1/2006' and an end date of '5/30/2006'. I need to parse out each month (1,2,3,4,5) and run seperate queries in the stored procedure based upon each of those months. The start ...more >>

Is there a vbscript / syntax to execute a sproc? I know how to create the exec command in a DTS and schedule it to run, but if I want to have a macro do it so that a user can manually invoke the macro what would that be? ...more >>

May be this may sound silly for some of you but. In my office the live server is SQL Server 2000. even client Workstation (My PC) has sql 2000 client. I want to work in SQL 2005 which my manager had approved for.. I have windows XP installed in my workstation. If I install SQL 2005 cli...more >>

Is possible verify in a select if a file exist in a folder? I have a table that have a column FILE_NAME that is the file name. I what select all lines in table that the FILE_NAME do not exist in a folder. Is possible doing this in a select? Thanks, Rui ...more >>

Hello - Is it necessary to install SP1 before installing SP2 for Sql Server 2005, or does SP2 contain all the bug fixes from SP1? -- Sandy...more >>

Hello, I'm not sure if this is an SQL issue or an Access issue. I have an Access 2002 FE and I'm using a passthrough query to retrieve some information from SQL server 2000. I am rounding in the SQL View to two decimal places, but the passthrough is giving me numbers like 14.450000. I've...more >>

Hello, I have the following query, SELECT GroupInfo.GroupID, GroupInfo.GroupName FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID) INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber ) It's out put is as follow: Group ID ...more >>

I have a sales stats table and I want to do some counting and grouping by source and cluster (join required). The 2 tables and sample data are as follows: CREATE TABLE dbo.stat (id_source varchar(10) not null, id_period int not null, id_dept varchar(10) not null, ind_Domestic char(1...more >>

I have a table T, one of the columns is tColumn varchar(8000) After the table is populated, some field values in tColumn contain GUID values SELECT * from T In my SELECT I want to filter out all the rows which have a GUID values (uniqueidentifier) in tColumn. How can I do it ? Is there funct...more >>

Hello, I need a suggestion regarding one of the scenario that i have currently programmed for. It is as follows: There are two tables. We need to check if a row in table1 is also present in table2. If present, we need to update the value in table2 else insert it into table2. In a lega...more >>

I have a trigger Create Trigger TTT On Table1 For Update As Set Declare @Active1 Bit, @Active2 Bit, @Val int Select @Active1=Active From Deleted Select @Active2=Active, @Val=Val From Inserted If @Active1<>@Active2 Beging Insert Into Table2(Field1, Field2) Select @Val, @ctive1 End ...more >>

Hi. Is there a way to disable all constraints on all user tables in a 2005 database via a SQL command? Also, is there a way to turn them back on? Thanks. ...more >>

Is it possible to slipstream SP2 of SQL Server 2005? I know SP1 didnt like it, but did they finally fix it for SP2? and if so how do you do it? Thanks! ...more >>

I have it in my head that varchar is correctly pronounced as var-"car". However, I'm now surrounded by people who say var-"char". It's knocking my confidence on the topic down to zero. Which is it? Thanks! -Humbled by Pronunciation in the Midwest ...more >>

as subject ...more >>

Hello I have a slight problem, I need to count 3 things and then do some math in a single select. Not sure how to get it done properly without needing subqueries. Hope it this testcode makes sense. TIA /Lasse DDL: CREATE TABLE #Tmp ( A char(1) NOT NULL, B char(1) NOT NULL ) G...more >>

I want to load a "page" of 100 items at a time from a sql table with 1M rows into a listbox. Ant good way to define the recordset with the first 100 records, next 100 records, and so forth so memory usage is optimized for this? Thanks ...more >>