I guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
I searched for all the posts which covered my question - but none were close enough to answer what i'm trying to do. Basically, the scenario is thus;
Table1 contains values for UserID, Account code, and Date.
My query (below) is trying to find all the accounts assigned to a particular user ID, but also those duplicate account codes which belong to a second user ID. The date column would be appended to the result set.
The query I'm using is as follows;
select acccountcode, userid, date from dbo.table1 where exists (select accountcode from dbo.table1 where accountcode = table1.accountcode group by accountcode having count(*) > 1) and userid = 'x-x-x' order by accountcode
What I think this produces is a list of all files where a duplicate exists, but of course it leaves out the 2nd UserID...which is crucial.
Hopefully this makes sense. Any insight my fellow DBA's can share would be greatly appreciated!
I need to create a table that would be the result set of a comparisonbetween table a and table b? Table a and b first 2 fields will always bethe same (CustomerName and CustomerNumber). But if the Address1 fieldchanges in table a, I would like to throw that whole row into mycomparison table. Almost like a Select Into with a sub query that wouldinclude a WHERE TableA.field <> TableB.field. I would need to do thiscomparison for about 8 fields. Help appreciated for my syntax is prettybad. Thanks.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I want to find out which records were created within a given period of time per user. For example I want to know if the user "georgev" (or any users) has created more than one record in this table within a 2 minute period.
Hopefully thsi will warm your brains this cold Monday morning :p
Code below: [Code SQL] USE TestWarehouse IF ('dbo.TestItems') IS NOT NULL DROP TABLE [dbo].[TestItems]; GO CREATE TABLE [dbo].[TestItems] ( TestItem int not null IDENTITY (1,1)
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b task_c task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus: queue_num task_num team queue_1 task_a red task_b red task_c red task_d red
No bonus for any team here queue_num task_num team queue_1 task_a red task_b blue task_c red task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
I need to find the name of the user that signed in to the web page. For this, I have a web page with a text box, the program should access a SQL database use, use the userID and return the name. My code (working) does the following:string mySqlQuery = ""; string strCnn = "";SqlDataReader myDataReader = null; mySqlQuery = "SELECT c_name FROM database WHERE c_user='demo'"; //connect to the databasestrCnn = "Data Source=" + connectionServer + "; Initial Catalog=" + connectionDatabase + "; user id=" + connectionUserID + "; password=" + connectionPassword + "; Integrated Security=false";cnn = new SqlConnection(strCnn);
try { cnn.Open();MessageBox.Show("Success Opening the Database"); } catch {MessageBox.Show("Problems with the database"); }SqlCommand sqlSelectCommand = new SqlCommand(mySqlQuery, cnn);
tbName.Text = "me"; cnn.Close();
This works, I just need to retrieve the value of c_name. Any help, thanks
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
create table dbo.customer ( customer_id int identity primary key clustered, customer_name nvarchar(256) not null ) create table dbo.purchase_order ( purchase_order_id int identity primary key clustered customer_id int not null, amount money not null, order_date date not null )
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
On SQL 6.5 we have had a couple of instances recently where an orphan connection cannot be killed and the only way to get rid of it is to stop and start the service. Does anyone know of any other way to get rid of that orphan connection?
As I do analysis on a database I am finding multiple clustered indexes defined on a single table. I know this is not possible since SQL Server only allows one clustered index per table. With more research I found these suspected clustered indexes have a corresponding row in sysindexes but without a correlating id match in either sysobjects or syscolumns. Is this orphaning of indexes common and what is a recommended solution. Thanks, Randy Garland
/************************************************************************************* This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server. *************************************************************************************/
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_fixusers IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>' END
GO
CREATE PROCEDURE dbo.sp_fixusers
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR FOR
SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers INTO @username
WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END
CLOSE fixusers DEALLOCATE fixusers END go IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>' go
I am not sure if this is actually a sql connection I didn't dispose of.The database the app use is franchise_search, login testHere are the results of sp_who before I start the web server and run the page1 0 background sa 0 NULL RESOURCE MONITOR 02 0 background sa 0 NULL LAZY WRITER 03 0 suspended sa 0 NULL LOG WRITER 04 0 background sa 0 NULL LOCK MONITOR 05 0 background sa 0 master SIGNAL HANDLER 06 0 sleeping sa 0 master TASK MANAGER 07 0 background sa 0 master TRACE QUEUE TASK 08 0 sleeping sa 0 NULL UNKNOWN TOKEN 09 0 background sa 0 master BRKR TASK 010 0 background sa 0 master TASK MANAGER 011 0 suspended sa 0 master CHECKPOINT 012 0 background sa 0 master BRKR EVENT HNDLR 013 0 background sa 0 master BRKR TASK 014 0 sleeping sa 0 master TASK MANAGER 015 0 sleeping sa 0 master TASK MANAGER 016 0 sleeping sa 0 master TASK MANAGER 017 0 sleeping sa 0 master TASK MANAGER 018 0 sleeping sa 0 master TASK MANAGER 020 0 sleeping sa 0 master TASK MANAGER 022 0 sleeping sa 0 master TASK MANAGER 024 0 sleeping sa 0 master TASK MANAGER 051 0 sleeping NT AUTHORITYSYSTEM HPDEV 0 msdb AWAITING COMMAND 052 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 053 0 sleeping sa HPDEV 0 master AWAITING COMMAND 054 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 055 0 runnable sa HPDEV 0 master SELECT 0 then I run the page and all I do is using (m_sqlConn = new SqlConnection(m_strSQLConnect)) { m_sqlConn.Open(); //Log_History(); //FillCompany_DropDown(); //m_sqlConn.Open(); //BuildCategoies(); }I open the connection never use it and return the using should close the connection.But after I close the page sp_who add the line56 0 sleeping test HPDEV 0 Franchise_Search AWAITING COMMAND 0Is this connection pooling or am I missing something?No other app or anything can use the test connection I just made it and changed the password. Jon
Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?
i have an app that starts a transaction (some deletes, etc...) in the middle of this transaction the connection drops (say we pull out our network cable) but this will leave the transaction still running and it will be orphaned. i know i can kill them in sql server, but what i'm looking for this to somehow not happen at all. I tried SET XACT_ABORT ON but no luck.
Is what i need even possible?
_______________________________________________ Causing trouble since 1980 blog: http://weblogs.sqlteam.com/mladenp SSMS Add-in that does a few things: www.ssmstoolspack.com
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
We have 4 sql servers which service 4 load balanced web servers (with sticky sessions) and we recently brought one of the four SQL servers online.
In the last week the new SQL Server 2000 Standard (SP4) has been exhibing orphaned connections and attempts to use invalid connections out of the connection pool on the app servers. This manifests itself in General network Errors and Timeouts (when the sql is not really doing much other than simple table requests).
Does anyone have any experience in what might be causing this on a new server. I've asked our networking people to verify that the network routing and firewall are set the same as the other three servers. The application is the same acrossed all of the app servers so I've ruled out any issues with the application not closing connections.
I€™ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03. The mirroring as all gone according to plan.
Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don€™t work. I€™ve read through other posts and found links to this SQL Server article: http://support.microsoft.com/kb/918992/
This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID€™s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not??
Can anyone point me in the right direction of what to do next? Or have any ideas.
Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:
SQL 7.0, NT4SP6, 2G ram, 4x
I've got a user process as follows: Status: ROLLBACK Command: SELECT Application: Enterprise Manager Wait Type: EXCHANGE Login time: 06/25/00 4:07:05AM Last batch: 06/25/00 4:07:20 AM The last TSQL command batch is a correlated subquery with grouping...
Apparently it hung and the user quit ungracefully.
No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.
I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.
Mgmt is reluctant to bounce the database, as am I.
I am copying a database to an alternate server by restoring a full backup onto the new server. However, whether I create the logins prior to the restoration or not the user accounts in the database no longer map to logins in the master.
Unfortunately it is not simply a case of dropping the acounts as they own objects in the database.
My best solution to date has been to use the sp_addalias to alias logins of the same name to the original user. However this is a far from ideal situation as one cannot readily tell that the logins are aliased and there must be an additional overhead in doing this.
I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.
How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.
Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".
Am I right? Or is there a way of attaching it that doesn't require it to have been detached?
All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$' Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select * from sys.database_permissions where grantor_principal_id = user_id ('NetworkSERVERNAME$' -- class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc 17 SERVICE 65538 0 5 19 SN SEND G GRANT And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.
On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.
To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.