Hi there, I'm stuck on a problem with sql. I have a table with many duplicate entries with column names like:- eventnumber housenumber value1 value2 None of these column names are the primary key as there are many duplicates. What I would like to do is select into another table by distinct housenumber but I seem to get the whole tabl...
Hello I want to generate a Unique Random number with out using the follow statement : Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM] Cause when I use joins clauses on "from" it generates double registers by using NEWID() Im using SQL Server 2000 *PD : When I use Rand() it probably repeat on probability 1 of 100000000 but this ...
I have a site using the asp.net membership schema. I'd like to set up a trigger on the aspnet_users table that inserted the user_id and the user_name of the new row into another table. How do I go about getting the values from the last insert? I can select by the last date_created but that seems smelly. Is there a better way? ...
I am trying to determine what indexes are no longer used in my Database. I have had great luck using the following query: SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], i.Type_Desc as [Index Type], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES F...
DB: SQL Server 2005 We have a table that has data in this manner: Project Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov ...
I would like to create a stored procedure that takes in a string of comma separated values like this "1,2,3,4", and break it apart and use those numbers to run a query on a different table. so in the same stored procedure it would do something like select somefield from sometable where somefield = 1 select somefield from sometable wher...
Hi, my default SQL Server 2005 log directory is full on C drive. In order to prevent this issue happening in future, I plan to move the default log directory to some other place. Could you please tell me how I can move the error log default directory? I browsed the web, there is solution for SQL Server 7 and 2000 but not 2005. Please ...
I have two long running queries that are both on transactions and access the same table but completely separate rows in those tables. These queries also perform some update and inserts based on those queries. It appears that when these run concurrently that they encounter a lock of some kind and it’s preventing the task from finishing ...
I'm trying to debug the source of a SQL timeout in a web application that I maintain. I have the source code of the C# code behind, so I know exactly what code is running. I have debugged the application right down to the line that executes the SQL code that times out, and I watch the query running in SQL profiler. When this query ...
If I have a view, and embed the view in a query, will the view have to be processed fully before the rest of the query? Example: CREATE VIEW dbo.ExpensiveView AS SELECT IndexedColumn, NonIndexedColumn FROM dbo.BigHairyTable WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000 GO SELECT * FROM dbo.ExpensiveView WHERE In...
I ran this query: SELECT i.name AS IndexName, s.used_page_count * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Stu') ORDER BY i.name and the largest index returned ...
I have a some questions about local and service-based databases: Does using a service-based database require the user to have SQL Server installed? If so, is there ANY way around it? Does a local database require the user to have SQL Server installed? What is the difference between a local database and a service-based database. (I am tal...
I have a problem. I have an application in C# with SQL SERVER 2005 as backend. The problem is in fetching the correct record based on the date. The frontend code is if (string.IsNullOrEmpty(txtFromDate.Text)) SelectCmd.Parameters[0].Value = DBNull.Value; else SelectCmd.Parameters[0].Value = txtFromDate.Text; Now if I ru...
Hi, Here's my situation (SQL Server): I have a web application that utilizes nHibernate for data access, and another 3 desktop applications. All access the same database, and are likely to utilize the same tables at any one time. Now, with the help of NH I'm batching selects in order to load an aggregate with all of its hierarchy - so...
I have a sql script to create a new database which i need to create when our product is installed. For this i need to fire the script using c#. DB is sql-server 2005 express. Plz help.... The sql script is as follows: USE [master] GO /****** Object: Database [Jai] Script Date: 02/12/2010 11:01:25 ******/ CREATE DATABASE [Jai] ON P...
sp_help lists columns among other things. I am trying to get just the results that include the column information. Has anyone implemented this? ...
I have a table on which there is update trigger written it has print statement before go statement. ALTER TRIGGER user_type_check ON user_table --code PRINT 'Modification of user is done.' GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Now with this structure whenever i perform update operation on the table it fai...
Is there a way to do something like this without converting the sql to a string and calling exec DECLARE @source_database varvhar(200) SELECT @source_database = 'wibble' SELECT * FROM SELECT @source_database.dbo.mytable ...
Is there any way of aliasing datatypes in T-SQL (SQL Server 2005), a bit like a C typedef? I'd like to be able to alias, say, VARCHAR(20) as 'my_datatype' so that wherever I wish to use VARCHAR(20) I could instead use my_datatype. Synonyms allow you to do this sort of thing for tables, and there are built-in synonyms for some datatypes ...
I could probably google this, but it seemed quirky enough that it might be worth having logged as an answer on SA. So in development land, if you want to swap interchance the values of two variables, you need a third temp variable. e.g. string x = "ABC"; string y = "DEF"; string temp; temp = x; x = y; y = temp; However in a SQL Up...