Monday, April 22, 2013

[SQL Server 2008 issues] option (maxdop 1)

[SQL Server 2008 issues] option (maxdop 1)

option (maxdop 1)

Posted: 09 Aug 2010 08:27 PM PDT

I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and Exe's. Anyone know what this is? Is it stats / indexes being rebuilt?insert [dbo].[CLIENT] select * from [dbo].[CLIENT] option (maxdop 1)

mixed data columns

Posted: 21 Apr 2013 07:35 PM PDT

I currently have a column that have both numeric and characters:Example: 3 PF 11 PF PF 3 3I am trying to pull the data like this into ssis and send it to a sql table. What I get are NULLS when I do DT_NUMERIC and DT_STR will not pull it in either. Did anyone had this problem and came up with an answer?Kind regardsFred

Pivot

Posted: 21 Apr 2013 07:20 PM PDT

Hi ,[code="other"]Create table temp1(Num varchar(50), id uniqueidentifier) create table temp2 ( num1 varchar(50), value varchar(max) )----INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '22', '50C6CC7C-140E-4697-9287-748AB307C497')INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '22', 'DB63AF5A-E8E6-42CC-AEC1-3FFA951FC8DD')INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '22', 'A8BA7E4D-8EAE-44A5-9172-2C0D0E618580')INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '22', '50C6CC7C-140E-4697-9287-748AB307C497') INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '21', 'CDC4C3F3-88B5-47BB-8B24-A878A766C76E')INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '21', '0B981992-55A0-4FE0-85F2-0A7E43A1A468') INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '21', '0B981992-55A0-4FE0-85F2-0A7E43A1A469') INSERT INTO [temp2] ([Num1] ,[id]) VALUES ( '21',null) INSERT INTO [temp2] ([Num1] ,[id]) VALUES ( '22',null) Select * from temp1 join temp2 on temp1.num=temp2.num1[/code]I need o/p as temp2 tableNum1 Value22 'X:50C6CC7C-140E-4697-9287-748AB307C497;X:DB63AF5A-E8E6-42CC-AEC13FFA951FC8DD;X:A8BA7E4D-8EAE-44A5-9172-2C0D0E618580;'21 'X:CDC4C3F3-88B5-47BB-8B24-A878A766C76E;X:0B981992-55A0-4FE0-85F2-0A7E43A1A468;X:0B981992-55A0-4FE0-85F2-0A7E43A1A469;Ie,I need value field of my temp2 table should be populated with Id's of temp1 concatinated with 'X:' grouped under Num1 COlumnPlease help me in this regard

DATA consitency errors

Posted: 21 Apr 2013 05:49 PM PDT

I dont have the backup when we ran the dbcc check db got the data consistency errors , how can we bring back the database with out data loss ?

i need .bak files

Posted: 21 Apr 2013 02:30 AM PDT

Hi, i need .bak files for testing purpose. where can i get that files........Regards,shiva

Schemas and Roles

Posted: 21 Apr 2013 05:23 AM PDT

Hi,I am confusing about the usage of roles and schemas. I have done some research on Internet, and I found that also many others are confused, with some admins claiming, that they avoid using schemas to avoid complications.I believe schemas can be useful and simplify things. The problem I see is the overlapping feature / functionality between roles and schemas. For example, when I create a role rlTest, then in the SSMS in the properties of this role Properties\Securables I can specify the schema schTest and set the permissions for the role on this schema.On the other hand, in the properties for the schema schTest I can set up permissions for the role rlTest: Properties\Permissions, field 'Users and Roles'.I think these two approaches are not equivalent and can lead to conflicts.So what would be the proper using of schemas/roles? I have the following example:Table: dbo.TestUsers: usr1 – read permissions usr2 – read permissions, insert permissions usr3 – full accessRoles: rl1, rl2 rl3Schemas: dbo, sch1, sch2, sch3Thank you for your insight.

How to fix Filestream enabled database

Posted: 21 Apr 2013 05:26 AM PDT

I have SQL Server 2008 R2 FileStream enabled Database, when I try to access any value that stored in the filestream I get the following error :[quote]Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)[/quote]I read about that exception and Microsoft has KB for it (KB972936), but it also didn't worked after I installed its hotfix.If I SELECT the count of the records in that table using :[quote]SELECT COUNT(1) FROM [Table_Name][/quote]I get a correct result.Here is some details for the database files and filegroups :The database have 2 files the "Row Data" file and the "Log" file where it should also contains the "Filestream Data" item.The database has the following filegroups : - Rows : PRIMARY with 1 File - Filestream : [MyFileName] with 0 Files ![url=http://amersaw.info/SQLDBDetails/Details.jpg]Here is a snapshots for the DB properties page (LINK)[/url][url=http://amersaw.info/SQLDBDetails/ErrorLog.txt]And here is the full SQL ERRORLOG file (LINK)[/url].

Fixing Divide by Zero Error

Posted: 15 Apr 2013 03:52 PM PDT

Hello,I have in select statement select t1.A/t1.B but get divide by zero errorwhen I used with isnull isnull(t1.A/t1.B,'-')I get error Error converting data type varchar to float.how to fix?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog