Hi. I have a table with call data records one for each call with call data and one of the fields is the CallerId which we use when quering the DB. We use the following TSQL to simulate an array parameter, is this the way to go or are we way off? ALTER PROCEDURE [dbo].[spStudio_Get_Smdr] @beginTime INT, @endTime INT, @subsc...
I have a poor man's replication setup that I can't do anything about. Some identifying data (basically primary key) from a call_table is copied into another table via a simple trigger, and then the "replication server" runs a stored procedure to copy the data from the queue table to a #temp table (to prevent locking in SQL 6.5 is the cas...
Hey All, I have a stored procedure that works fine on my local SQL Server (2005 or 2008 cant recall off hand) but fails when I try to create the procedure on the Production server (SQL 2000). Any help would be appreciated. TIA. The stored procedure declaration is this: /****** Object: StoredProcedure [dbo].[AssignPCSCheckNumbers] ...
When I perform a select/Insert query, does SQL Server automatically create an implicit transaction and thus treat it as one atomic operation? Take the following query that inserts a value into a table if it isn't already there: INSERT INTO Table1 (FieldA) SELECT 'newvalue' WHERE NOT EXISTS (Select * FROM Table1 where FieldA='newvalue...
I am trying to setup a data warehouse app for use my company with a partitioned data import table. I am trying to drop older data off and make room for new data. That is where I am getting this error message: Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'AssetServer...
What are the advanced Features With SQL2008 over SQL2005 Particularly with TSQL ...
Imagine I have these columns in a table: id int NOT NULL IDENTITY PRIMARY KEY, instant datetime NOT NULL, foreignId bigint NOT NULL For each group (grouped by foreignId) I want to delete all the rows which are 1 hour older than the max(instant). Thus, for each group the parameter is different. Is it possible without looping? ...
I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my field allow NULLS as I've decided null to mean "all values". Because of nulls, the following statement in my stored procedure can't work: IF EXISTS(SELECT * FROM MY_TABLE WHERE MY_FIELD1 = @IN_MY_FIELD1 AND MY_FIELD2 = @IN...
This query for creating a list of Candidate duplicates is easy enough: SELECT Count(*), Can_FName, Can_HPhone, Can_EMail FROM Can GROUP BY Can_FName, Can_HPhone, Can_EMail HAVING Count(*) > 1 But if the actual rule I want to check against is FName and (HPhone OR Email) - how can I adjust the GROUP BY to work with this? I'm fairly ce...
I can use the PRINT statement in a stored procedure to debug my code. I see the output in the Messages tab of SQL Server Management Studio. How can I dump one or more entire SELECT statement outputs to that Messages tab? My stored procedure returns several output variables so returning a single dataset isn't an option here. I am strug...
I have a demo database with a couple of hundred tables in it. Each table usually has at least one field named tstamp which is a smalldatetime datatype. Some tables have other datefields too. Many tables also have 1 or more triggers on them. I wrote a script (the hard way - see below) to increment the date fields in each table by a given...
I'm developing an application in Access 2007. It uses an .accdb front end connecting to an SQL Server 2005 backend. I use forms that are bound to ADO recordsets at runtime. For the sake of efficiency, the recordsets usually contain only one record, and are queried out on the server: Public Sub SetUpFormRecordset(cn As ADODB.Connection...
I am trying to create Update trigger which should be invoked only if the ReturnedOn column is clicked. I have used the following code-snippet but it generates the error: CODING: CREATE TRIGGER trg_ForUpdateOnBookIssuedDetails on BOOKISSUEDDETAILS For update as begin declare @Rows1 int,@Rows2 int if(update(ReturnedOn) begin IF EXISTS(...
I have 2 tables requests (ID, company_id, amount) companies (ID, name) with FK constraint (requests.company_id -> companies.id) requests.company can be NULL I need to get all requests and replace company_id with appropriated company name or left it blank if no company was specified. I have next query: SELECT R.[ID], C.[name] AS [c...
I have a weird problem where after setting nocheck on a foreign constraint and re-enabling it, I am getting a same out-dated execution plan that was used with nocheck on. Why would SQL server generate an execution plan as if foreign constraint FKBtoA is disabled even after adding the check again with following statement? alter table ...
I want to iterate through a table/view and then kick off some process (e.g. run a job, send an email) based on some criteria. My arbitrary constraint here is that I want to do this inside the database itself, using T-SQL on a stored proc, trigger, etc. Does this scenario require cursors, or is there some other native T-SQL row-based ...
Hi, This question is just for a sake of knowledge. I don't have any practical implementation of this. I have fired a query like SELECT RAND() RANDOMCOLUMNS,COL1 FROM tblY ORDER BY RANDOMCOLUMNS Output: RANDOMCOLUMNS COL1 ================================ 0.567773034904758 1 0.567773034904758 2 0.567773034904758 3 ...
Hi, when i try to execute this sql statement i am getting the error.. Conversion failed when converting character string to smalldatetime data type. Does anyone know what i am doing wrong? declare @modality varchar(50) declare @datefrom smalldatetime set @modality = 'xxxxxxx' set @datefrom = '20090101' declare @var1 nvarchar(4000) se...
I have some negative values coming back from a query. I would like them to just be zero. How do I write a condition in my sql query that returns zero if the value is below a certain value. sol: CASE WHEN CONVERT(float,dt.FQI53X02_101) < 1.3 THEN 0 ELSE CONVERT(float,dt.FQI53X02_101) END AS FQI53X02_101 ...
Below is the code snippet with comments which describes the problem statement. We have an update trigger which internally calls another update trigger on the same table inspite of Recursive Trigger Enabled Property Set to false. Would like to understand the reason for this as this is causing a havoc in my applications. /* Drop statemen...