Thursday, October 10, 2013

[SQL Server 2008 issues] Connect from LEI Server

[SQL Server 2008 issues] Connect from LEI Server

Connect from LEI Server

Posted: 09 Oct 2013 06:32 PM PDT

Didn't know where to ask or how to google this issue, so I'll throw it out here..Trying to import data from Lotus Domino to SQL Server 2008 R2. This server is clustered server with 2 clusters. When Node B is active I can connect from LEI to SQL Server, but when Node A is active it just fails. Any idea what is wrong with it?

Case statement returning duplicates

Posted: 08 Oct 2013 09:56 PM PDT

HiI have written a query in sql.When i run it with the case statement it returns duplicate figuresAny reason why it would be doing this?

MS SQL 2008 DBA certification

Posted: 09 Oct 2013 02:30 PM PDT

What happen when the certification retired?

Need help in SQL Query

Posted: 08 Oct 2013 08:40 PM PDT

Hi, I have a group called Leaders group. Users belonging to this Leaders group should be able to see only people information belonging to Leaders group. The following people belong to Leaders group[a,,c,d,e,f]. I need to display this for a SSRS report. Can anyone help me with the query.

Difficulty in creating the proper Update Statement

Posted: 09 Oct 2013 10:18 AM PDT

I am in the process of converting some old tables into a new format and I need to do an update as an intermediate step. I thought this would be fairly straight forward, but of course. . . :(Here is the DDL and Insert statements:[code]declare @t1 table( [RIN] [int], [Type] [tinyint], [Code] [int], [Lot] [varchar](50), [ShouldBe] [int] NULL)insert @t1 values (130363, 2, 145, 0033, NULL) ,(132757, 2, 145, 0033, NULL) ,(165041, 2, 145, 0033, NULL) ,(160574, 2, 145, 0034, NULL) ,(160575, 2, 145, 0034, NULL) ,(132763, 2, 145, 0035, NULL) ,(137219, 2, 145, 0035, NULL) ,(140963, 2, 145, 0035, NULL)[/code]What I am trying to do is point the duplicate rows ShouldBe column to point to the RIN of the first row of the duplicates.I have tried the following, but I am pretty sure that I am violating an update principle of SQL. [code];with cte as( select RIN, TYPE, Code, Lot, ShouldBe, ROW_NUMBER() over (Partition by Type, Code, Lot order by RIN) RowNum from @t1)update c set c.ShouldBe = case when c.RowNum = 1 then 0 when c.RowNum = 2 then p.RIN else p.ShouldBe end from cte as c left outer join cte as p on c.RowNum = p.rownum + 1 and c.Lot = p.Lotselect *, ROW_NUMBER() over (Partition by Type, Code, Lot order by RIN) RowNum from @t1 [/code]This is the output I am looking for:[code]RIN Type Code Lot ShouldBe RowNum130363 2 145 33 0 1132757 2 145 33 130363 2165041 2 145 33 130363 3160574 2 145 34 0 1160575 2 145 34 160574 2132763 2 145 35 0 1137219 2 145 35 132763 2140963 2 145 35 132763 3[/code] Any thoughts?

Adding Zero

Posted: 09 Oct 2013 02:07 AM PDT

HI,I have the below query set up. What i am trying to do is create a line that populates as value to be zero if there is no count of unit in a given month like below. Is this possible?There are 4 units, so for each month i want 4 lines, but currently if there are no actuals in a month its giving 3 lines.SELECT count([Unit]) as Actual,unit,[1st_of_month],last_of_monthFROM [lchs_live].[dbo].[Full_Referrals_Dataset_live]where RfR1 = '18 month project'group by unit,[1st_of_month],[last_of_month]Results6 NW 2013-08-01 2013-08-314 SE 2013-08-01 2013-08-315 SW 2013-08-01 2013-08-31Required Result6 NW 2013-08-01 2013-08-314 SE 2013-08-01 2013-08-315 SW 2013-08-01 2013-08-310 NE 2013-08-01 2013-08-31

sys.sp_cdc_enable_table does not consider additions or removal of columsn to the table

Posted: 09 Oct 2013 05:51 AM PDT

I am building a CDC on my tables. However, I noticed, if I add a new column to the table being audited, the Audit table doesnot have this column. Instead, the only way I can see is to disable the CDC on the table and re-enable it, which menas the data sitting in the audit table is Lost.Is there any way this can be done seamlessly in CDC?[code="sql"]--EXEC sys.sp_cdc_enable_db--EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AuditTest', @role_name = NULL/*Operation 1 = DeleteOperation 2 = InsertOperation 3 = Old value before the operationOperation 3 = New Value after the Operation*/CREATE TABLE dbo.AuditTest(RowID INT IDENTITY(1,1),VALUE VARCHAR(50),UserName VARCHAR(50), CreationDate DATETIME DEFAULT GETDATE(),ModifiedDate DATETIME)EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', -- sysname @source_name = 'AuditTest', -- sysname @capture_instance = NULL, -- sysname @role_name = NULL -- sysnameINSERT INTO dbo.AuditTest ( VALUE ,UserName )VALUES ( 'QQQ' ,'User123' )-------THIS NEW COLUMN ADDED IS NOT AVAILABLE IN "dbo_AuditTest_CT"--------ALTER TABLE AuditTest add TESTCol varchar(10)UPDATE AuditTest SET Value='TESTINGzzz' WHERE Value='QQQ'DELETE from AuditTestselect CASE WHEN [__$operation]=1 THEN 'DELETE' WHEN [__$operation]=2 THEN 'INSERT' WHEN [__$operation]=3 THEN 'OLD VALUE' WHEN [__$operation]=4 THEN 'NEW VALUE' END AS OPERATION ,*from cdc.dbo_AuditTest_CT[code="sql"][/code]Also, what are the other ways we can Audit transactional changes to the tables?Cheers Ram

Error converting data type

Posted: 09 Oct 2013 03:27 AM PDT

I have a query that is comparing two sections and the I am converting the type to numeric (38,0) to compare them.All the section should be numeric ex: '1123' or '1907' but one of the section has alphanumeric characters for the previous year(2012 and 2011) and I am running query to select the data for the current year(2013)So I had filtered out previous year data by using where clause and the query was working fine earlier but today I am getting the error converting data type varchar to numeric even though I am selecting only for current year which does not contain alphanumeric sections.Select * from professortbl A left join classtable B where Cast(A.class_section as numeric(38,0))= Cast (B.class_Section as Numeric(38,0)) Where term >='2013'I am not sure why something would stop working suddenly if it has worked earlier.PLease help.Thnaks,Blyzzard

how to split the comma delimited string into rows without using LOOP, CTE, XML.

Posted: 08 Oct 2013 10:58 PM PDT

DECLARE @PARAMETER NVARCHAR(MAX)SELECT @P = 'MONDAY,SUN,FUN,D'

Version

Posted: 09 Oct 2013 01:48 AM PDT

Hi, I'm just trying to verify if I have a CTP version of SQL 2008 R2.@@version gives me:Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) SERVERPROPERTY('ProductLevel') gives me SP2 only.I suspect I'm not looking in somewhere really obvious!!

Finding Server Name

Posted: 09 Oct 2013 01:20 AM PDT

We have Server name- TESTXXABDatabase name - TEST_USERThis DB have many TablesNow we have to write a query which will search the data in the tables which contains the Server name(TESTXXAB) in it..:w00t:can this be done??

Data Collector - collection_set_4_noncached_collect_and_upload job failing

Posted: 20 Jun 2011 06:16 PM PDT

Morning AllI have not seen this before with all the instances of data collector I have set up but could anyone shed some light.The 4th job in data collector (collection_set_4_noncached_collect_and_upload) is failing with the following error messageOLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid object name 'tempdb.dbo.sysutility_batch_time_internal'.". . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: OLE DB Source [1], Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: Generate T-SQL Package Task, Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.I understand the error, the object isn't there so the task fails.The job step is a cmdexec step with the following linedcexec -c -s 4 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" -m 1Can anyone shed some light on this step so I can fix the issue?This seems to have run three times as under SSIS -> Stored Packages -> MSDB -> Data Collector -> Generated there are 3 sets of collect upload packages, but this may be me reading into the error incorrectly.Any assistance would be great.ThanksAnt

Impact on production server

Posted: 08 Oct 2013 09:13 PM PDT

Hi Team,Heard that "Running SQL Profiler on Production environments will degrade performance of server and it adds additional load on the CPU"What is impact on production server if i ran the profiler thru remote.Please suggest.

Transaction COMMIT

Posted: 08 Oct 2013 09:09 PM PDT

Hi I have this query:BEGIN TRANSACTION INSERT CUSTOMER(NAME, CITY, STATE) VALUES('John C', 'Chicago', 'IL')COMMIT TRANSACTIONBEGIN TRANSACTION INSERT CUSTOMER(NAME, CITY, STATE) VALUES('Bubba C', 'Austin', 'TX')ROLLBACK TRANSACTION SELECT * FROM CUSTOMERNow when I execute the first query, it addds John to my table as it's suppose to, and when I execute the second query it doesn't add Bubba as it's suppose to, but it also delete John who's added in the first query.Why is it so?

setup bootstrap\update cache

Posted: 08 Oct 2013 07:40 PM PDT

can you remove the contents of setup bootstrap\update cache?Its taking a few gbs on my system drive
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog