Tuesday, July 16, 2013

[SQL Server 2008 issues] quick edit data

[SQL Server 2008 issues] quick edit data

quick edit data

Posted: 15 Jul 2013 05:49 AM PDT

I recently found a quick way to do some data editing for a table with filter added.That is from design view window from management studio.when creating a new view, I can edit the data in the result panel.is that a valid way of doing data modification?Thanks,

Role Permission

Posted: 15 Jul 2013 05:52 PM PDT

Hi allSimple question, If I grant user permission to role "DbCreator", does that user have access to create databases and database objects?Thanks!

create linked server using windows authentication

Posted: 15 Jul 2013 05:16 PM PDT

Hi, I have a peculiar requirement but was not able to go much ahead. Setup a linked server on InstA(mixed mode authentication) to fetch data from InstB(Windows Only). The user accessing the linked server is a SQL Authenticated user on InstA. When i use the below script (from another post in sqlservercentral.com).[code="sql"]DECLARE @strLinkedServer NVARCHAR(100)SELECT @strLinkedServer = 'SRV2\InstB'EXECUTE master.dbo.sp_addlinkedserver @strLinkedServer, 'SQL Server'EXECUTE master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @strLinkedServer, @useself = N'False', @locallogin = N'sqluserA', -- add local login @rmtuser = N'Domain1\user', -- add the remote login @rmtpassword = 'StrongPassword' -- add the remote login password[/code]This seems to work in terms of creating the linked server. While trying to use it, i get the error [font="Courier New"]Msg 18456, Level 14, State 1, Server SRV2\InstB, Line 1Login failed for user 'Domain1\user'.[/font]I get the below error in the SQL Server Error logs on InstB[b][font="Courier New"]Login failed for user 'Domain1\user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.1.1.11][/font][/b]Looks like I would not be able to create a linked server mapping a local SQL Authenticated user to a domain user account for the remote login. Is there a work around for this?Cheers

DATABASE BACK UP PROBLEM

Posted: 15 Jul 2013 04:46 PM PDT

Correct me ,, if i am posting my problem in a wrong forum.Well,,,Here i in my company i am using full recovery model for the database back up plan.The Process is like this:SUNDAY: FULL BACK UPMONDAY-SATURDAY: Differential Backup Each Dayand, Transaction log back up every hour.I.e. on Sunday at 12:00 AM Full back up will be taken,,,, then hourly transaction log back up and on Monday 12:00 AM a Differential Back up...transaction as hourly. (This gives us A WEEK SET of database)Now,the problem i am facing is that..... suppose in between Sunday Full back up - Monday differential back up,,, some Developer takes a full backup of the database in an emergency or any situation,, then in that case,,, the Monday differential backup will be of the Latest Full (i.e. in case,, the developer's one) ,,, which breaks my process.So,, can any one suggest me a Script that Gives the Full back up to the developer but does not break my process. (i believe its all about the LSN ,, i am very new to SQL)Request: To all Sql Server masters,, please Help..!! :-)

Handling error message

Posted: 14 Jul 2013 10:10 PM PDT

Hi, while writing script I have to check that whether input value is correct or not and then display the error message for that...What I did is use RAISERROR[code="sql"]RAISERROR('Invalid Amount',20,1) WITH LOG [/code]Here is some part of my code:[code="sql"]IF NOT EXISTS (SELECT (a.VoucherNo) FROM GV_Voucher a WHERE a.TransactionID = @TransactionID AND a.VoucherNo = @FirstVoucherNo )BEGIN SET @ErrorMessage = @ErrorMessage + ' ' + 'First ' + @message2 RAISERROR('User Define: Invalid First Voucher No |', 20, 1) WITH LOGENDIF NOT EXISTS (SELECT (a.VoucherNo) FROM GV_Voucher a WHERE a.TransactionID = @TransactionID AND a.VoucherNo = @LastVoucherNo)BEGIN RAISERROR('User Define: Invalid Last Voucher No |', 20, 1) WITH LOG SET @ErrorMessage = @ErrorMessage + ' ' +'Last' + @message2END[/code]If I handle error in this manner will it make any performance effect on my procedure.

Tempdb files - Load test

Posted: 15 Jul 2013 06:09 AM PDT

How to arrive at the number of tempdb files needed for a SQL 2008 2 processor server. Everyone mentions about having 1/4 or 1/2 or equal to the number of cpu's. But, finally it varies depending on the setup and the load. So, how can i perform a load test and arrive at an optimal number of tempdb files?

Parsing a string...between a comma and space

Posted: 16 Aug 2011 03:53 AM PDT

I have a concatenated field with name which I'm trying to parse out. The names are entered as so: Lastname, Firstname or sometimes... Lastname, Firstname Middleinit (notice the space between the firstname and middleinit)In order to parse out the name, I have the following:[code="sql"]SELECT LTRIM(RIGHT(@Name, CHARINDEX(',',REVERSE((@Name))-1)) --TO FIND FIRSTNAMESELECT LEFT((@Name, CHARINDEX(',', (@Name)-1) --TO FIND LASTNAME[/code]But the code for the first name will only pickup the correct firstname in cases where there's been no initial entered. How do I select out the substring between the comma and the space after the firstname (so it doesn't matter if there's a middle initial entered or not)?For example: "MOUSE, MICKEY X" or "MOUSE, MICKEY" = MICKEY MOUSEHelp?Thank you!!

Truncating data on a remote Server

Posted: 15 Jul 2013 04:29 AM PDT

Hello,I am trying to truncate data from one server to the other (remote server)[u][b]it works from query analyzer like this:[/b][/u]exec RemoteServerName.DatabaseName.DBO.sp_executesql "truncate table DBO.tablename"[u][b]it does NOT work from query analyzer like this:[/b][/u]Declare @cmd varchar(2000)set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'exec xp_cmdshell @cmd[u][b]Produced this error:[/b][/u]'RemoteServerName.DatabaseName.DBO.sp_executesql' is not recognized as an internal or external command,operable program or batch file.Please Help !

User datafeed overwrite with ssis package

Posted: 14 Jul 2013 10:56 PM PDT

Hi,I have tables user and userprofiles. I get datafeed every night in csv file. I need to create ssis package to overwrite data everyday. I need to update the existing user data,add the new user data and disable the user if he is not in the datafeed.In the past I used to import this data into a new table called userdatafeed and overwrite the data using a stored procedure. But now the requirement is to implement this SSIS package.What data flow items I can use to achieve this?

Selecting records in table only when ALL related records in other table satisfy multiple conditions

Posted: 15 Jul 2013 03:23 AM PDT

Hello and thank you for your time. I feel like this should be simple but I can't seem to work it out….maybe I have been working on it so long that I need another perspective.I have a Lab_result_obr_p table that contains labs that have been resulted: The PK is LabNum which is the lab Number and the FK is OrderNum which is the order number. So in this table one order# can have multiple lab#sTable: Lab_result_obr_p:LabNum OrderNum A29E02FE D2C6DDA84A563D24 D2C6DDA80F53BC60 DC01E4EBGS43A689 DC01E4EBF943C7034 DF2654G7The other table is Lab_rsults_obx and it contains the lab components and individual component results. In this table, each lab# (uique_obr_Number) from above will have multiple lab comments and results Table: Lab_rsults_obxLabNem LabC Flag ValueA29E02FE WBC N 3.5A29E02FE RBC N 260 4A563D24 LMP: N 504A563D24 RH TYPE N DNR0F53BC60 BACTERIA N TNPGS43A689 MCV N 30GS43A689 MCH N 40F943C7034 RH TYPE Y NegativeI need to select all ngn_order_num from Lab_result_obr_p where all components of all labs has a Abnormal_Flag of N and does not have a value of TNP. So if an Order has two labs, I need all the components for both labs to have an N for Abnormal_Flag and to not have a value of TNP for the order number to be selected So for the data above my result would look like:ngn_order_num D2C6DDA8

Get date only and order by in correct way

Posted: 15 Jul 2013 02:43 AM PDT

I converted orderdate to get date only using code below.convert(varchar(20), orderdate, 101) The problem is that orderdate can not be sorted correctly. (because it is string now)For example, 05/20/2010 will list first when compare to 10/20/2005 (order by orderdate asc)How to solve this problem?

Login Frequency for a Login on a database

Posted: 15 Jul 2013 04:06 AM PDT

Hello SQL Experts,I have a SQL Server 2008 Standared Edison server and I have 3 database with both SQL Logins and windows logins.Is there any way to find out, how frequently a login (could be windows or SQL logins) is accessing the database per week.(For ex: lets say there is a database name "sql2k8" and there is a sql login "login_sql" and a windows login "login_windows" that are part of that DB (sql2k8). I would like to find out how many times the logins "login_sql" and "login_windows" are accessing the database per weekHope I'm clear in explining.Thanks for all your helpRegards

Permissions - Domain Question

Posted: 15 Jul 2013 02:48 AM PDT

Bit of a strange one. We have a user who has a db_owner role but still wasnt able to perform an insert. We found that a group had denydatawriter and that if we changed this, evrythign was fine.Here is the strange thing, the user is not in this specific group. He is in a group of the same name but coming from a different domain so is SQL only looking at the latter part?

CMS to store data

Posted: 15 Jul 2013 02:58 AM PDT

Can we store data of regitered servers to CMS locally to query further.Like to store Failed jobs,database status etc.If yes let me know how.Thanks

Performance issue on DB

Posted: 15 Jul 2013 12:52 AM PDT

Hi,We have a sql 2005 prod server,where one of the DB became very slow past 5 days.i have observer that blocking happening @ regular intervials from past 3 days.and also i have checked fragmentation and some the non cluster indexs and heap ,avg_fragmentation_in_percent is having more than 40% .Can you please help us hw to troubleshoot this issue.Many THanks.

Replication DB - Dead lock issue

Posted: 29 Jun 2013 03:32 PM PDT

Hi,We have One Database which is a Replication Db, sometimes during beginning of the month, lot of user activities going and we having Dead lock issue.Any one has idea how to prevent the dead lock in Replication DB?We have to manually kill the user session one by one but if someone having same kind of issue and have any idea?Thanks,

Alerts for Database Mirroring

Posted: 15 Jul 2013 01:30 AM PDT

HiI'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance (i.e. mirroring becomes suspended/inactive)So far, the methods I've found require the Service Broker to be enabled on the Mirrored DB, unfortunately, this cannot be enabled in this scenario.Is there any other simple way that I can simply set MSSQL to notify me (via email) when Mirroring isn't active?Thanks.

ERROR on SELECT * FROM OPENDATASOURCE using ('Microsoft.ACE.OLEDB.12.0)

Posted: 15 Jul 2013 01:21 AM PDT

Hello, can some one point me to proper direction here or it just stupid question (not sure what to think)I have an automated file processing setup on SQL2010 server.what it does is read a number of Excel 2010 (xlsx) files, convert(extract) specific worksheets form those files into CSV delimited files for uploading to the website.my issue is that I have 6 Excel files, and at least 2 of them error out when I do the select on them.as an example the first select works just fine but the second errors with "Msg 7314, Level 16, State 1, Line 2The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "'Centers$'". The table either does not exist or the current user does not have permissions on that table." all files are in the same folder and created and updated by the same user.folder is a public share for all internal users use thus all users have all permissions to the files.the path is a local path for the server as in I could easily provide path like 'D:\USERSHARE\WebsiteLiveFiles\BAndSConfig.xlsx' and it still works when it works. (in fact all paths are local in actual job script.)yes I know it is not a good idea to have SQL server and file server on one machine but we do not have another chassis at this moment. I can not provide sample of the files as the data is proprietary and confidential.and since I am not sure what cause the issue I can not replicate it either.I have tried copying the work sheet from files that work into one that errors out.the copied WS works just fine selecting from the same file. so it is not file permission.#1 (WORKS)SELECT * FROMOPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="\\MYSERVER\USERSHARE\WebsiteLiveFiles\BAndSConfig.xlsx";Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...['SPC Data$']#2 (ERRORS OUT)SELECT * FROMOPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="\\MYSERVER\USERSHARE\WebsiteLiveFiles\DPC.xlsx";Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...['Centers$']

SQLCMD seems to use a different code page

Posted: 15 Jul 2013 12:50 AM PDT

Odd one this.I use SQLCMD mode of SSMS extensively when managing major projects. This was I can get developers to code each procedure into a separate file, create rollback scripts, manage it using source control, and most of all, use the :r and :setvar commands to build install scripts that can run everything.This has worked perfectly for a long time until one of the developers noticed a peculiarity with a stored procedure. The procedure returns as one of it's fields a piece of text with a number prefixed with the £ symbol (e.g. "Remit by return £19.55").For exampleCREATE PROCEDURE [stp_testsymbol]ASSELECT 'This is the £ symbol';GOIf I execute this and use sp_helptext to query it, there is no problem.If I switch to SQLCMD mode and execute the script, there is no problem.But, if I then save the script and execute it from another SQLCMD script using :r "C:\testscript.sql" what I end up with is this:SELECT 'This is the � symbol'; Does anyone know of any way to stop this?

how i can identify local windows service that coresponds to sql spid?

Posted: 11 Jul 2013 01:29 AM PDT

Good morning Everyone,Our environment: sql server 2008 r2 sp2 on windows 2008 r2 enterprise sp1, 2 node active/passive cluster; 200 (500mb databases)Approximately every 10-15 minutes i see multiple (per database) connections are being established…those would run for a minute and disconnectIt's not really causing any performance issues….but, 600-800 connections to server that comes and goes are driving me crazy.I cannot identify WHERE are they coming from….i do see that some local process initiating them…cannot figure out which one.All I see is sql User: NT AUTHORITY\SYSTEMhost name : local physical node nameProgram: .Net SqlClient Data ProviderNet Library: TCPWin Domain : NT AUTHORITYWin User: SYSTEMI see that these are just sql server related information collections and we do have 3rd party monitoring tools, which I currently disabled.Can these be just a sql server native status info collections?????And these are the query that it runs:Connection1:SELECT [dbfiles].[physical_name] AS DBFilesPhysicalName,[mediafamily].[physical_device_name] AS BackupFilesPhysicalName FROM msdb.dbo.backupmediafamily [mediafamily] JOIN (SELECT [backupsets].media_set_id, [backupsets].database_name FROM msdb.dbo.backupset [backupsets] JOIN (SELECT TOP 1 [backupsets].media_set_id, [backupsets].database_name, [backupsets].backup_finish_date FROM msdb.dbo.backupset [backupsets] JOIN sys.databases [databases] ON [backupsets].[database_name] = [databases].[name] and [databases].[is_in_standby] = 0 AND [databases].[source_database_id] IS NULL AND ([databases].name = 'master' OR DATEDIFF ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0 ) WHERE [databases].[database_id] = DB_ID() AND [backupsets].type = 'D' ORDER BY [backupsets].backup_finish_date DESC) AS [latest_backupset] ON [backupsets].[database_name] = [latest_backupset].[database_name] AND [backupsets].[backup_finish_date] >= [latest_backupset].[backup_finish_date]) AS latest_backups ON [latest_baConnection2:select * from sys.master_files where database_id = '20' and type in (0,1)Connection3:(@DatabaseName nvarchar(10))IF (Exists(SELECT * FROM(SELECT name as 'DatabaseName' FROM sys.databases WHERE name NOT IN ('master','model','tempdb','msdb' ) and source_database_id IS NULL) AS UserDatabase WHERE UserDatabase.DatabaseName = @DatabaseName))BEGIN SELECT distinct obj.name FROM sys.objects AS obj JOIN sys.stats stat ON stat.object_id = obj.object_id CROSS apply sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE obj.type = 'U' AND modification_counter > 1000 END

Timeout exception

Posted: 14 Jul 2013 11:15 PM PDT

Hi,I am facing problem to connect with SQL Server 2008R2 using java application/tool It through timeout exception.But its working fine with ssms/.net application.Can anyone help.Thanks in advance.

Audit connections via extended events

Posted: 14 Jul 2013 09:53 PM PDT

Hi,I am looking for a little bit of advice on how to setup an extended event in SQL 2008 R2 which will audit each and every connection to the SQL Server.I have managed to do so in SQL 2012, but so far the same code will not work in SQL 2008 R2 as the event sqlserver.login does not exist.This is the event I had defined:[code="sql"]CREATE EVENT SESSION [RT_Test3] ON SERVER ADD EVENT sqlserver.login(SET collect_database_name=(1),collect_options_text=(0) ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.username) WHERE ([package0].[equal_boolean]([is_cached],(0)))) ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(204800))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)GO[/code]Basically I want to capture the following for each and every connection made to the server:1. Time2. Hostname3. NT User (if applicable)4. SQL Login (If Applicable)I may add further fields in the future as at the moment this is merely testing.I have tested using SQL Audit and a server side trace but both generate very large files on disk due to the volume of connections being made, I want to use extended events to utilise the buffer storage as I will summaries the data stored in the buffer and store the results to seperate database. I understand that the buffers are written to in an Asynchronous mannner so can be slightly behind current activity and also that they get overwritten as the buffer fills to its set capacity.Any help would be appreciated.

Is this a crazy way to migrate from one OS version to another?

Posted: 11 Jul 2013 12:06 AM PDT

I need to migrate all the servers I support from Windows Server 2008, to 2008 R2 by the end of the year (plenty of time, really.) All the servers are virtuals, and all of them are running SQL 2008 R2. Seeing as they're configured with an OS drive, and multiple data / SQL Server drives, I had a thought on a possible way to migrate quickly.Of course, this method does have the downside of being one way, with no quick way to go back if it doesn't work...My thought is, set up a new VM with just an OS drive and the OS. Once this is ready, on the current server, detach all the DBs, and stop the SQL service (the reason for the detach will become apparent.) Have the VMWare admins then remove the data drives from the "old" server, and attach them to the new. Once the drives are attached, delete EVERYTHING except where the DBs / TLogs live, install SQL 2008 R2 as normal and update as needed, then proceed to attach all the DBs I detached.My thinking (if this idea has merit) is this gives me a few advantages:1. I don't need to worry about the VMWare / Storage admins complaining about space, I'm just switching already used space from one VM to another...2. I think this might go a bit quicker than backing up each DB on the old, copying the backup to the new, then restoring.Of course possible problems:1. If this doesn't work well, or a DB doesn't come up, my only fall back is a backup of the DB.2. If it takes longer than expected, I may have users hitting me with "why isn't it done yet, what's taking so long..."I'm mostly interested in whether people think this would be a way to do this. If you ignore the VM part, it'd be no different than buying a new server, installing the OS, then pulling out the drives from your old server with the DBs on them and installing them in the new...Of course, a better solution would be if I could get the VMware admin to "clone" the data / log drives and attach the clones to the new VM. But that potentially brings in a fight with the storage guys...Thanks,Jason
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog