| Renaming system databases Posted: 26 Apr 2013 07:10 PM PDT Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master? |
| read txt file Posted: 26 Apr 2013 05:00 PM PDT hello all.i use this T-Sql for reading txt file:select * from OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="f:\rahnama.txt";Extended properties="Text;hdr=no"')...rahnama#txtbut gets error:OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "'f:\rahnama.txt' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Line 4Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".what is the problem?what do you do? |
| Sql Books Posted: 26 Apr 2013 04:25 PM PDT I have series of following books but i have not gone through those books yet. I am planning on reading them now. But i am thinking as 2012 is already out. Do you guys think that there is so much changes between sql 2005 and 2008R2/2012([b]in terms of content of these books[/b])?Is it good idea to read those books when sql 2012 is already out? Can someone tell me what are the features which are deprecated in sql 2008 R2 and sql 2012 and i do not need to learn from these books?Inside SQL Server 2005: TSQL Querying Inside SQL Server 2005: TSQL Programming Inside SQL Server 2005: The Storage EngineInside SQL Server 2005: Query Tuning and OptimizationThanks so much |
| System can't file specified Posted: 26 Apr 2013 02:51 PM PDT I am working on creating peer-to-peer replication testing.I have created Peer publication from DEV-> DBTEST. All log reader agent job working to be fine on DEV but it give me error on DBTEST as follow.Any help should be greatly appreciated. Please let me know if more information is required.Executed as user: sqlservices. Replication-Replication Transaction-Log Reader Subsystem: agent DBTEST-Test_PeerReplication-8 failed. The system cannot find the file specified. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.When i click on Publication -> View Log Reader Agent Status , it throw me an error System can't find the file specified. I am not sure how to set this file up. |
| convert xml into csv Posted: 26 Apr 2013 07:13 AM PDT I'm looking for an easy way to either convert xml to csv. I've tried using the SSIS xml source package way and I'm getting all kinds of truncation errors. Thanks in advance. |
| using NOLOCK in views Posted: 26 Apr 2013 05:40 AM PDT I've done a lot of reading on using WITH NOLOCK and understand the pitfalls. We have a situation where our nightly UPDATE process will get blocked by somebody. The other day it was a spotfire query that wasn't doing anything - but for some reason had kept a connection open since 12 hours before. A few weeks ago it was a PC SAS query that the user had killed but again for some reason had kept a connection to the database and held a lock which caused our nightly process to wait all night until we killed the transaction the next morning. We don't allow any updates during the day. So one argument of dirty reads if using WITH NOLOCK won't be an issue. We are not trying to use WITH NOLOCK to fix concurrency problems of transactions not committing either. It's just these random "read" transactions that will get hung for some reason and keep a lock. Would our idea of coding all VIEW's using the WITH NOLOCK work in preventing these errors? All access to our database is done via views. So anybody reading during the day (with PC SAS, spotfire, etc...) would not be taking any locks. If they got hung for some reason, there would not be any locks to get in the way of our nightly UPDATE process. Looking for opinions? We don't really have any way of testing because we can never recreate these hung transaction situations. |
| concerns using linked server -security. Posted: 26 Apr 2013 09:47 AM PDT We are designing of an application that using backend SQL database. When records are inserted into a SQL table, we also wanted to update records in an oracle database. The plan is to using linked server in SQL , and create an trigger when SQL server records are updated, then Oracle will be updated too.We don't use linked servers in our environment for Security reasons, but if it is needed we have to go this route. Any recommendations for security of linked server?Thanks, |
| WHILE loop to relieve contention in an update Posted: 26 Apr 2013 07:31 AM PDT We have a situation where the business requires some ETL to be done throughout the day on a production database. One of the procs updates an xml column for a set of rows. The developer has decided to change this proc to do a while loop and update row by row to relieve page contention and only take a row lock. Is this a good idea when speed of the proc is not a concern? Also, this is a while loop with a counter, not a cursor... so should this maybe be a cursor instead of a while loop? |
| Migration 2005 TO 2008 R2 Posted: 26 Apr 2013 09:00 AM PDT Hello,I have recently migrated one database from 2005 which was running on sql server 2000 compatibility to sql server 2008 R2. I have couple of maintenance plans which clean up the backup files which are older than 23 hours. I am getting the following error while running the daily maintenance job:Code: 0xC002F210 Source: {BE15E2A4-4E27-4617-85FC-769FA40B8C15} Execute SQL Task Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp..." failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Code: 0xC0024104 Source: Maintenance Cleanup Task Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: Code: 0xC0024104 Source: {C1CAE90D-B745-4E00-B5DD-9A31CF44895F} Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed.I have checked the msdb database but there is not table called dbo.sysmaintplan_subplans both is 2005 and 2008 R2. But the job was running fine in 2005 which was running in sql server 2000 compatibility.I really appreciate all the help I can get on this.. |
| Grant select, exec permission to SP, inline, scalar & table-values functions Posted: 26 Apr 2013 06:12 AM PDT I need to grant a user to select & exec permissions to SP, inline, scalar & table-values functions....but I need this done in every databases. There are 50 databases on this instance.Anyone have a script to accomplish this? Please help. Thanks.SueTons. |
| Convert SQL To Access (Generation of random values at time intervals) Posted: 26 Apr 2013 06:44 AM PDT I'm hoping someone can help me with my delema. I've posted it in detail here[url]http://stackoverflow.com/questions/16243905/convert-sql-to-access-generation-of-random-values-at-time-intervals[/url]Basically, My goal is to create a make-shift model that will never run out of data because time is auto incremented with a random value. I did this in SQL but now I need to do it in Access and I have basically no experience. Any help even just reccomending certain articles or sites is very much appreciated. Thanks |
| Unique Constraint Across More Than One Column Posted: 26 Apr 2013 05:47 AM PDT Hi,I have a table that contains, among other columns, 4 columns that contain the same type of data, call them Chip1, Chip2, Chip3, Chip4. I want to create an Index that will check that the same entry cannot appear more than once in any row of any of the columns.So, if Chip1 = ABC then ABC can only appear once in Chip1 and not at all in Chip2, 3 or 4. It would be better to do this as a one-to-many to another table but unfortunately the structure exists already. Is there a way of setting up a constraint that will prevent duplicates?Thanks |
| Suspended Commands on master db Posted: 30 Sep 2012 04:23 PM PDT HiI'm hoping someone will be able to cast some light on my current server issue.There are a number of commands which on the master db which have a suspended status, with significant wait times along with them.Command........................Wait Time...........Wait TypeSIGNAL HANDLER..............269275190..........KSOURCE_WAKEUPTASK MANAGER................269282488..........ONDEMAND_TASK_QUEUEBRKR EVENT HNDLR...........269276124..........BROKER_EVENTHANDLERBRKR TASK......................269282100..........BROKER_TRANSMITTERBRKR TASK......................269282095..........BROKER_TRANSMITTERFT GATHERER...................470649..............FT_IFTS_SCHEDULER_IDLE_WAIT(Apologies for my crude table)None of these have a Blocked By SPID listed against them. If my calculations are correct, the longer suspended tasks were all put into this state around 72 hours ago - in my part of the world that means Friday evening at around 6pm. This points the finger at one or a combination of scheduled jobs involved in processing XML data rerieved from an external webservice. Unfortunately the logs that far back have now been purged - cause for me to reconsider our clean up regime.From my research so far I get it that these are system tasks and as such they cannot be killed. I also understand that the BRKR tasks are created by the Service Broker and probably relate to our use of DB Mail or some other background operation.I'm seeking to understand what may have given rise to these and why. Later this evening I will restart the server, expecting these tasks to clear during that process. The need to uderstand what is going on is driven by wanting to avoid them in the future as I suspect that they have played a role in very poor performance from the server today.I look forward to someone perhaps being in a position to provide some possible explanation of this situation. My thanks in advance.CheersRowan |
| How to delete the duplicate row without any unique value? please help Posted: 26 Apr 2013 02:40 AM PDT I have a table of contacts that has no unique key on it. The users have entered multiple copies of several people's information and I want to remove the duplicate values.Here are the column namesLast_Name, First_NAme, Department, Email, phone, Sid (sid can be null)Please help- |
| Backups Failing Posted: 26 Apr 2013 01:28 AM PDT Hi, In my production environment there are only two drives C: and D: drives. The backups has to go the mapped drive named Z: The backups are failing now.I went to backup maintenance plans history and found this message Error msg: Could not find a part of the path 'z:\\xxx_TransactionLog_20130426091520.txt'.can any one help me whats going on here. |
| SQLPS vs SSMS Posted: 26 Apr 2013 02:58 AM PDT Ran into a SNAFU. I have a simple script that will "create" creation scripts for any object in an object folder. (get-childitem | %{$_.Script()} > C:\temp\whatever.sql)Let's use the example I want to script all my JOBS to be recreated on another server. It should be you run this script, load the "whatever" on another server and "there ya go'. But, right after the first job is created the sql failures begin. I looked a little closer and found the script generation leaves out the GO command that should be there just prior to the next BEGIN TRANSACTION. No problem, I use WORD and a little Find/Replace magic and we're good to go. (BTW check out ^c in the Replace box)If you open the Detailed Obj Expl in SSMS and highlight all the jobs, you can right click and get the same script except the GO is there. Since Microsoft uses PowerShell under the covers I never expected this result. Did I miss something or is this a no go plan?I guess I could extend the script to put the GO in , but REALLY... |
| wich OS is better for slq server 2008 R2 Posted: 26 Apr 2013 12:13 AM PDT Iam creating a new windows server in the cloud (Dediserve), this will be a database server and I should use SQL SERVER 2008 R2 standard edition, my question is: what OS I should use to a better sql server performance?windows 2008 r2 or windows 2012, both standard eddition.Thanks |
| Which one is better? Posted: 25 Apr 2013 10:19 PM PDT Here is sample code below:Both queries will retun same result.But I want to know which one better to use? CREATE TABLE #temp1 (ID int, Name varchar(10))INSERT INTO #temp1 VALUES (1,'A'),(2,'B'),(3,'C')CREATE TABLE #temp2 (ID int, Name varchar(10))INSERT INTO #temp2 VALUES (1,'A'),(2,'B'),(4,'D')SELECT t.ID, t.Name FROM #temp1 tLEFT JOIN #temp2 t1 ON t.ID = t1.ID WHERE t1.ID IS NULLSELECT t.ID, t.Name FROM #temp1 t EXCEPTSELECT t1.id, t1.name FROM #temp2 t1 |
