Friday, May 10, 2013

[SQL Server 2008 issues] hight wait type of PREEMPTIVE_OS_AUTHENTICATIONOPS & PREEMPTIVE_OS_LOOKUPACCOUNTSID

[SQL Server 2008 issues] hight wait type of PREEMPTIVE_OS_AUTHENTICATIONOPS & PREEMPTIVE_OS_LOOKUPACCOUNTSID

hight wait type of PREEMPTIVE_OS_AUTHENTICATIONOPS & PREEMPTIVE_OS_LOOKUPACCOUNTSID

Posted: 10 Jan 2011 03:09 AM PST

Can anyone explain what these wait types are? PREEMPTIVE_OS_AUTHENTICATIONOPSPREEMPTIVE_OS_LOOKUPACCOUNTSID

Restoring failed

Posted: 09 May 2013 05:01 PM PDT

The restoring of database failed with below messageMsg 3201, Level 16, State 2, Line 1Cannot open backup device 'C:\May10.bak'. Operating system error 2(The system cannot find the file specified.).Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.

What is a semicolon used for in SQL Server?

Posted: 09 May 2013 11:38 AM PDT

Hello All,I have a co worker that uses the semicolon at the end of his SQL statements likeSelect * From Table1(nolock) t1Where t1.column is not null;Besides it telling SQL Server that it is the end of the statement, is this good or normal practice? In C# it is required and is this where this has come from? I am just curious and would like to know best practices. Thanks in advance for your time and assistance.

Error: Workload in the input table specified : Trace01 could not be opened.

Posted: 09 May 2013 05:38 PM PDT

Just to get my hands on DTA. I followed the following process.I setup and ran a trace in Profiler using the tuning template and ran it for some 10-15min into a table in my database. Now when I try to run DTA on the trace table I get the following error:[b]"Error: Workload in the input table specified : Trace01 could not be opened." [/b]I do not understand why since I can open it up fine in Profiler. I give it a session name, choose Table, select the table from the list, and select the database to tune. If I have this in the wrong forum, I apologize

Deny login while connecting to Sql server

Posted: 09 May 2013 12:11 AM PDT

Hi All,I have a scenario where i am trying to deny permission to specific login while connecting to server.I came across the following query and was able to block the access when the user is trying to connect through Sql server.Please find the query belowCREATE TRIGGER Login_DenyON ALL SERVER FOR LOGONASBEGINIF ( (ORIGINAL_LOGIN()= 'abc_def') AND APP_NAME() IN ('Microsoft SQL Server Management Studio - Query') ) ROLLBACKENDThe problem i am currently facing is the same login id is used in ETL process which runs in informatica.I want to deny the permission only if the connection is made through SQL server and the same login should work when they are trying to connect from informatica.Is this possible?

i need some clarification

Posted: 09 May 2013 03:04 PM PDT

i have a table where i need to populate child table in parents table i have a column which is a surrogate key and this same column is PK in child table my question is can i use Surrogate key as PK in child table??

Need output

Posted: 08 May 2013 10:30 PM PDT

Hi. create table #variableta(assessment1 varchar(114),assessment2 varchar(114),assessment3 varchar(114),assessment4 varchar(114),asessment_pt1 varchar(114),asessment_pt2 varchar(114),asessment_pt3 varchar(114),asessment_pt4 varchar(114),code1 varchar(20),code2 varchar(20),code3 varchar(20),code4 varchar(30)) insert into #variableta (txt_bh_assessment1,txt_bh_assessment2,txt_bh_assessment3,txt_bh_assessment4)values ( 'test1','test2','test3','test4') create table #testing1(code varchar(20),descr varchar(114))create table #testing2(code varchar(20),descr varchar(114))create table #testing2(code varchar(20),descr varchar(114))I need a output that, #variableta table-code1,code2,code3,code4 need to updated with #testing1 and #testing2 and #testing3 tables.Need to find codes for assessment1,assessment2,assessment3,assessment4.these fields values will be in descr field from #testing1 and #testing2 and #testing3 tables.Please help me out how to reach this.Regards,tony

NOLOCK clarification please

Posted: 09 May 2013 02:09 PM PDT

I'm looking for some help clarifying what effect NOLOCK will have on the SPROCS we run on our SQL 2008 server please. 1. We are not committing data via transactions or rolling it back. Just simple inserts/updates/deletes.a. If a user inserts, deletes or updates data, and then SELECTS from the table using NOLOCK, will that change be immediately available?b. If not, how does one tell that the change has been applied, or force it to be applied? c. If yes, will the change be consistently available to all subsequent SELECTS using NOLOCK?2. Is this true: If people are selecting from a table with NOLOCK, others could (sequentially) insert, update or delete at the same time - the reads may pick up some or all of the changes. It will be faster all round as no one has to wait (assuming only one person is changing the data and the rest are reading it).3. Do any of these answers change for SQL2008R2 or SQL2012?Many thanksNick

why does the table alias

Posted: 09 May 2013 08:28 AM PDT

make this crash?select cbclaim from [dev].[stage].[dbo].[claimsview] with(noexpand) tit works fine without the t?thanks a lot

SSIS

Posted: 09 May 2013 06:45 AM PDT

When I look into the SQL Server Job it is run as "SQL Server Integration Service Account" and it is NOT run as Proxy Account but when I look at the job history it says Executed as 'UserOne' (for example). I am trying to find out where this is tied because I need to access a fileShare where Agent account does not have access where as UserOne would- without actually using a proxy account. Thanks.

Query Help

Posted: 09 May 2013 08:06 AM PDT

I have following tableID ActiveFalg Date1 0 1/1/20131 0 2/1/20131 1 3/1/20131 0 4/1/20132 0 1/1/20132 1 2/1/20132 1 4/1/2013I am looking for result output on which date active flag changes from for particular idlike this1 1 3/1/20121 0 4/1/20132 1 4/1/2013

Error: Column Names Input Into Rows

Posted: 09 May 2013 08:30 AM PDT

Hi All,I am having an odd situation that is occuring with my SQL server. I have created some reports in excel that open a connection to SQL server through ADO and saves data into a SQL table. I originally had this working in MS Access and it did not have this specific problem (Others though attributed to users crashing the Excel). Apparently somehow the column names are being saved into the rows of the table (this may be occuring during crashing of Excel too). Any ideas why the column headers would get repeated into the rows of data?

Saving Circular and Rectangular path in geography data type

Posted: 02 May 2013 06:37 PM PDT

Hi all,I have an interface (google map) where i allow my users to define an area. Google map provides rectangle, line, circle and polygon to define a path or area. I have provided all the four options for my users to define the area. When my users use polygon or line, i get the relevant points and able to save the data as geography data type as well.SET @g1 = geography::STGeomFromText('LINESTRING(Points that i get from google)', 4326);SET @g1 = geography::STGeomFromText('POLYGON(points that i get from google)', 4326);For Circular path define, all i get is one point and radius. Similarly for rectangle. i get the adjacent points alone. Is there a way to convert these points to Geography data type.Thanks & RegardsKrishna

Get Previuos unpaid Customer id

Posted: 09 May 2013 12:38 AM PDT

DECLARE @TABLE TABLE (CUSTOMERID INT,INSTALMENTNO INT,AMOUNT INT,PAIDAMT INT)INSERT INTO @TABLE VALUES (1000000001,1,100,100),(1000000001,2,100,0),(1000000001,3,200,200),(1000000001,4,300,300), (1000000002,1,200,0),(1000000002,2,250,0),(1000000002,3,300,0),(1000000002,4,400,0), (1000000003,1,100,0),(1000000003,2,250,250),(1000000003,3,300,0),(1000000003,4,400,400), (1000000004,1,200,200),(1000000004,2,250,250),(1000000004,3,300,300),(1000000004,4,400,400) SELECT * FROM @TABLE Hi All,I have a table which have data like mention in script .i want to get only those customer Id whose PAIDAMT = 0 but previous installmentno PAIDAMT is not 0So If U Run the above script then i want to get only customer id : 1000000001,1000000003because on 1000000001 id INSTALMENTNO = 1 and PAIDAMT = 100 But INSTALMENTNO = 2 and PAIDAMT = 0 thanks in advance.....

SSMS question - Saving Auto Recovery Information...

Posted: 09 May 2013 03:44 AM PDT

I know this might be a simple question but I have not been able to find my answer as of yet and thought I would throw it out there in hopes somebody knows the answer. In my SSMS (management studio) I get the 'Saving Auto Recovery Information' message when it is saving off the Auto Recovery Information which I do not have an issue with my issue is the frequency of this seems to set to every couple minutes and I want to set that to every 30min if possible.Any help appreciated.Microsoft SQL Server Management Studio 10.0.5500.0Microsoft Analysis Services Client Tools 10.0.5500.0Microsoft Data Access Components (MDAC) 6.1.7601.17514Microsoft MSXML 3.04.05.06.0Microsoft Internet Explorer 8.0.7601.17514Microsoft.NET Framework 2.0.50727.5448Operating System 6.1.7601

Aliases - weird behaviour

Posted: 09 May 2013 05:13 AM PDT

Hi I have 2 versions of SQL on my win 7 machineDefault instance(ver 2005) = port 20000 Alias = "2005"named instance(ver 2008 R2)= "ins2008" for port 47777 Alias = "2008"SQL Browser service is runningEverything works ok when I use aliases in enterprise manager, it's a great feature.However whenever I try and use a particular in-house application to connect to the DB I get weird results. The application is running on the same machine as my SQL ServersThe Program uses the SQL Server ODBC driver to connect. It just tries to connect using a connection string (Its compiled code so cannot see exactly what it uses)1. The app cannot find the server using the Alias name i.e "2008". On a failed attempt it removes the port number from the alias seen in config manager. This stops Enterprise manager connecting with the alias. To fix I have to remove the alias and re-add it with the port number.2. To get the app to connect I put the server name as machinename\inst2008,47777 After it connects I can see a new Alias called machinename\inst2008,47777 has been createdCan someone explain this behaviour. My machine name has changed since installing both SQL instances so running SELECT @@SERVERNAME returns OLDMACHINENAME\inst2008.I'm baffled by this, I never expected an app to change my Alias properties when there is no code to add Aliases in it.Thank you

Convert binary string to binary

Posted: 09 May 2013 02:15 AM PDT

I have a string: '0x0000000000018662' that I want to "use" as a binary: 0x0000000000018662. CONVERT/CAST does not work because, I think, it's converting byte by byte the binary/ascii representation of each character. This seems pretty simple, but I just can't figure out how to get this to work!? Any help would be greatly appreciated!!

Using SQL Management Query in Visual Studio

Posted: 02 May 2013 02:42 AM PDT

I am trying to use a query that works in SQL Management in Visual Studio but the code it not translating. Can any one help me fix this. Here is the code in SQL Management.DECLARE @StartDate DATETIME = '04/01/2013'DECLARE @EndDate DATETIME = '04/30/2013 23:59:59'DECLARE @UTCTimeDiff INT = -1 * (SELECT DATEDIFF(hh, GETDATE(), GETUTCDATE()))SELECT CASE WHEN MAX(TransactionDate) IS NOT NULL THEN CONVERT(VARCHAR, DATEADD(hh, @UTCTimeDiff, MAX(TransactionDate))) ELSE 'No Data Present' END AS 'Data Freshness' FROM TotalInvoicing..TI_TransactionReporting (nolock)I believe it has something to do with the Declare Statements but I am unsure how to remedy this error. Any help is greatly appreciated. Thank you!-Molly

SQL 2008 - SSIS - FTP task

Posted: 09 May 2013 01:58 AM PDT

I have to download file/s from Ftp server. Can we use 1 SSIS package with FTP task only, to download file1 from Job1 and file2 from job2 by means of variable or any other means?I am using package configuration with values stord in table

creating XML from SQL query problem

Posted: 08 May 2013 11:35 PM PDT

Hi All,I have a problem trying add a element with an illegal character. I have the following SQL[code="sql"]SELECT 'en-GB' AS '@xml:lang','20120217164611.1712._3FO0ZXYQ3@GBRAPPCHL1' AS '@payloadID','2012-02-17T16:46:11' AS '@timestamp','1.2.014' AS '@version', ( SELECT ( SELECT /* FROM */ (SELECT 'TecSolExchangeID' AS 'Credential/@domain', 33333 AS 'Credential/Identity' FOR XML PATH(''), TYPE), (SELECT 'name' AS 'Credential/@domain', 'Test Limited' AS 'Credential/Identity' FOR XML PATH(''), TYPE) FOR XML PATH('From'), TYPE ), ( SELECT /* TO */ (SELECT 'TecSolExchangeID' AS 'Credential/@domain', 66666 AS 'Credential/Identity' FOR XML PATH(''), TYPE), (SELECT 'name' AS 'Credential/@domain', 'Test Company' AS 'Credential/Identity' FOR XML PATH(''), TYPE) FOR XML PATH('To'), TYPE ), ( SELECT /* SENDER */ (SELECT 'TecSolExchangeID' AS 'Credential/@domain', 13041 AS 'Credential/Identity', '' AS 'Credential/SharedSecret' FOR XML PATH(''), TYPE), ' cXML Link v1.3.0' AS 'UserAgent' FOR XML PATH('Sender'), TYPE ) FROM TEST2.PORDER WHERE POHNUM_0 = '2011302POH00000002' FOR XML PATH('Header'),TYPE ) , ( SELECT 'production' AS '@deploymentMode', ( SELECT ( SELECT /* ShipNoticeHeader */ (SELECT '' FOR XML PATH(''), TYPE) FOR XML PATH('ShipNoticeHeader'), TYPE ), ( SELECT '' AS '@domain', '' as 'CarrierIdentfier', ''AS 'ShipmentIdentifier' FOR XML PATH('ShipControl'), TYPE ), (SELECT /* Ship Notice Portion */ (SELECT '1;29117/42' AS 'DocumentReference/@PayLoadID', '' AS 'DocumentReference' FOR XML PATH('OrderReference'), TYPE), (SELECT '4' AS '@Quantity', '1' AS '@LineNo', 'Box 4' AS 'UnitOfMeasure' FOR XML PATH('ShipNoticeItem'), TYPE) FOR XML PATH('ShipNoticePortion'), TYPE) FROM TEST2.PORDER WHERE POHNUM_0 = '2011302POH00000002' FOR XML PATH('ShipNoticeRequest'), TYPE ) FROM TEST2.PORDER WHERE POHNUM_0 = '2011302POH00000002' FOR XML PATH('Request'), TYPE)FROM TEST2.PORDERWHERE POHNUM_0 = '2011302POH00000002' FOR XML PATH('?XML')[/code]As you can see I have a question mark in the root field. This fails with the error.'?XML' contains an invalid XML identifier as required by FOR XML; '?'(0x003F) is the first character at fault'.My question is I need to be able to create the element with illegal characters in. For example I also need another element surrounding the message which will be "<!DOCTYPE>". same scenario except this field has a exclamation mark.Is this possible first off, and if so, can anyone help me finish it off.Thanks in advance

Attaching database

Posted: 08 May 2013 09:21 PM PDT

Hi All, I downloaded the adventureworks2012 database and followed the normal procedure to attach database. I placed the mdf and ldf files in D: drive. when i am trying to attach .mdf file it is throwing error message: OS error 5 (access denied).What is the reason for this.I changed the path to C: and attempted to attach . It went fine and database is in read only mode. I tried to put in normal mode and i am getting the same error.

Query to Search the Data

Posted: 08 May 2013 08:03 PM PDT

Hi, My requirement is,I want to search records,in my application,Example,LnnoNameCodeSNoThis are the Text box available on the screen where user will enter values in any one text box and based on this value records should be display.So I written query for this as,I enters some code in Code textbox,so this should retreive only the records for code like R0000299,but it displays all the records, Select A.* From CRM_Customer_Master A Join CRM_Loan_Master B On A.Lnno = B.Lnno Where A.Lnno Like '% %' Or SNo Like '% %' Or Code Like '%R0000299%' Or Name Like '% %')Kindly provide solution for this,as I need this urgently.Thanks in Advance!

CTE Recursive

Posted: 08 May 2013 07:56 PM PDT

HiI have a query below and I will explain what I wish to archive. I have two methods that I need to do, (1)find all the records on #PR_LINK_INV_HST where the @Client_Id = #PR_LINK_INV_HST.CLIENT_ID or #PR_LINK_INV_HST.LINKED_CLIENT_ID (2) find the clients that are linked to the linked clients of the specified Client, we call this "deep linking".All the clients will be on CLIENT_ID, and the clients they are linked to will be on LINKED_CLIENT_ID. Now e.g, if our specified client is Client A we will find client A(CLIENT_ID) is linked to client B(LINKED_CLIENT_ID), if client B(LINKED_CLIENT_ID) is also a child to client C(CLIENT_ID), we must also show this row because client C is indirectly linked to A because of client B.below is my query, whih somehow my logic is wrong, because when I select LINK_CLIENT_ID only on my finaly DISTINCT SELECT, I get 6 rows back, which is correct, but when I select all three colums I get 14 back, which is wrong, I have to get only 6 rows.Please help.--create PR_LINK_INV_HST temp table--Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))--insert into PR_LINK_INV_HST temp table--Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')insert into #PR_LINK_INV_HSTSelect LINK_CLIENT_ID,CLIENT_ID,LINK_REASONFROM #PR_LINK_INV_HSTdeclare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9';WITH cte AS( SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON, CAST(CLIENT_ID + '/' AS VARCHAR(MAX)) AS traversed FROM #PR_LINK_INV_HST WHERE @CLIENT_ID = CLIENT_ID UNION ALL SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON, CAST(traversed+t.client_id + '/' AS VARCHAR(MAX)) AS traversed FROM #PR_LINK_INV_HST t INNER JOIN cte c ON c.LINK_CLIENT_ID = t.CLIENT_ID WHERE traversed NOT LIKE '%'+t.client_id + '%') SELECT DISTINCT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON FROM ctedrop table #PR_LINK_INV_HST
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog