Wednesday, September 18, 2013

[SQL Server 2008 issues] Not throwing exeception while creating a stored proc if dependent objects do not exist

[SQL Server 2008 issues] Not throwing exeception while creating a stored proc if dependent objects do not exist

Not throwing exeception while creating a stored proc if dependent objects do not exist

Posted: 17 Sep 2013 06:57 PM PDT

Hi,While creating a stored procedure in SQL Server 2008 Standard Edition, it is not throwing any execption and created successfully even if the table it is operating on, does not exist. Is there any settings that I have to enable?Please guide me.Thanks & Regards,Kumar Anand

Problem in query

Posted: 17 Sep 2013 06:37 PM PDT

Hello,i have a problem regarding query,my data is like this EID------checktime-------------status-----123-----7/1/2013 08:00:00 PM-----I---------123-----7/2/2013 02:00:00 AM-----O---------I means in and O means Outemployee enter in a factory on 7/1/2013 08:00:00 PM and out in a factory on 7/2/2013 02:00:00 AM at night becouse after 12:00 date is change.i want a query that shows data like thisEID------checktime-------------status-----123-----7/1/2013 08:00:00 PM-----I---------123-----7/1/2013 02:00:00 AM-----O---------Please Help me out.Thanks

How to caclulate the size of teh DB using extent info ???

Posted: 17 Sep 2013 09:30 AM PDT

I am moved to an old project , but sure why but the previous guy was collecting the total extent for the DB everyday . I am assuming that he was trying to get the DB growth . The problem is , when i do the simple calculation of total Extent * 64 * 1024 to the get the actual size of the DB , I am not getting the size of the DB which I see on GUI . Can someone let me know what is that I am missing here ? I am aware of getting this data using other commands , but I am trying to use the historical data ( using extent ) to get the overall growth story .

LOOKUP ISSUE

Posted: 17 Sep 2013 04:44 PM PDT

Hi Everyone, I am having a problem while using Lookup Scenario : i am loading Dimension table and Fact Table in a package and while loading Dim Table i am using Lookup, for any newrecords by using no match output to destination in lookup but Dim Table failing saying that Can't insert Duplicate Rows when i use thedata viewer it is showing unique rows only i am little confused so please can any one help me.Thanks,Kumar

PK vioaltion in replication

Posted: 17 Sep 2013 04:37 PM PDT

I have no work experience in replication , how to solve the PK vioaltion in transactional replication instead of ignoring the alert ?

Sql Server Authentication

Posted: 17 Sep 2013 04:13 PM PDT

Please help me, what are the system procedures and system functions that are used for validating whether a user is a valid user or not when we click on "connect" button for sql server 2008.

cannot generate SSPI context

Posted: 17 Sep 2013 11:52 AM PDT

Hi, im new with sql server 2008 and i'm having a bit of a problem when connecting with the sql server..it says "cannot generate SSPI context", and this error occurs randomly..sometimes i can connect and sometimes i cannot and there goes the error. pls help me..i already googled it but im confused with their suggestions of troubleshooting and solving the problem..hope you can help me..thank you..

DBA Roles and Responsibilties

Posted: 17 Sep 2013 12:50 AM PDT

All, I am in kind of bad situation here at work. I just took a new job less than 3 months ago and the SR DBA that I work with, come to find out, was totally against this position being filled as he wants to do all of the work himself. He has a hard time answering emails, pretty much wont take the time to answer any questions I may have regarding the environment as I am still getting acclimated. If I dont get any project based work, I sit here reading articles on Database Design, SQL Clusters, Performance Tuning etc...Any time a request comes in for DBA work, I attempt to do it, but he takes gets it done etc... He tells me to find my own things to do. That being said, what are somethings I can do to keep me busy and also learn the environment while proving my self to this guy?I've been through the job description and stuff I was not familiar with, I did. I have documented our SQL Servers to the best of my abilities; looking at any third party software tools that were installed and reading up on those.Does anyone have suggestions on things they do when the first start a DBA role to keep busy, learn, and prove themselves?Thanks and my apologies for the non technical question!!

Query Tuning help

Posted: 17 Sep 2013 07:41 AM PDT

Dear Experts, I understand that without test data and actual DDL it is hard to get anyone to help with. I am just looking for some one to give me an idea on how to fine tune this query. Currently it runs in about 20 seconds and that is too slow. I have feeling there should be a better way to write this query, but I just couldn't get it straight. most of the time spent on the 2 inner query that union together.Thanks![code="sql"] SELECT * FROM ( SELECT D2.DataOwner AS [Defined in Asset Model],D1.DataOwner, OT.ObjectType, R1.ID as RefDef, R1.OUID AS [RefDef Name] FROM IMOC_$Ref REF JOIN IMOC_$RelObjects R1 ON REF.RefDef = R1.ID JOIN IMOC_$RelObjects R2 ON REF.InputID = R2.ID JOIN IMOC_$ObjectTypes OT ON R2.ObjectTypeID = OT.ID JOIN IMOC_$DataOwners D1 ON R2.DataOwnerID = D1.ID JOIN IMOC_$DataOwners D2 ON R1.DataOwnerID = D2.ID WHERE D1.DataOwner = N'SomeValue' GROUP BY D2.DataOwner, D1.DataOwner, OT.ObjectType, R1.ID, R1.OUID UNION SELECT D2.DataOwner AS [Defined in Asset Model],D1.DataOwner, OT.ObjectType, R1.ID as RefDef, R1.OUID AS [RefDef Name] FROM IMOC_$Ref REF JOIN IMOC_$RelObjects R1 ON REF.RefDef = R1.ID JOIN IMOC_$RelObjects R2 ON REF.OutputID = R2.ID JOIN IMOC_$ObjectTypes OT ON R2.ObjectTypeID = OT.ID JOIN IMOC_$DataOwners D1 ON R2.DataOwnerID = D1.ID JOIN IMOC_$DataOwners D2 ON R1.DataOwnerID = D2.ID WHERE D1.DataOwner = N'SomeValue' GROUP BY D2.DataOwner, D1.DataOwner, OT.ObjectType, R1.ID, R1.OUID ) as Ref WHERE NOT EXISTS (SELECT * FROM IMOC_ReferenceTypes WHERE IMOC_ReferenceTypes.[Defined in Asset Model] = Ref.[Defined in Asset Model] AND IMOC_ReferenceTypes.DataOwner = Ref.DataOwner AND IMOC_ReferenceTypes.ObjectType = Ref.ObjectType AND IMOC_ReferenceTypes.RefDef = Ref.RefDef AND IMOC_ReferenceTypes.[RefDef Name] = Ref.[RefDef Name]) CREATE TABLE [dbo].[IMOC_$RelObjects]( [DataOwnerID] [uniqueidentifier] NOT NULL, [ObjectTypeID] [uniqueidentifier] NOT NULL, [ID] [uniqueidentifier] NOT NULL, [OUID] [nvarchar](200) NULL, [FriendlyName] [nvarchar](200) NULL, [Description] [nvarchar](50) NULL, [UpdateIDRefreshed] [uniqueidentifier] NOT NULL, [SourceID] [uniqueidentifier] NULL, [LastModDate] [datetime] NULL, CONSTRAINT [PK_IMOC_$RelObjects] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO CREATE TABLE [dbo].[IMOC_$Ref]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [OutputID] [uniqueidentifier] NOT NULL, [OutputPN] [nvarchar](100) NULL, [InputID] [uniqueidentifier] NOT NULL, [InputPN] [nvarchar](100) NULL, [RefDef] [uniqueidentifier] NOT NULL, [UpdateIDAdded] [uniqueidentifier] NOT NULL, [AddedByDO] [uniqueidentifier] NOT NULL, [Visible] [tinyint] NULL, [Push] [tinyint] NULL, [ShowArrow] [tinyint] NOT NULL, [IO] [tinyint] NULL, [Attachment] [tinyint] NULL, [LDVisible] [tinyint] NULL, [HideInSummary] [tinyint] NOT NULL, [LDStyle] [nvarchar](50) NULL, [GraphicAttributes] [nvarchar](100) NULL, CONSTRAINT [PK_IMOC_$Ref] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_IMOC_$Ref] UNIQUE CLUSTERED ( [OutputID] ASC, [OutputPN] ASC, [InputID] ASC, [InputPN] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[IMOC_$Ref] ADD CONSTRAINT [DF_IMOC_$Ref_ShowArrow] DEFAULT ((1)) FOR [ShowArrow]GOALTER TABLE [dbo].[IMOC_$Ref] ADD DEFAULT ((0)) FOR [HideInSummary]GO CREATE TABLE [dbo].[IMOC_$ObjectTypes]( [DataOwnerID] [uniqueidentifier] NOT NULL, [ID] [uniqueidentifier] NOT NULL, [ObjectType] [nvarchar](50) NOT NULL, [Hidden] [tinyint] NOT NULL, [Private] [tinyint] NOT NULL, [Spare] [tinyint] NOT NULL, [OUIDSeq] [bigint] NOT NULL, [DataType] [nvarchar](10) NULL, CONSTRAINT [PK_IMOC_$ObjectTypes] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[IMOC_$ObjectTypes] ADD DEFAULT ((0)) FOR [Hidden]GOALTER TABLE [dbo].[IMOC_$ObjectTypes] ADD DEFAULT ((0)) FOR [Private]GOALTER TABLE [dbo].[IMOC_$ObjectTypes] ADD DEFAULT ((0)) FOR [Spare]GOALTER TABLE [dbo].[IMOC_$ObjectTypes] ADD CONSTRAINT [DF_IMOC_$ObjectTypes_OUIDSeq] DEFAULT ((0)) FOR [OUIDSeq]GO CREATE TABLE [dbo].[IMOC_$DataOwners]( [ID] [uniqueidentifier] NOT NULL, [Counter] [int] IDENTITY(1,1) NOT NULL, [DataOwner] [nvarchar](50) NOT NULL, [AssetModelID] [uniqueidentifier] NULL, [IsAssetModel] [tinyint] NOT NULL, [Hidden] [tinyint] NOT NULL, [TablesetCreated] [tinyint] NOT NULL, [RemoteServerName] [nvarchar](200) NULL, [RemoteDataOwner] [nvarchar](50) NULL, [ServerName] [nvarchar](200) NULL, [DatabaseName] [nvarchar](200) NULL, [Login] [nvarchar](50) NULL, [Password] [nvarchar](50) NULL, [ConnectionString] [nvarchar](1000) NULL, [DataOwnerType] [nvarchar](50) NULL, [TablePrefix] [nvarchar](50) NULL, [KnowledgeBase] [nvarchar](10) NULL, CONSTRAINT [PK_IMOC_$DataOwners] PRIMARY KEY CLUSTERED ( [Counter] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[IMOC_$DataOwners] ADD CONSTRAINT [DF_IMOC_$DataOwners_IsAssetModel] DEFAULT ((0)) FOR [IsAssetModel]GOALTER TABLE [dbo].[IMOC_$DataOwners] ADD CONSTRAINT [DF_IMOC_$DataOwners_Hidden] DEFAULT ((0)) FOR [Hidden]GOALTER TABLE [dbo].[IMOC_$DataOwners] ADD CONSTRAINT [DF_IMOC_$DataOwners_TablesetCreated] DEFAULT ((0)) FOR [TablesetCreated]GO[/code]

Quickest and most efficient ways to handle data

Posted: 17 Sep 2013 06:55 AM PDT

Hey everyone..I would like to start a discussion to understand what are the different quickest and most efficient ways to handle the following scenarios:1) How to move trillion recordsa) From one SQL Server table to another SQL Server tableb) From SQL Server table to Excel or CSV filec) From Excel or csv file into SQL server table2) How to count trillion records in SQL server Please provide any details if you can..Thank you!!

SQL Server Auditing Fail - Need ideas for capturing parameterized database reads.

Posted: 17 Sep 2013 04:38 AM PDT

I have the requirement of capturing who, what, and when of all CRUD operations on my database. It was originally spec'd out that we would use SQL Server Audit to accomplish this. Unfortunately, SQL Server Audit has a known issue of obfuscating the underlying values of any parameters used in a query. As we're using EntityFramework, pretty much everything is coming in as a parameterized sproc, rendering Audit effectively useless.We could use EF to audit, but it wouldn't capture anyone with direct DB access. My thought now is to use triggers, but I *MUST* be able to see if someone has viewed data, when they viewed it, and what criteria were used. Audit again fails to meet the requirements if anyone with direct DB access runs any sort of parameterized query to view data.Halp?

SQL Server Developer Edition

Posted: 17 Sep 2013 04:16 AM PDT

We want to have 'test,' 'staging,' and 'production' SQL Server Environments. Our production server is SQL Server 2008 R2.We are looking at SQL Server 2008 Developer Edition. How closely does that match up to SQL Server 2008 R2?Can the databases be migrated between versions?

LOG_REUSE_WAIT_DESC is ACTIVE_TRANSACTION but dbcc opentran shows nothing

Posted: 17 Sep 2013 01:41 AM PDT

I was trying to shrink the log on a database in simple recovery.It shrunk by about 10Gb, and then wouldn't shrink any further. in the sys.databases Log_Reuse_Wait_Desc column the reason was "Active_Transaction", however dbcc opentran showed no open transactions.On running a CHECKPOINT and trying the shrink again, the log could be shrunk and the Reuse_wait_desc changed to NOTHING.I cant figure out what happened here, and why a checkpoint would clear any active transactions. Can anyone shed light on this?

Remote Access Connection Manager Service In SSIS

Posted: 17 Sep 2013 03:57 AM PDT

I need create package to check whether the Remote Access Connection Manager Service is running or not and execute message if stopped.. it is very urgent

Import Text File to SQL Database

Posted: 12 Sep 2013 03:08 AM PDT

I have a text file with no delimiters and fixed width fields that I need to import into a SQL Server database. An example of the text file is:012345678901012013TYPEADESC FIELD 8.00123456789001022013TYPEBDESC FIELD 14.00234567890101032013TYPECDESC FIELD .75Text File is set up as EmpID: 10Date: 8 (mmddyyyy)Type: 5Desc: 12Hours: (6,2) DecimalSQL Server table is set up as:Date (datetime)Type: varchar(5)Desc: varchar(12)Hours: decimal(6,2)EmpID: varchar(10)The problem I'm having is with the Date field. How do I convert the date from the text file (mmddyyyy) to the sql server date field (yyyy-mm-dd hh:mm:sss)?

openquery: "DBTYPE" of 130 at compile time and 129 at run time

Posted: 16 Sep 2013 11:47 PM PDT

Hi guys,I want to add a SQLAgent check into my hourly job, alerting if it's down as long as its not XE. My procedure works outside of of openquery, works for servers where it is down but it does not work for servers where the agent is running.Full error message: http://pastebin.com/hQLZ5rwbSo, let's say I have SRV01, SRV02, SRV03. 01 is XE, 02 has agent running, 03 has agent down.It should have 2 rows of 'dird' (I thought there was an issue with null) and 1 saying 03 is down.Instead I only get 03 record and I get errors for the other 2 (as shown above). Any idea how to address this compile/run issue? I don't see the problem between returning 'dird' or a server.Code:[code]DECLARE @query varchar(max);set @query = 'insert into dbo.DB_CHANGES (INSTANCE,DB_NAME,CHANGE_INFO,CHANGE_DATE)select * from openquery(' + @server + ',''IF NOT EXISTS (SELECT 1 FROM MASTER.dbo.sysprocesses WHERE program_name = N''''SQLAgent - Generic Refresher'''')BEGIN IF EXISTS (select 1 where convert(varchar(30), convert(sql_variant, SERVERPROPERTY(''''edition''''))) not like ''''Express%'''') BEGIN SELECT @@SERVERNAME [INSTANCE], null [DB_NAME], ''''SQLAgent not running on '''' + @@SERVERNAME [CHANGE_INFO], GETDATE() [CHANGE_DATE] END ELSE SELECT ''''dird'''' [INSTANCE],''''dird'''' [DB_NAME],''''dird'''' [CHANGE_INFO], getdate() [CHANGE_DATE] ENDELSESELECT ''''dird'''' [INSTANCE],''''dird'''' [DB_NAME],''''dird'''' [CHANGE_INFO], getdate() [CHANGE_DATE]'')';exec(@query);[/code]

Job steps - Data sources

Posted: 16 Sep 2013 08:26 PM PDT

Hi,Within a SQL job I would like to to get the information from the Data sources tab, so for each step I would like to have the following information:Connnection ManagerDescriptionConnection StringIs this possible?Thanks

active active sql cluster

Posted: 16 Sep 2013 10:35 PM PDT

Hi All,We need to install active active SQL cluster 2008 r2 as per the client requirementCan any one tell me do we need to install two mSDTC services( 2 msdtc network name,2 MSDTC ip address) or only one is enough.If 2 msdtc is required then Kindly confirm the steps are correct for the active active cluster.1)we will configure msdtc .MSDTC1 ,ip addresss ,Drivemsdtc1MSDTC2, ip address ,DRIVEmsdtc2after msdtc configuration then we will install sql server instances on node1 and node22)Installing default instance ( network nameA,ip addressA,drivedataA1,drivelogA2)a)Node1 starting setup for installing instanceAb)adding Node on NODE23)for Named instance :( network nameB,ip addressB,drivedataB1,drivelogB2)a)From Node2 starting setup for installing Sql InstanceB b)adding node on Node1Advance thanks..

Help with Excel (Un)pivot Import

Posted: 16 Sep 2013 09:24 PM PDT

This is probably really simple but I just can't see it this morning. I have an Excel table like this[code="other"]| AGE | 2.00% | 2.25% | 2.50%| 2.75% | 3.00% || 23 | 27 | 29 | 31 | 33 | 35 || 26 | 28 | 29 | 31 | 33 | 35 || 28 | 28 | 30 | 32 | 33 | 35 |[/code]And I want to flatten this data out into SQL Server with a table as follows[code="other"]| AGE | Rate | Amount || 23 | 2.00 | 27 || 23 | 2.25 | 29 || 23 | 2.50 | 31 || 23 | 2.75 | 33 || 23 | 3.00 | 35 || 26 | 2.00 | 28 || 26 | 2.25 | 29 || 26 | 2.50 | 31 || 26 | 2.75 | 33 || 26 | 3.00 | 35 || 28 | 2.00 | 28 || 28 | 2.25 | 30 || 28 | 2.50 | 32 || 28 | 2.75 | 33 || 28 | 3.00 | 35 |[/code]Table definition for import table[code="sql"]CREATE TABLE GADRates ( Age INT NOT NULL, Rate DECIMAL(6,3) NOT NULL, Amount MONEY NOT NULL );[/code] Example Excel table attached.This imports as follows:[code="sql"]CREATE TABLE [dbo].[Sheet1$]( [AGE] [float] NULL, [2#00%] [money] NULL, [2#25%] [money] NULL, [2#50%] [money] NULL, [2#75%] [money] NULL, [3#00%] [money] NULL, [3#25%] [money] NULL, [3#50%] [money] NULL, [3#75%] [money] NULL)[/code]To shortcut excel import here is a data creation script[code="sql"]INSERT [dbo].[Sheet1$] ([AGE], [2#00%], [2#25%], [2#50%], [2#75%], [3#00%], [3#25%], [3#50%], [3#75%])SELECT 23, 27.0000, 29.0000, 31.0000, 33.0000, 35.0000, 36.0000, 38.0000, 40.0000 UNION ALLSELECT 26, 28.0000, 29.0000, 31.0000, 33.0000, 35.0000, 37.0000, 39.0000, 40.0000 UNION ALLSELECT 27, 28.0000, 30.0000, 31.0000, 33.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALLSELECT 28, 28.0000, 30.0000, 32.0000, 33.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALLSELECT 29, 28.0000, 30.0000, 32.0000, 34.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALLSELECT 30, 29.0000, 30.0000, 32.0000, 34.0000, 36.0000, 38.0000, 39.0000, 41.0000 UNION ALLSELECT 31, 29.0000, 31.0000, 32.0000, 34.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALLSELECT 32, 29.0000, 31.0000, 33.0000, 34.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALLSELECT 33, 30.0000, 31.0000, 33.0000, 35.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALLSELECT 34, 30.0000, 32.0000, 33.0000, 35.0000, 37.0000, 39.0000, 40.0000, 42.0000 UNION ALLSELECT 35, 30.0000, 32.0000, 34.0000, 35.0000, 37.0000, 39.0000, 41.0000, 43.0000 UNION ALLSELECT 36, 31.0000, 32.0000, 34.0000, 36.0000, 37.0000, 39.0000, 41.0000, 43.0000 UNION ALLSELECT 37, 31.0000, 33.0000, 34.0000, 36.0000, 38.0000, 40.0000, 41.0000, 43.0000;[/code]When you import the Excel into SQL Server, it changes the 2.00% into column names [2#00%] - and my best effort so far doesn't even work or take into account the amount column.nonworking code [code="sql"] SELECT [Age] ,[2#00%] ,[2#25%] ,[2#50%] ,[2#75%] ,[3#00%] FROM ( SELECT [Age], [2#00%], [2#25%], [2#50%], [2#75%], [3#00%] FROM [dbo].[Sheet1$] ) AS PVTUNPIVOT ( [AGE] FOR RATE IN ([2#00%], [2#25%], [2#50%], [2#75%], [3#00%])) AS UNPVT[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog