SQL DB User's changes
I , unknowingly, create a user in database with loginname of 'windows_user' instead of sql server user , now enable to drop. pl suggest the way .. ...
sql-server
SQl to select from multiple tables
I have two table emptable1(empid,status) emptable2(empid,week) i want to select all the empid whose status is 0 in emptable1 and from that list of empid i need to select empid whose week is 7 from the table emptable2 Please help :-) ...
the best way to connect sql server (Windows authentication vs SQL Server authentication) for asp.net app
I have a database and a site having forms authentication. It is working fine with VS2008. This time, I am using "Trusted_connection =True". But when it is opened from outside or directly from browser then I am getting error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." I know this is due to permission. SQL server is based on w...
How determine database server IP via UDP in Java
Hi, I am writing clients in Java that store the data on a database server. So far, the IP and Port of the server has to be specified in the client's settings manually. I have heard, that it is possible to automatically determine the IP of database servers via broadcast / multicast / UDP (I am not familiar with these concepts). Question: ...
How to make awkward pivot of sql table in SQL Server 2005?
I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format? Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time. Given ...
Is a primary key automatically an index?
If I run Profiler, then it suggests a lot of indexes like this one CREATE CLUSTERED INDEX [_dta_index_Users_c_9_292912115__K1] ON [dbo].[Users] ( [UserId] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] UserId is the primary key of the table Users. Is this index better than the...
Table in DB for generating primary keys?
Do you ever use a separate table for "generating" artificial primary keys for DB (and why)? What I mean is to have a table with two columns, table name and current ID - with which you could get new "ID" for some table by simply locking the row with that table name, getting the current value of the key, increment it by one, and unlock the...
Does SQL Server have any kind of magic undo feature?
Long story short is I tried to quickly update a single row in SQL Server using the Management studio and just typed UPDATE table SET column='value' and forgot the WHERE other_column='other_value' portion. Went for lunch, came back and theres 15 unread emails waiting for me. Happened about an hour ago, waiting for the database guy to co...
Profiling statements inside a User-Defined Function
I'm trying to use SQL Server Profiler (2005) to track down some application performance problems. One of the calls being made is to a table-valued user-defined function. This function wraps a select that joins several tables together. In SQL Server Profiler, the call to the UDF is logged. However, the select that underlies the UDF isn't...
How do I insert into a unique key into a table?
I want to insert data into a table where I don't know the next unique key that I need. I'm not sure how to format my INSERT query so that the value of the Key field is 1 greater than the maximum value for the key in the table. I know this is a hack, but I'm just running a quick test against a database and need to make sure I always sen...
Subscription website architecture questions + SQL Server & .NET
Hey Guys, I have a few questions about the architecture of a subscription service I am about to embark on and I am looking for some feedback on how best to set it up. I won’t have a large amount of customers as Basecamp, maybe a few hundred and was wondering what would be a solid architecture for setting up the customer sites. I’m runn...
Can somebody give me detailed construction how to find table scans with Sql Profiler?
I'm using SqlServer 2008. A usefull link is also ok. I've found some links, but since I'm not an expert with Sql Profiler, I can't seem to find how I would do this. By the way, some data is retrieved with Stored Procedures, but others are done with sql in the .NET server layer. ...
How can I compare rows from 2 tables that have composite primary keys?
Here's the scenario: I have 2 tables with data, one is the 2009 version and the other is the 2010 version. The primary key for each of the tables is a composite key. I know there is a different number of rows in each one and I need to find out the differences. Typically, in the "normal" primary key set-up, I would just look for primary...
SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns
Scenario: Table A MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/1/2010, 'Fred', null, null 2, 1/2/2010, 'Barney', 'Mr. Slate', 1/7/2010 3, 1/3/2010, 'Noname', null, null Table B MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/3/2010, 'Wilma', 'The Great Kazoo', 1/5/2010 2, 1/4/2010, 'Betty', 'Dino', 1/...
Is there a way to convert a non-clustered index on a Primary Key to a clustered one? (SQL Server 2008)
With Management Studio I can't alter the index. Dropping it doesn't work because it's being used as a foreign key in a lot of other tables. Can I somehow alter it? Or how would you do this? ...
Is there a standard pattern for scanning a job table executing some actions?
(I realize that my title is poor. If after reading the question you have an improvement in mind, please either edit it or tell me and I'll change it.) I have the relatively common scenario of a job table which has 1 row for some thing that needs to be done. For example, it could be a list of emails to be sent. The table looks somethi...
Why use INCLUDE in a SQL index
I recently encountered an index in a database I maintain that was of the form: CREATE INDEX [IX_Foo] ON [Foo] ( Id ASC ) INCLUDE ( SubId ) In this particular case, the performance problem that I was encountering (a slow SELECT filtering on both Id and SubId) could be fixed by simply moving the SubId column into the index proper rathe...
Validate a string in a table in SQL Server - CLR function or T-SQL (Question updated)
I need to check If a column value (string) in SQL server table starts with a small letter and can only contain '_', '-', numbers and alphabets. I know I can use a SQL server CLR function for that. However, I am trying to implement that validation using a scalar UDF and could make very little here...I can use 'NOT LIKE', but I am not sure...
How I can move table to another filegroup ?
Hello, I have SQL Server 2008 Ent and OLTP database with two big tables. How I can move this tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in this tables. Each table have about 100M records and six fields (including one field geography). I looking for ...
Filling data in FormView from different tables
Hi, I am using a FormView in an online quiz page for displaying the questions and RadioButtons (for answers) http://stackoverflow.com/questions/2438219/online-quiz-using-asp-dot-net Now, I need to pick the questions according to a TestID and a particular Set of that Test. The testid and the set_number would be passed using Session...