I have a scenario where I'm using transactional replication to replicate multiple SQL Server 2005 databases (same instance) into a single remote database (different instance on a separate physical machine). I am then performing some processing on the replicated data for reporting purposes. I'm using table level triggers to identify chan...
We have a number of items coming in from a web service; each item containing an unknown number of properties. We are storing them in a database with the following Schema. Items - ItemID - ItemName Properties - PropertyID - PropertyName - PropertyValue - PropertyValueType - TransmitTime - ItemID [fk] The properties tabl...
Inspired by this question How can I quickly identify most recently modified stored procedures in SQL Server I am wondering if at the same time we can identify the user? How to do this using sql program(if any possible)? I am eager to know. Thanks in advance ...
Can anyone give me advice on where to start when it comes to setting up a project on a server so that my helper and I can check out code files? I have a hosting account with Go Daddy - IIS 7, if that can be used. Also, I'm looking for advice on how to learn more about setting up my database on a server - currently my project has it set...
Hi! I'm trying to run multiple queries on multiple tables- similar to "select count(*) from TableA where x=1" per table. What I'd like to do, is get all of the count(*) values that are returned and sum them into a single value... Any ideas? ...
Hello friends, I am trying to create an SP for presenting paged data on aspx page. I have written following code - Create PROCEDURE [dbo].[sp_GetAllAssignmentData_Paged] @currentPage INT=1, @pageSize INT=20 AS BEGIN SET NOCOUNT ON; with AssignmentData As( select ROW_NUMBER() over (order by a.StockNo desc) AS ...
Hi, I have a table that contains data for electric motors the format is: DATE(DateTime) | TagName(VarChar(50) | Val(Float) | 2009-11-03 17:44:13.000 | Motor_1 | 123.45 2009-11-04 17:44:13.000 | Motor_1 | 124.45 2009-11-05 17:44:13.000 | Motor_1 | 125.45 2009-11-03 17:44:13.000 | Motor...
Do we need to Update table statistics after calling Truncate table or it gets updated automatically? Q: Do we need to call "UPDATE STATISTICS" after truncating a table? ...
I have a Patient table: PatientId Admitted --------- --------------- 1 d/m/yy hh:mm:ss 2 d/m/yy hh:mm:ss 3 d/m/yy hh:mm:ss I have a PatientMeasurement table (0 to many): PatientId MeasurementId Recorded Value --------- ------------- --------------- ----- 1 A d/h/yy hh:mm:ss 100 1 A d/h/yy hh:mm:ss 200 1 ...
I have a table in a SQL Server database that has an auto-generated integer primary key. Without inserting a record into the table, I need to query the database and get what the next auto-generated ID number will be. I think it's SQL Server version 2005, if that makes a difference. Is there a way to do this? ...
Consider a SQL Server table that holds log data. The important parts are: CREATE TABLE [dbo].[CustomerLog]( [ID] [int] IDENTITY(1,1) NOT NULL, [CustID] [int] NOT NULL, [VisitDate] [datetime] NOT NULL, CONSTRAINT [PK_CustomerLog] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY] The query here is around finding the distributi...
Short Question: What's the best date format to use in SQL Server? Long Explanation: We're converting our database from mysql to SQL Server. In mysql we always used int(11) to avoid the daylight savings time issue, should we be doing the same in mysql (or is DATETIME good enough)? The use of the dates vary. Most of the time they're ...
I am trying to get the last Id using max(Id) function where Id is the primary key. My code works well unless I delete last row from the table, because when I delete last row then that Id is still preserved and in that case max(Id) will retrieve the last row Id but not that Id which is deleted from the table. Is there any function which ...
Hi there. I am a rookie in writing SQL stored procedure using C#. Just wandering is there any way I could create a general method which will be called in the stored procedure within the same script? ...
The sp_databases stored proc in sqlserver returns a remarks column. Where does this information come from, and can I edit it? ...
Assume Category table has categoryId, name and parentCategoryId columns, where categoryId is an identity field. now what if I have to replicate a category and assign it to a new parent? Because it's not a simple insert statement. I have to insert all the subcategories and their subcategories and so on. How would I keep track of their...
I've created a sproc to delete multiple records by accepting a comma-delimited list of ID's as varchar, and using IN to attempt the deletion - doesn't work: ALTER PROCEDURE [dbo].[sp_DeleteItemsFromItemCategories] @UserID bigint, @ItemsList varchar(8000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interferi...
I have a job with around 100K records to process. There are many print statements which get executed for each record. Does these print statements have any impact on performance? For that matter, does number of lines or comments have any impact on performance? I want to save even a single ms if I can. ...
Do index names have to be unique accross the entire sql server database, or just for that table? For example, should I name my index: IX_OrderLoadCarrierDelivery_OrderLoadID for the OrderLoadID column of the OrderLoadCarrierDelivery table. Or should I just name it IX_OrderLoadID Thanks! ...
Can anyone recommend a free (or cheap) program out there which allows me to easily document what certain tables and fields are used for in our applications? Ideally, when we update a fields type, it would automagically update in this tool. Also, showing what fields are indexed etc would be beneficial. What solutions have you guys / gals...