Saturday, July 20, 2013

[SQL Server 2008 issues] Help with SQL query

[SQL Server 2008 issues] Help with SQL query

Help with SQL query

Posted: 19 Jul 2013 05:00 PM PDT

Hello all, I am not sure whether this is the right thread to post this or not.Anyways, I have one table as follows:table mastertab(op INT,msisdn VARCHAR (12),imei VARCHAR (20));Sample values that I have are as follows:aa, 0191, 111222333aa, 0191, 111222444aa, 0192, 111222333aa, 0192, 111222444aa, 0192, 111222555aa, 0193, 111222333bb, 0171, 222222333bb, 0171, 222222444bb, 0172, 222222444cc, 0152, 333222444Now, i want the output to show as (op, imei_count) set where imei_count displays the total number of duplicate imei and groups it by op. Expected output is as follows:op imei_count-- ----------aa 2bb 1cc 0Any help in this regard will highly be appreciated.Thanks,Mehfuz

Tempdb issue

Posted: 07 Jul 2013 04:42 PM PDT

we are facing issue in tempdb file size , initial size we allocate 2 GB , but after restart sql server , size would be 1 MB , its gone 2 GB, plz anyone help , How size will keeping on 2 GB after restart sql server ..Any settings ? ThanksJerry

Where is the beginners page, i need help?

Posted: 19 Jul 2013 03:26 AM PDT

I need advice, would be straight forward for most of you. i have a 1 week old .bak file. Then, hdd failure came. i re installed the same sql (2008), restored ok. The connecting application provides error ole80040E14. I assume, the db is corrupt, and i need to repair the mdf file with a tool, expensive. Database 1.3 gb, but containing gold. Any resopnse?

SQL Broker Performance Question

Posted: 19 Jul 2013 02:09 AM PDT

I am having some performance issues when a stored procedure is executed going through a broker-based request. The SP is doing a simple join-based update. Sometimes it will update 1M rows in under 5 minutes.When it is bad the same update for 300K rows will run for 6 hours.There appear to be no other processes competing for resources on the server. CPU usage remains constant during this time at about 30-35 %. SQL Wait Stats during the execution time are high for OLEDB, BROKER_EVENTHANDLER & BROKER_RECEIVE_WAITFOR. Any ideas greatly appreciated.

Need A Help in DATA MASKING in SQL SERVER 2008

Posted: 19 Jul 2013 01:44 AM PDT

Hello Friends,I want to mask certain fields in employee_bank_account_info table, which are very sentive information.I searched for it , i found verious third-party tools online, which I can not use in my current enviornment.I need help to find out some way of data masking via sql script or use of any inbuilt function or library in sql server 2008!!!Please give me any suggestion or example about this.thanks in advance.

SSIS package creation

Posted: 19 Jul 2013 06:04 AM PDT

Hi,I need to import data from Oracle table to SQL Server table using SSIS.I'm using data flow task with OLEDB source, Data Conversion and OLEDB destinationHere is the table script for Oracle table. Can I have equivalent SQL Server Table script for this Oracle Table? CREATE TABLE AMC.POLICY( POLICY NUMBER NOT NULL, AGENCY NUMBER, WRITING_COMPANY NUMBER, NEW_WRITING_COMPANY NUMBER, DEC_ADDR NUMBER, POLICY_PREFIX NUMBER, NAMED_INSURED NUMBER, INSPECTOR NUMBER, RENEWED_AS NUMBER, NEW_WRITING_DATE DATE, RENEWED_FROM NUMBER, NEW_AGENCY NUMBER, NEW_AGENCY_DATE DATE, QUOTE_FLAG NUMBER(38) DEFAULT 0, PRODUCER VARCHAR2(80 BYTE), TERM_TYPE VARCHAR2(80 BYTE), CURRENT_DEC NUMBER, TERM_MONTHS NUMBER(38), CURRENT_DEC_DATE DATE, TERM_EFFECTIVE_DATE DATE, UMBRELLA VARCHAR2(80 BYTE), AGENCY_CODE NUMBER(38), TERM_EXPIRATION_DATE DATE, LEGAL_TEXT VARCHAR2(80 BYTE), POLICY_NBR VARCHAR2(80 BYTE), EMPLOYEE_FLAG NUMBER(38) DEFAULT 0, INCEPTION_DATE DATE, QUOTE_SUFFIX VARCHAR2(80 BYTE), ENDORSE_MODE NUMBER(38) DEFAULT 1, QUOTE_REASON VARCHAR2(40 BYTE), POLICY_SEARCH_NBR VARCHAR2(80 BYTE), APP_CHECK VARCHAR2(80 BYTE), AUDIT_FREQ NUMBER(38), TERM_NBR NUMBER(38) DEFAULT 1 NOT NULL, REMITTER VARCHAR2(40 BYTE), AUDIT_FORM VARCHAR2(40 BYTE), AUDIT_DATE DATE, MVR_REQUEST_DATE DATE, POLICY_STATUS VARCHAR2(40 BYTE), APP_REMITTER VARCHAR2(40 BYTE), POLICY_STATUS_DATE DATE, PYMT_PLAN VARCHAR2(40 BYTE), ACTIVITY_STATUS VARCHAR2(40 BYTE), APP_CASH_AMT FLOAT(126), ACTIVITY_STATUS_DATE DATE, APP_CHECK_KEY NUMBER, CANCEL_WAIT_DAYS NUMBER(38), CANCEL_EFFECTIVE_DATE DATE, APP_INSURED_NAME VARCHAR2(80 BYTE), CANCEL_REQUEST_BY VARCHAR2(40 BYTE), RISK_GROSS_AMT FLOAT(126), CANCEL_CHECK_SENT VARCHAR2(40 BYTE), CANCEL_INSPECTION NUMBER(38) DEFAULT 0, REINSTATED_EFFECTIVE_DATE DATE, NONRENEW_WAIT_DAYS NUMBER(38), NON_PAY_NOTICE NUMBER(38) DEFAULT 0, CANCEL_NOTICE_DATE DATE, NONRENEW_NOTICE_DATE DATE, NONRENEW_INSPECTION NUMBER(38) DEFAULT 0, CANCEL_PREM_DATE DATE, EXTENSION_DATE DATE, CANCEL_ACTUAL_DATE DATE, REISSUE_FEE NUMBER(38) DEFAULT 0, CREATE_ID VARCHAR2(30 BYTE) DEFAULT USER, CANCEL_NSF_AMT FLOAT(126), FIRST_MODIFIED DATE DEFAULT SYSDATE, AUDIT_ID VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, LAST_MODIFIED DATE DEFAULT SYSDATE NOT NULL, REINSTATED_REASON VARCHAR2(40 BYTE), NON_PAY_COUNT NUMBER(38), BILL_TYPE VARCHAR2(40 BYTE), LAST_BILL_DATE DATE, NEXT_BILL_DATE DATE, NONRENEW_EFFECTIVE_DATE DATE, BILL_DUE_DATE DATE, CURRENT_GROSS_OS FLOAT(126), CURRENT_NET_OS FLOAT(126), COMMISSION_PCT FLOAT(126), CURRENT_INFORCE FLOAT(126), CARRY_DATE DATE, PREVIOUS_CARRIER VARCHAR2(40 BYTE), REFERRED_BY VARCHAR2(80 BYTE), CURRENT_MIN_DUE FLOAT(126), RENEWAL_CERT_COUNT NUMBER(38), FACULTATIVE_FLAG NUMBER(38) DEFAULT 0, WRITING_CODE NUMBER(38), BRANCH_CODE NUMBER(38), PRINT_CANCEL NUMBER(38) DEFAULT 0, PRINT_NONRENEW NUMBER(38) DEFAULT 0, PRINT_REINSTATE NUMBER(38) DEFAULT 0, PRINT_NR_RESCIND NUMBER(38) DEFAULT 0, PRINT_AGENCY NUMBER(38) DEFAULT 0, BILL_STATUS VARCHAR2(40 BYTE), BILL_STATUS_DATE DATE, NOTICE_STATUS VARCHAR2(40 BYTE), NOTICE_STATUS_DATE DATE, CANCEL_COUNT NUMBER(38), CANCEL_NOTICE_COUNT NUMBER(38), FULL_TERM NUMBER(38) DEFAULT 0, NEW_AGENCY_DEC_FLAG NUMBER(38), BUSINESS_LINE NUMBER, AGENCY_EXTENSION NUMBER(38), LEGAL_TEXT_LONG VARCHAR2(256 BYTE), HOLD_NOTICE_DATE DATE, PRINT_CANCEL_NP NUMBER(38) DEFAULT 0, PMWRITING_COMPANY NUMBER, QUOTE_STATUS VARCHAR2(40 BYTE), INFLATION_GUARD_PCT FLOAT(126), PRINT_DECLINATION NUMBER(38) DEFAULT 0, CANCEL_FUNC_DEPT VARCHAR2(80 BYTE), NONRENEW_FUNC_DEPT VARCHAR2(80 BYTE), CANCEL_TYPE VARCHAR2(40 BYTE), NONRENEW_TYPE VARCHAR2(40 BYTE), PARENT_AGENCY_KEY NUMBER, INFLATION_GUARD VARCHAR2(40 BYTE), DEC_FUNC_DEPT VARCHAR2(80 BYTE), SCANLINE VARCHAR2(254 BYTE), VIEW_PARENT_AGENCY NUMBER(38) DEFAULT 0 NOT NULL, CANCEL_SYSTEM NUMBER(38) DEFAULT 0, IS_SPECIAL_BILL NUMBER(1) DEFAULT 0, UMBRELLA_KEY NUMBER, REVERSE_REISSUE_FEE NUMBER DEFAULT 0, CONV_IN_THIS_TERM NUMBER DEFAULT 0, RENEWAL_CR_OVERRIDE_FLAG NUMBER, APP_AGENCY NUMBER, ACCOUNT NUMBER, CONTROL NUMBER, SPECIALTY_PGM NUMBER, ONLINE_REF_NUMBER NUMBER, GLOBAL_TERR_CLEARANCE NUMBER, GTC_ACTION NUMBER, LOSS_FREE_CREDIT NUMBER, ENTERED_BY VARCHAR2(80 BYTE), ENDORSE_QUOTE_INFO NUMBER, STP_FLAG NUMBER(10) DEFAULT 0, MVR_STATUS VARCHAR2(40 BYTE), APP_CASH_TYPE VARCHAR2(80 BYTE))________________________________________________________________________________________________________________[b]In the OLEDB Destination editior, I'm getting the below query when I click to create new table. Can I use this as is or do I need to modify? Because it's creating the columns twice with copy. Why columns are getting created twice?[i][/i][/b]CREATE TABLE [OLE DB Destination] ( [POLICY] nvarchar(38), [AGENCY] nvarchar(38), [WRITING_COMPANY] nvarchar(38), [NEW_WRITING_COMPANY] nvarchar(38), [DEC_ADDR] nvarchar(38), [POLICY_PREFIX] nvarchar(38), [NAMED_INSURED] nvarchar(38), [INSPECTOR] nvarchar(38), [RENEWED_AS] nvarchar(38), [NEW_WRITING_DATE] datetime, [RENEWED_FROM] nvarchar(38), [NEW_AGENCY] nvarchar(38), [NEW_AGENCY_DATE] datetime, [QUOTE_FLAG] numeric(38,0), [PRODUCER] varchar(80), [TERM_TYPE] varchar(80), [CURRENT_DEC] nvarchar(38), [TERM_MONTHS] numeric(38,0), [CURRENT_DEC_DATE] datetime, [TERM_EFFECTIVE_DATE] datetime, [UMBRELLA] varchar(80), [AGENCY_CODE] numeric(38,0), [TERM_EXPIRATION_DATE] datetime, [LEGAL_TEXT] varchar(80), [POLICY_NBR] varchar(80), [EMPLOYEE_FLAG] numeric(38,0), [INCEPTION_DATE] datetime, [QUOTE_SUFFIX] varchar(80), [ENDORSE_MODE] numeric(38,0), [QUOTE_REASON] varchar(40), [POLICY_SEARCH_NBR] varchar(80), [APP_CHECK] varchar(80), [AUDIT_FREQ] numeric(38,0), [TERM_NBR] numeric(38,0), [REMITTER] varchar(40), [AUDIT_FORM] varchar(40), [AUDIT_DATE] datetime, [MVR_REQUEST_DATE] datetime, [POLICY_STATUS] varchar(40), [APP_REMITTER] varchar(40), [POLICY_STATUS_DATE] datetime, [PYMT_PLAN] varchar(40), [ACTIVITY_STATUS] varchar(40), [APP_CASH_AMT] float, [ACTIVITY_STATUS_DATE] datetime, [APP_CHECK_KEY] nvarchar(38), [CANCEL_WAIT_DAYS] numeric(38,0), [CANCEL_EFFECTIVE_DATE] datetime, [APP_INSURED_NAME] varchar(80), [CANCEL_REQUEST_BY] varchar(40), [RISK_GROSS_AMT] float, [CANCEL_CHECK_SENT] varchar(40), [CANCEL_INSPECTION] numeric(38,0), [REINSTATED_EFFECTIVE_DATE] datetime, [NONRENEW_WAIT_DAYS] numeric(38,0), [NON_PAY_NOTICE] numeric(38,0), [CANCEL_NOTICE_DATE] datetime, [NONRENEW_NOTICE_DATE] datetime, [NONRENEW_INSPECTION] numeric(38,0), [CANCEL_PREM_DATE] datetime, [EXTENSION_DATE] datetime, [CANCEL_ACTUAL_DATE] datetime, [REISSUE_FEE] numeric(38,0), [CREATE_ID] varchar(30), [CANCEL_NSF_AMT] float, [FIRST_MODIFIED] datetime, [AUDIT_ID] varchar(30), [LAST_MODIFIED] datetime, [REINSTATED_REASON] varchar(40), [NON_PAY_COUNT] numeric(38,0), [BILL_TYPE] varchar(40), [LAST_BILL_DATE] datetime, [NEXT_BILL_DATE] datetime, [NONRENEW_EFFECTIVE_DATE] datetime, [BILL_DUE_DATE] datetime, [CURRENT_GROSS_OS] float, [CURRENT_NET_OS] float, [COMMISSION_PCT] float, [CURRENT_INFORCE] float, [CARRY_DATE] datetime, [PREVIOUS_CARRIER] varchar(40), [REFERRED_BY] varchar(80), [CURRENT_MIN_DUE] float, [RENEWAL_CERT_COUNT] numeric(38,0), [FACULTATIVE_FLAG] numeric(38,0), [WRITING_CODE] numeric(38,0), [BRANCH_CODE] numeric(38,0), [PRINT_CANCEL] numeric(38,0), [PRINT_NONRENEW] numeric(38,0), [PRINT_REINSTATE] numeric(38,0), [PRINT_NR_RESCIND] numeric(38,0), [PRINT_AGENCY] numeric(38,0), [BILL_STATUS] varchar(40), [BILL_STATUS_DATE] datetime, [NOTICE_STATUS] varchar(40), [NOTICE_STATUS_DATE] datetime, [CANCEL_COUNT] numeric(38,0), [CANCEL_NOTICE_COUNT] numeric(38,0), [FULL_TERM] numeric(38,0), [NEW_AGENCY_DEC_FLAG] numeric(38,0), [BUSINESS_LINE] nvarchar(38), [AGENCY_EXTENSION] numeric(38,0), [LEGAL_TEXT_LONG] varchar(256), [HOLD_NOTICE_DATE] datetime, [PRINT_CANCEL_NP] numeric(38,0), [PMWRITING_COMPANY] nvarchar(38), [QUOTE_STATUS] varchar(40), [INFLATION_GUARD_PCT] float, [PRINT_DECLINATION] numeric(38,0), [CANCEL_FUNC_DEPT] varchar(80), [NONRENEW_FUNC_DEPT] varchar(80), [CANCEL_TYPE] varchar(40), [NONRENEW_TYPE] varchar(40), [PARENT_AGENCY_KEY] nvarchar(38), [INFLATION_GUARD] varchar(40), [DEC_FUNC_DEPT] varchar(80), [SCANLINE] varchar(254), [VIEW_PARENT_AGENCY] numeric(38,0), [CANCEL_SYSTEM] numeric(38,0), [IS_SPECIAL_BILL] numeric(1,0), [UMBRELLA_KEY] nvarchar(38), [REVERSE_REISSUE_FEE] nvarchar(38), [CONV_IN_THIS_TERM] nvarchar(38), [RENEWAL_CR_OVERRIDE_FLAG] nvarchar(38), [APP_AGENCY] nvarchar(38), [ACCOUNT] nvarchar(38), [CONTROL] nvarchar(38), [SPECIALTY_PGM] nvarchar(38), [ONLINE_REF_NUMBER] nvarchar(38), [GLOBAL_TERR_CLEARANCE] nvarchar(38), [GTC_ACTION] nvarchar(38), [LOSS_FREE_CREDIT] nvarchar(38), [ENTERED_BY] varchar(80), [ENDORSE_QUOTE_INFO] nvarchar(38), [STP_FLAG] numeric(10,0), [MVR_STATUS] varchar(40), [APP_CASH_TYPE] varchar(80), [Copy of POLICY] nvarchar(38), [Copy of AGENCY] nvarchar(38), [Copy of WRITING_COMPANY] nvarchar(38), [Copy of NEW_WRITING_COMPANY] nvarchar(38), [Copy of DEC_ADDR] nvarchar(38), [Copy of POLICY_PREFIX] nvarchar(38), [Copy of NAMED_INSURED] nvarchar(38), [Copy of INSPECTOR] nvarchar(38), [Copy of RENEWED_AS] nvarchar(38), [Copy of NEW_WRITING_DATE] datetime, [Copy of RENEWED_FROM] nvarchar(38), [Copy of NEW_AGENCY] nvarchar(38), [Copy of NEW_AGENCY_DATE] datetime, [Copy of QUOTE_FLAG] numeric(38,0), [Copy of PRODUCER] varchar(80), [Copy of TERM_TYPE] varchar(80), [Copy of CURRENT_DEC] nvarchar(38), [Copy of TERM_MONTHS] numeric(38,0), [Copy of CURRENT_DEC_DATE] datetime, [Copy of TERM_EFFECTIVE_DATE] datetime, [Copy of UMBRELLA] varchar(80), [Copy of AGENCY_CODE] numeric(38,0), [Copy of TERM_EXPIRATION_DATE] datetime, [Copy of LEGAL_TEXT] varchar(80), [Copy of POLICY_NBR] varchar(80), [Copy of EMPLOYEE_FLAG] numeric(38,0), [Copy of INCEPTION_DATE] datetime, [Copy of QUOTE_SUFFIX] varchar(80), [Copy of ENDORSE_MODE] numeric(38,0), [Copy of QUOTE_REASON] varchar(40), [Copy of POLICY_SEARCH_NBR] varchar(80), [Copy of APP_CHECK] varchar(80), [Copy of AUDIT_FREQ] numeric(38,0), [Copy of TERM_NBR] numeric(38,0), [Copy of REMITTER] varchar(40), [Copy of AUDIT_FORM] varchar(40), [Copy of AUDIT_DATE] datetime, [Copy of MVR_REQUEST_DATE] datetime, [Copy of POLICY_STATUS] varchar(40), [Copy of APP_REMITTER] varchar(40), [Copy of POLICY_STATUS_DATE] datetime, [Copy of PYMT_PLAN] varchar(40), [Copy of ACTIVITY_STATUS] varchar(40), [Copy of APP_CASH_AMT] float, [Copy of ACTIVITY_STATUS_DATE] datetime, [Copy of APP_CHECK_KEY] nvarchar(38), [Copy of CANCEL_WAIT_DAYS] numeric(38,0), [Copy of CANCEL_EFFECTIVE_DATE] datetime, [Copy of APP_INSURED_NAME] varchar(80), [Copy of CANCEL_REQUEST_BY] varchar(40), [Copy of RISK_GROSS_AMT] float, [Copy of CANCEL_CHECK_SENT] varchar(40), [Copy of CANCEL_INSPECTION] numeric(38,0), [Copy of REINSTATED_EFFECTIVE_DATE] datetime, [Copy of NONRENEW_WAIT_DAYS] numeric(38,0), [Copy of NON_PAY_NOTICE] numeric(38,0), [Copy of CANCEL_NOTICE_DATE] datetime, [Copy of NONRENEW_NOTICE_DATE] datetime, [Copy of NONRENEW_INSPECTION] numeric(38,0), [Copy of CANCEL_PREM_DATE] datetime, [Copy of EXTENSION_DATE] datetime, [Copy of CANCEL_ACTUAL_DATE] datetime, [Copy of REISSUE_FEE] numeric(38,0), [Copy of CREATE_ID] varchar(30), [Copy of CANCEL_NSF_AMT] float, [Copy of FIRST_MODIFIED] datetime, [Copy of AUDIT_ID] varchar(30), [Copy of LAST_MODIFIED] datetime, [Copy of REINSTATED_REASON] varchar(40), [Copy of NON_PAY_COUNT] numeric(38,0), [Copy of BILL_TYPE] varchar(40), [Copy of LAST_BILL_DATE] datetime, [Copy of NEXT_BILL_DATE] datetime, [Copy of NONRENEW_EFFECTIVE_DATE] datetime, [Copy of BILL_DUE_DATE] datetime, [Copy of CURRENT_GROSS_OS] float, [Copy of CURRENT_NET_OS] float, [Copy of COMMISSION_PCT] float, [Copy of CURRENT_INFORCE] float, [Copy of CARRY_DATE] datetime, [Copy of PREVIOUS_CARRIER] varchar(40), [Copy of REFERRED_BY] varchar(80), [Copy of CURRENT_MIN_DUE] float, [Copy of RENEWAL_CERT_COUNT] numeric(38,0), [Copy of FACULTATIVE_FLAG] numeric(38,0), [Copy of WRITING_CODE] numeric(38,0), [Copy of BRANCH_CODE] numeric(38,0), [Copy of PRINT_CANCEL] numeric(38,0), [Copy of PRINT_NONRENEW] numeric(38,0), [Copy of PRINT_REINSTATE] numeric(38,0), [Copy of PRINT_NR_RESCIND] numeric(38,0), [Copy of PRINT_AGENCY] numeric(38,0), [Copy of BILL_STATUS] varchar(40), [Copy of BILL_STATUS_DATE] datetime, [Copy of NOTICE_STATUS] varchar(40), [Copy of NOTICE_STATUS_DATE] datetime, [Copy of CANCEL_COUNT] numeric(38,0), [Copy of CANCEL_NOTICE_COUNT] numeric(38,0), [Copy of FULL_TERM] numeric(38,0), [Copy of NEW_AGENCY_DEC_FLAG] numeric(38,0), [Copy of BUSINESS_LINE] nvarchar(38), [Copy of AGENCY_EXTENSION] numeric(38,0), [Copy of LEGAL_TEXT_LONG] varchar(256), [Copy of HOLD_NOTICE_DATE] datetime, [Copy of PRINT_CANCEL_NP] numeric(38,0), [Copy of PMWRITING_COMPANY] nvarchar(38), [Copy of QUOTE_STATUS] varchar(40), [Copy of INFLATION_GUARD_PCT] float, [Copy of PRINT_DECLINATION] numeric(38,0), [Copy of CANCEL_FUNC_DEPT] varchar(80), [Copy of NONRENEW_FUNC_DEPT] varchar(80), [Copy of CANCEL_TYPE] varchar(40), [Copy of NONRENEW_TYPE] varchar(40), [Copy of PARENT_AGENCY_KEY] nvarchar(38), [Copy of INFLATION_GUARD] varchar(40), [Copy of DEC_FUNC_DEPT] varchar(80), [Copy of SCANLINE] varchar(254), [Copy of VIEW_PARENT_AGENCY] numeric(38,0), [Copy of CANCEL_SYSTEM] numeric(38,0), [Copy of IS_SPECIAL_BILL] numeric(1,0), [Copy of UMBRELLA_KEY] nvarchar(38), [Copy of REVERSE_REISSUE_FEE] nvarchar(38), [Copy of CONV_IN_THIS_TERM] nvarchar(38), [Copy of RENEWAL_CR_OVERRIDE_FLAG] nvarchar(38), [Copy of APP_AGENCY] nvarchar(38), [Copy of ACCOUNT] nvarchar(38), [Copy of CONTROL] nvarchar(38), [Copy of SPECIALTY_PGM] nvarchar(38), [Copy of ONLINE_REF_NUMBER] nvarchar(38), [Copy of GLOBAL_TERR_CLEARANCE] nvarchar(38), [Copy of GTC_ACTION] nvarchar(38), [Copy of LOSS_FREE_CREDIT] nvarchar(38), [Copy of ENTERED_BY] varchar(80), [Copy of ENDORSE_QUOTE_INFO] nvarchar(38), [Copy of STP_FLAG] numeric(10,0), [Copy of MVR_STATUS] varchar(40), [Copy of APP_CASH_TYPE] varchar(80))

mssql split column string into rows

Posted: 19 Jul 2013 05:15 AM PDT

I have select resultsCAGE_CODE BUS_TYPE_STRING6UC62 2U.V26UHK6 2X.A5.VW.XS6U4M3 2U.V26U7L8 23.27.2X.8E.8W.A2.PI.VW6U4X4 2X.VW6UJU9 2U.LJ.V26U6T9 2X.VW.XS6U9K1 2X.LJ.VW5NFQ4 27.2X.8E.8W.A2.HQ.LJ.VW5R4D8 27.2X.8E.8W.A2.HQ.LJ.VWand I want to split BUS_TYPE_STRING into row like 6UC62 2U6UC62 V26UHK6 2X6UHK6 A56UHK6 VW6UHK6 XS....What is the bestway to achieve this?

Double Hop Question

Posted: 19 Jul 2013 04:50 AM PDT

If I use my laptop to connect to server A using SSMS and run a query there that joins to a table on server B, I get the known response, "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." If I remote desktop to server A and run the same query using SSMS, it exec's successfully (it's only "single-hop"). If I then run the original query on the laptop again, it also exec's successfully, I think because the Kerberos authentication is somehow allowing me to run queries for a limited time from my laptop.Here's the question: server B is in a different domain than server A and the network folks don't want to / can't get both trusted domains and the Active Directory configurations working needed just to solve the double-hop problem, so I'm left looking for a work-around. I have a small set of users. I'd like to allow them remote desktop access to server A and when they log in, have a sqlcmd run in a batch script that just connects to server B. Then the Kerberos ticket should allow them to run queries from their laptops. When I try it myself: - remote desktop to server A- open a cmd prompt- run sqlcmd -S serverB -d Db1 -q "SELECT top 1 ID from dbo.company;" [runs successfully]- go back to my laptop and run the original query, I get "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." again.Please help.Thanks,Seth

MERGE statement - WHEN NOT MATCHED DELETE takes ages

Posted: 19 Jul 2013 01:58 AM PDT

I am using the MERGE functionality in SQL 2008.My code works fine. However if I clean the buffers I've noticed that the WHEN NOT MATCHED DELETE statement adds an extra 8 seconds onto by sub 1 second query even if there is nothing to delete Is this usually the case with the MERGE statement?Thanks

Help on finding the total space used by a database schema

Posted: 19 Jul 2013 02:06 AM PDT

I am using SQL server 2008 R2 and I would like to find the total space used by just the schema of the database, not the data.Thanks

Reporting Services scheduling- hourly between 8 and 5 impossible?

Posted: 04 Jun 2012 05:14 AM PDT

Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something? If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?

creating a non sequential incremental number field

Posted: 18 Jul 2013 08:57 PM PDT

Client Funding Total Commissioned Value Payment Frequency1 1 100 4 weeks2 2 158 4 weeks3 3 254 4 weeks4 4 36 4 weeks5 5 21 4 weeksMy solution to the problem of creating a funding stream is to create a new table with two variable and one fixed data columns. Increment – This is determined by the Payment Frequency value within the Funding Details tableDate – This shows the days on which funding payments are received. It is determined by adding the Increment value to the Start Date for each record.Income – This is a fixed value taken directly from the Funding Details table (Total Commissioned Value).Client 1 Funding StreamIncrement Date Income1 01/04/2013 10028 29/04/2013 10055 26/05/2013 10082 22/06/2013 100109 19/07/2013 100136 15/08/2013 100163 11/09/2013 100190 08/10/2013 100217 04/11/2013 100244 01/12/2013 100271 28/12/2013 100298 24/01/2014 100325 20/02/2014 100352 19/03/2014 100379 15/04/2014 100IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FundingReportTable]') AND type in (N'U'))DROP TABLE [dbo].[FundingReportTable];declare @date datetime = '20100101'CREATE TABLE [DBO].[FundingStream](Id_num INT IDENTITY(1,28),Date DATETIME NOT NULL,Income NUMERICAL (5,2))INSERT INTO [FundingStream](Date,Income)SELECT([SELECT dateadd(dd,id_num,'20100101') FROM master..spt_values WHERE dateadd(dd,id_num,'20100101')<dateadd(yy,1,@date)],[FundingDetails].[Total Commissioned Value])FROM FundingDetails;When this code is run I get the following error message ….Microsoft Server error 102.can anyone help?

Database defragmentation and autogrowth settings

Posted: 19 Jul 2013 01:21 AM PDT

We do have some maintenance plan for our sql server 2008 r2 express.Every month we do defragment of the database if any table has page count more tahn 50 for any table and average fragmentation more than 20.If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.This is what we are doing till now.But we found that autogrowth events are resource incentive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?Please tell me a solution.

Alerts for Database Mirroring

Posted: 15 Jul 2013 01:30 AM PDT

HiI'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance (i.e. mirroring becomes suspended/inactive)So far, the methods I've found require the Service Broker to be enabled on the Mirrored DB, unfortunately, this cannot be enabled in this scenario.Is there any other simple way that I can simply set MSSQL to notify me (via email) when Mirroring isn't active?Thanks.

How to take backup of Single table and how to restore?

Posted: 13 Jul 2013 03:44 PM PDT

Hi... How to take backup of Single table and how to restore? is there any query like database backup?shivakumar...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog