Tuesday, October 8, 2013

[SQL Server 2008 issues] Transferring sql logins and whilst retaining User Mappings

[SQL Server 2008 issues] Transferring sql logins and whilst retaining User Mappings

Transferring sql logins and whilst retaining User Mappings

Posted: 07 Oct 2013 01:16 AM PDT

HiI need to re-create the logins on my old server onto my new server. I wish to use a method that transfers the users password and BUT ALSO keeps their current database mappings and permissions. I keep getting forwarded to the following link http://support.microsoft.com/kb/918992 which creates the user ok (still got to test if it keeps the current password) but it does not map the users database permissions and I am having to manually go in and add this, which is a pain. Is there a way to script this task?

error when add and update in sp

Posted: 07 Oct 2013 07:26 AM PDT

CREATE PROCURE w ASALTER TABLE t ADD x char(1)UPDATE t set x =1Even when it lets me create that stored procedure (if I create it when x exists), when it runs, there is an error on the UPDATE statement because column x doesn't exist.What's the conventional way to deal with this, it must come up all the time? I can work around it by putting the UPDATE inside EXEC, is there another/better way?Thanks

Split character sting - Selected Part

Posted: 07 Oct 2013 08:32 AM PDT

I am looking for help on splitting a string and select nth occurrence only to display like@String='123,0.934,98,928.34,987.45'The above string contains 5 values delimited by Comma.I need to display only 3rd split - 98I need to display only 5th split value - 987.45I could find functions to display all values together, but I want only nth occurrence only.I appreciate your kind help.

Replication Log Reader Agent error

Posted: 07 Oct 2013 08:25 AM PDT

I have a replication that was working fine we and then a reboot occurred and now I'm getting these error messages from replication monitor. The funny thing is I don't know why it's spitting out the user 'NA\MajAdmins' because we are using account 'NA\MajService' for the replication jobs and as the account to set up replication on publisher and subscriber servers. Publisher and Subscriber are on two different servers. Help is much appreciated! Thanks.Error messages:The process could not execute 'sp_replcmds' on 'Server1\MAJ'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)Get help: http://help/MSSQL_REPL20011Could not obtain information about Windows NT group/user 'NA\MajAdmins', error code 0xffff0002. (Source: MSSQLServer, Error number: 15404)Get help: http://help/15404The process could not execute 'sp_replcmds' on 'Server1\MAJ'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)Get help: http://help/MSSQL_REPL22037

sparse column - how it works

Posted: 23 Jan 2011 10:21 PM PST

I have read few articles on sparse columns. I found that when you store null values in sparse columns it wont need any storage space and when you store non null in any sparse column , it would need additional 4 byteCan anyone tell me WHY?May be something related to bitmap but want to know more . if anyone has the answer please let me know

Calling Netbackup from SQL Job

Posted: 05 Oct 2012 02:29 AM PDT

Hi there,I am trying to initiate a Netbackup task from a SQL Job using the following CmdExec code:"C:\Program Files\VERITAS\NetBackup\bin\dbbackex.exe" -f "C:\Program Files\Veritas\NetBackup\DbExt\MsSql\<Filename.bch>" -p <profilename> -u <username> -pw <password> -s <server> -npIf I run it from a Command Prompt window it works fine, however if I copy the exact same code into a SQL Job and use the job type 'Operating System (CmdExec)' it fails to run. All I get in the Job History is:"Executed as user: Domain\Username. The step did not generate any output. Process Exit Code 2. The step failed."I have managed to get this to work on another almost identical server using the same accounts and everything but for some reason it won't run on the one I'm currently using.Any suggestions? Anything that needs to be enabled on the server perhaps?Thanks,Matt

select joining tables without any where clause

Posted: 07 Oct 2013 06:57 AM PDT

Could someone desipher this query that was handed to me? Primarily, how are they joining OeOrders with DOeCategories? I can't understand how you can join tables this way.Shouldn't there be a where clause somewhere?[code="sql"]SELECT IP.SourceID ,IP.FacilityID ,IP.AccountNumber ,IP.UnitNumber ,IP.Name AS PatientName ,IP.VisitID ,IP.PatientID ,IP.AdmitDateTime ,IP.DischargeDateTime ,IP.BirthDateTime ,IP.ErServiceDateTime ,IP.PtStatus ,OE.OrderID ,OE.OrderNumber ,DOP.[Name] AS [OrderName] ,OE.OrderDateTime ,OMC.Interface AS CategoryInterface ,OE2.OrdSourceID /* Whether CPOE was used */ ,CASE WHEN DARP.OeDefaultSourceID IS NOT NULL THEN 'Y' ELSE NULL END AS CPOE INTO dbo.#TotalOrders FROM ( dbo.OeOrders AS OE INNER JOIN dbo.DOeCategories AS OMC ON OE.SourceID = OMC.SourceID AND OE.Category = OMC.CategoryID AND OE.OrderDateTime BETWEEN @FromDate AND @ThruDate AND ( OMC.Interface IN ( SELECT ParameterValue FROM dbo.#MedInterface ) OR OMC.Interface IN ( SELECT ParameterValue FROM dbo.#LabInterface ) OR OMC.Interface IN ( SELECT ParameterValue FROM dbo.#RadInterface ) ) AND OE.[Status] NOT IN ( 'CANC', 'UNVER' ) INNER JOIN dbo.DOeProcs AS DOP ON OE.SourceID = DOP.SourceID AND OE.Category = DOP.CategoryID AND OE.OrderedProcedure = DOP.ProcedureID INNER JOIN dbo.OeOrders2 AS OE2 ON OE.SourceID = OE2.SourceID AND OE.OrderID = OE2.OrderID LEFT OUTER JOIN ( dbo.DrArraAppReportParameters AS DARP INNER JOIN dbo.mt_fn_2014_MG_NPRApplicationSourceIDs(@EnvironmentID, 'DR') AS DRSI ON DARP.SourceID = DRSI.DatabaseSourceID ) ON OE2.OrdSourceID = DARP.OeDefaultSourceID ) INNER JOIN dbo.mt_fn_2014_CoreMenuInitialPopulation(@BeginDate, @EndDate, NULL-- Indicates do not use Admit or Discharge DateTime , CASE WHEN UPPER(@EDMethod) = 'A' THEN 'IN_ER' -- All ED Visits Method WHEN UPPER(@EDMethod) = 'O' THEN 'IN_INO' -- Observation Services Method ELSE 'IN_ER' END, @EnvironmentID) AS IP ON IP.SourceID = OE.SourceID AND IP.VisitID = OE.VisitID[/code]

SQL Server Agent Performance Issue

Posted: 07 Oct 2013 02:38 AM PDT

Hello EveryoneI am facing one issue with SQL Server Agent.I schedule on job,There is one T-SQL in it. If i execute that T-SQL in SSMS it takes only 12 minutes to run but same T-SQL Script I schedule from SQL Server Agent and it takes 6 Hours to Finish. I refresh SQL Server Agent once and re-ran Job but still it takes same time.If anybody knows this issue please help me to resolve.

Rounding issue

Posted: 07 Oct 2013 05:11 AM PDT

Hello,I need to convert the following calculation. The 2 and 13 are sums from other columns that I need to divide. Not sure why this isn't working, but here is what I have to do...This should be the end result... 2/13 = 15.385%However, I keep getting 2/13 = 0.1538461Any help would be greatly appreciated!

High Latch Waits/Sec

Posted: 07 Oct 2013 03:24 AM PDT

Hi,One of my Servers running on SQL Server 2008 R2 , while monitoring perfmon I can see high values for latch waits/sec. The value keeps fluctuating consistently from 20 to 100. I read somewhere that a value more than 10 for this is considered as a problem.How do I find where these latches come from ? I want to find which queries running in the database causes these high latches and I am unable to find a good documentation to troubleshoot this. Any help regarding this is highly appreciated.Thanks in Advance !

Sharepoint Slow, Queries Recompiling everytime

Posted: 07 Oct 2013 12:39 AM PDT

We have a sharepoint 2010 server running in native mode. The backend server is SQL 2008 R2 RTM.We recently changed the credentials a report was using to connect to SSAS. After that change we have been getting timeouts regularly for the report. We changed the credentials back, but the timeouts persist.When I run SP_WHOISACTIVE I see queries that appear to be recompiling, blocking the select queries.[IMG]http://i44.tinypic.com/f07v9t.jpg[/IMG]The stored Procs do not have the 'with recompile' option in them. The Create statements run for more than 2 minutes, which exceeds the Sharepoint timeout.I found this reference - [url=http://support.microsoft.com/kb/2691331]http://support.microsoft.com/kb/2691331[/url] And based on it I redeployed the entire solution, however I'm still seeing these problems.

Using Execute as and encryption in a stored procedure

Posted: 06 Oct 2013 08:47 PM PDT

HI Experts,I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.The below query is getting errors, kindly some one help.Create procedure testprocwith execute as 'user' and with encryptionas truncate table some tableThanks Mj

How to register SP automatically on the database.

Posted: 06 Oct 2013 10:27 PM PDT

Hi, I am using Sql server 2008 R2,In my Organisation,we daily restore the Database.We need to give the daily reports from this updated newly restored database.For this cuurently,after restoring the database,we register(create) all the Stored Procedure,on this server and execute the SP.My requirement is,once the database is restored, all the SP should be automatically created on the restored database.Or any other alternative to do this.Please help me how to do this, this is urgent for me.Thanks in Advance!!

There is insufficient system memory to run this query

Posted: 06 Oct 2013 07:47 PM PDT

Hi,We have sql 2005 running on win 2003 ,user getting error as '" There is insufficient system memory to run this query".Ram is 4GB and sql is assigned to max 2.4 GB and Min 1 GBAT presnt CPU and Memory is looking normal.I have connected to server and there is no blockings and tried to connect the error log but got error as below,( XP-readerrorlog)Msg 22004, Level 16, State 1, Line 0Failed to open loopback connection. Please see event log for more information.Msg 22004, Level 16, State 1, Line 0error log location not foundThen i have cheked eventvwer an found below error.1)The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:8, OS: 8 [Microsoft][SQL Native Client]SQL Network Interfaces: Not enough storage is available to process this command..2)There is insufficient system memory to run this query.any help is much appricated...THanks in advance..

Audit record in Audit Table

Posted: 06 Oct 2013 11:13 PM PDT

Hello geeks,I have a problem with the SSIS audit values recording in the SSISAudittable. I try to execute a group of packages with Audit open and Audit close working stored procedures. when i execute the packages individually it shows all the values recorded in the Audit table, but when i try to execute the packages as a group , i see the values recorded for each of the package but the values for the main package is not recorded in the audit table, it shows the starting execution of the package values such as startdatetime, version, ...but doesnot show the values like enddatetime processduration(which are the part of closed stored procedure).Does any help me out with this issue.Thanks San

Backup on shared drive

Posted: 06 Oct 2013 07:41 PM PDT

can we create split backup files on shared drive while generating the backup.is so please help me in the script

MaxDOP setting?

Posted: 06 Oct 2013 09:57 PM PDT

As of now SQL Server maxdop value 0, it is using all available CPU..--Generic Servers or general settings is MAXDOP value is 8SELECT COUNT(*) AS proc# FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1--Result is 80, does server have logical CPU is 80? so server Hyperthead is enabled- how much value set int maxdop of result 80?select (select top 1 count(*) schedulers_per_node from sys.dm_os_schedulers where scheduler_id < 255 group by parent_node_id) schedulers_per_node , (select value_in_use from sys.configurations where configuration_id = 1539) current_max_dop --schedulers_per_node = 20 --current_max_dop = 10--Can we set the value 10 in maxTop?Here i have doubt First query result 80 and second query 20, so which is the correct one?How to check server have NUMA enabled or not?Is there relation between maxtop and NUMA?How it will be matching maxtop and NUMA?Thanksananda
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog