Wednesday, July 3, 2013

[SQL 2012] A question about replacing a character in a nvarchar

[SQL 2012] A question about replacing a character in a nvarchar

A question about replacing a character in a nvarchar

Posted: 03 Jul 2013 01:04 AM PDT

Hello,First of all, i apologize if this was asked before. I did search before asking, so please don't get mad:-)I have a string (nvarchar). Using replace, i can replace a character in this string with another one.But what if i wanted to do something like this:[code="sql"]select replace(replace('abc', 'a', 'b'), 'b', 'z')[/code]Not only that, but what if i want to replace 10 characters. The code will look monstrous:[code="sql"]SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('abcdefghijklmn', 'a', 'b'), 'b', 'c'), 'c', 'd'), 'd', 'e'), 'e', 'f'), 'f', 'g'), 'g', 'h'), 'h', 'j'), 'j', 'k'), 'k', 'i')[/code]Is there a better way, that will not sacrifice the performance? I tried using a recursive CTE, but the performance is really really bad.Thank you for your time:)Cheers,Radu

Restoring a backup of an existing DB to a new name on the same server and getting exclusive lock error

Posted: 03 Jul 2013 02:43 AM PDT

I am attempting to restore a version of my current database ("JamestonDB") to a new name ("JamestonDB0628"). I have been successfull restoring other databases but this one is giving me "cannot obtain exclusive lock error". This database is more heavily used, but since I am creating a "new" database I do not understand the error.

2012 and Always on - different security access?

Posted: 03 Jul 2013 01:16 AM PDT

Here is what I would like to do and wanted to know if possible.With always on, have one active node for production applications, one node for reporting with different logins permitted, and one node for backups.Is it possible to have a different set of logins per node?I am trying to sell 2012 to where I work and one thing we are looking at is using transactional replication in 2008 R2 or go to always on in 2012. Just depends on the security options.Thank you.Kameron

Stop users from expanding the Tables nodes in Object Explorer

Posted: 03 Jul 2013 12:49 AM PDT

I would like to know if there is a way to stop users from being able to see the tables of my database by stopping users from being able to expand the Table node in the Object Explorer?

SQL Developer Edition Question

Posted: 02 Jul 2013 03:23 AM PDT

Is it possible to restrict SQL Developer edition to only allow features in Standard edition? Our production environment is Standard Edition, and we are considering using Dev Edition in our dev environment due to the licensing, but we want to make sure whatever breaks in prod due to editions restrictions will break in dev.

Log Shipping Backup Very Slow (sqllogship.exe)

Posted: 02 Jul 2013 06:14 AM PDT

Hello, We have a SQL Server instance with ~200 DBs on it. Each one is log shipped to a remote site. To avoid all jobs going off at once, I've scripted sqllogship.exe to run in 1 job sequentially. Anyway, I am finding that each DB is taking 30 seconds to complete. I have log shipping on servers with ~100 DBs similarly sized and with similar data that works fine, taking seconds on each DB. I have invoked sqllogship.exe from the command prompt and indeed it took 30 seconds. So the problem lies within sqllogship.exe. I thought perhaps these 200 DBs have created a large MSDB that is slowing down sqllogship so I ran sp_delete_backuphistory and eventually deleted everything but the last 30 days, which leaves about 100k records, which is very small compared to the other servers that have a much faster time running sqllogship. I also added some indexes I thought could help (backupset(media_set_id), restorefile(restore_history_id), restorefilegroup(restore_history_id)) but they did not affect the 30 second runtime. What is odd is that it has been 30 seconds exactly on several databases. Another clue is that backupset logs the backups being made by sqllogship.exe as being finished in a second or so...so where are the other 29 seconds coming from? I tried setting verboselevel to 0 in sqllogship but that doesn't help either. All of our servers have this version:Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)Thanks!

Setting up SQL Server 2012 AlwaysOn, can't get error msgs to clear

Posted: 07 Mar 2013 09:00 AM PST

I'm trying to set up an SQL 2012 Availability Group between 'ServerA' and 'ServerB'On the source server (ServerA) I get the following message in the 'AlwaysOn High Availability' tab in SQL Server Configuration Manager: 'An error occurred while loading the AlwaysOn High Availability properties [return code: 0x80070005]. This server is running Windows Server 2008 R2 Enterprise and SQL Server 2012 SP1 Enterprise edition, but I can't confirm if the hotfix in KB2494036 has been installed as I don't have admin rights on the server (don't ask). The server admin said he was going to install it last night but I haven't confirmed with him, and he's left the office for the night.On the destination server (ServerB) I get the standard error message saying that SQL 2012 Enterprise Edition, Windows Server 2008 and KB2494036 need to be installed. This server is also running Windows Server 2008 R2 Enterprise and SQL Server 2012 SP1 Enterprise edition, and I can confirm the hotfix has been installed.When I try to run the Availability Group wizard on ServerA, on the Replicas tab, when I put in the credentials for ServerB, I get the error message: "Cannot connect to QA-SQL-LOAD1\VRS70. AlwaysOn feature is not enabled on SQL Server instance 'QA-SQL-LOAD1\VRS70'. (Microsoft.SqlServer.Management.HadrTasks)"Getting clear installation and troubleshooting documentation on setting this up has been quite challenging (which might be part of my problem...) - does anyone have any ideas on where to go from here?TIA,Mike

Copy Tables to Another DB on Same Server - Error

Posted: 02 Jul 2013 07:36 AM PDT

As I process each month, I am copying some of the tables in my DB (db1) to another DB (ab1_archive)on the same server. This is to allow users access to older data from a web front-end. The tables are renamed from tbl1 to tbl1_201305 - the suffix being year and month.This is done in a stored proc in db1. It worked once, but is now giving error that either db1_archive does not exist or there is no permission. I am not sure what changed. Some of the tables are getting copied though. What am I doing wrong? is there a permission on the table that I need to change? If so, how? Thanks in advance.[code="sql"]BEGIN if @thruPeriod is null or @thruPeriod = '' BEGIN PRINT(N'The value cannot be null or empty.') END ELSE BEGIN --final output EXEC ('SELECT * INTO tbl1_' + @thruPeriod + ' FROM tbl1') ENDEND[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog