Currently I have a large import process that I'm trying to wrap inside a transaction so if anything breaks - i could rollback. The issue I have is that when the TSQL inside the trans blows up, it won't rollback when the following SQL error occurs Msg 8152, Level 16, State 14, Line 249 String or binary data would be truncated. The state...
We have a table that is going to be say 100 million to a billion rows (Table name: Archive) This table will be referenced from another table, Users. We have 2 options for the primary key on the Archive table: option 1: dataID (bigint) option 2: userID + datetime (4 byte version). Schema: Users - userID (int) Archive - userID - d...
How can I find all database objects in a given database using an object name? We prefix all site specific tables, views, indexes, functions, constraints etc. with a constant string. I need to find all objects with names starting with that string. ...
Basically I have a sql 2005 standard server with a named instance installed (server/instance1). I also have created a DNS entry (dnsDBServer) that points to the ip address of the sql server. A web application we have can connect using the following methods (ipaddress/instance1, server/instance1) but cannot connect using the dnsDBServer...
I'm having a problem with an ad-hoc query that manages a fairly high amount of data. Upon executing the query, the status immediately goes into suspended state. It will stay suspended for around 25 minutes and then complete execution. I have a mirror environment with SQL2K and the same query executes in around 2 minutes and never goes i...
I need to build a sqlserver query that if a text column has a full text index, it will be used, if not, the query will degrade to the O(N²) approach of LIKE '%word%'? I believe the answer will be something like: IF has_full_text_index('mycolumn') select mytable_id from mytable where contains(mycolumn, 'word') ELSE select mytab...
I have a table that has ntext field. MSDN says that ntext is deprecated and they suggest other data types: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), var...
Trying to understand what Sql Profiler means by emitting "sp_reset_connection". I have the following, "exec sp_reset_connection" line followed by BatchStarting and Completed, RPC:Completed exec sp_reset_connection SQL:BatchStarting SELECT [c].[TestID] AS [TestID], [c].[Description] AS [Description] FROM [dbo].[Test] AS [c] SQL:...
In SQL Server 2005, we defined some UDT (User Defined Datatypes) on in particular was SK (for Surrogate Key). These were defined as 32 bit 'int'. And consequently the size was 4 bytes. In SQL Server 2008, the UDT for the integer datatypes uses a different storage mechanism, depending upon the precision: Storage Displays the max...
My work company has a MSSQL server 2005. I have two questions about finding out current log user and any way to send out a warning message: First question is if there is any T-SQL or SP available to find out current login user name and machine name. If the user is using SQL server sa name to remotely access to SQL server, is there any w...
Could anyone help me understand why the following query works fine in SQL 2000 and not in SQL 2005 In SQL 2005 it errors out "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value" DECLARE @Table table(date varchar(6),code char(1)) INSERT INTO @Table select '010209','N' INSERT INTO ...
I have a MS SQL DB contains set of tables each table represents a collection of variables calculated based on our formulas. All the variables are numeric with predefined percision (we are using numeric data type with n.m as n number of digits for integral part and m number of digits for fractional part). My question is how to prevent ou...
I have a problem similar to the on in this weeks podcast. We have a Java application using hibernate with Sql Server 2005. Hibernate is generating a Query for us that is taking nearly 20 minutes to complete. If we take the same query using show_sql and replace the questions marks with constant value the answer is returned immediately....
I have the following query DECLARE @userId INT DECLARE @siteId INT SET @siteId = -1 SET @userId = 1828 SELECT a.id AS alertId, a.location_id, a.alert_type_id, a.event_id, a.user_id, a.site_id, a.accepted_by FROM alerts AS a JOIN alert_types AS ats ON a...
We have an MS Access database that we want to migrate to a SQL Server Database with a new DB design. A part of the application that uses the SQL Server DB is already written. I looked around to find out how to do the migration step most easily and started with Microsofts SQL Server Integration Services (SSIS). Now I have gotten to the p...
I have a SP that calls another SP and the resultset needs to be filtered to only the columns that I am interested in. DECLARE @myTable TABLE ( Field1 INT, Field2 INT, Field3 INT ) --If someSP returns say 30 params and I need only 3 params, I don't want to declare all 30 in my temp table @myTable INSERT INTO @myTable (Field1, Fie...
I am a beginner in database field and this question might sound too stupid but I want to know why there is a login called sa and can I delete it? I want to delete it because it seems to have pretty serious privileges on database server! If it matters, I am using SQL Server Express 2008. ...
Hi, Via a web service, remote computers will be sending a set of rows to insert into our central sql server. What is the best way (performance wise) to insert these rows? There could be anywhere from 50-500 rows to insert each time. I know I can do a bulk insert or format the data as XML that insert it that way, but I've never done t...
Hi, Does the order of the columns in a WHERE clause effect performance? e.g. Say I put a column that has a higher potential for uniqueness first or visa versa? ...
I'm using Sqlserver express and I can't do before updated trigger. There's a other way to do that? ...