[SQL 2012] AWE on SQL SERVER 2012 and Windows Server 2008 Data Center?

---

• AWE on SQL SERVER 2012 and Windows Server 2008 Data Center?
• Blob data handling - best option?
• SSDT - Apply Different Security per Target Environment

AWE on SQL SERVER 2012 and Windows Server 2008 Data Center? Posted: 20 Mar 2013 02:58 AM PDT We have SQL Server 2012 Enterprise 64-bit installed on a Windows Server 2008 Data Center 64-bit. For some reason AWE is allocating all the unused memory from the OS. It's my understanding that AWE was depricated from 2012, which would make sense since I can't find anywhere to disable it in the SQL config. Is there some other way that AWE would be installed and enabled? Some other applicaion? 2008 is not installed and this is a fresh install with 2012, not an upgrade.Any thought you have would be appreciated.Thanks.

Blob data handling - best option? Posted: 19 Mar 2013 07:31 PM PDT HiI am a programmer and need to do the following.Currently we are using SQL Server 2000 and we are handling lots of resume/cv in .DOC and PDF formats. we use some 3rd party tools to extracts the contents of the file and store it in NTEXT column which is full-text indexed. Now we are planning to go with SQL Server 2012 Standard and i would like to get some advice on this scenario. 1. I dont want to use the text extracter (3rd party tools). Should i store the files as single BLOB and full-text index on it. This is main point as we search these very often to find the best matched candidate for a job requirement. Are is there any other best method to handle this situation? Thanks.

SSDT - Apply Different Security per Target Environment Posted: 19 Mar 2013 09:19 PM PDT HiHopefully the title makes my request obvious but just to elaborate. I am using SQL Server Data Tools for a SQL 2012 database and have set up 3 publish.xml files in order to publish the database to 3 different environments.I want to be able to alter the security that is applied to the DB post deployment depending on the environment I am targetting. Any pointers on how to do this?ThanksBen

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] SQL Refusing to use an index

---

• SQL Refusing to use an index
• T-SQL substring replace function
• Table Join using unique values or where Null = all values
• Help with query using exclusion table
• help with query - am I going down the right path on this
• Incrementing code to add code
• How to read XML file with multiple children and load it in SQL Server
• asp with sql server 2000?
• SP Help
• How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2
• Design of database table and PK update
• event is occurring between today at 1:00am and tomorrow at 12:59am
• Substring with IF or CASE statement
• How is SQL query processed in this example?

SQL Refusing to use an index Posted: 19 Mar 2013 10:14 PM PDT I've just had a designer come to me with a really odd problem. A query, joining two tables on a primary key, example below:SELECT <fields> FROM <table1> INNER JOIN <table2> ON <table1.primarykey> = <table2.indexed field>The query runs optimally, within a couple of seconds .. until .. an additional field is added to the SELECT fields from <table2>.I can see what is happening. Until the additional field is added, all of the fields in the SELECT are available in an index; it is an old index migrated from SQL2000 with the fields as part of the index list and not simply INCLUDEd. Stats show the index is being used and to good use.However the moment the new field from table 2 is added, which is not being covered by the index, rather than the optimizer looking for another qualifying index, it is changing from the non-clustered index to a clustered index scan of table 2 which is processing nearly 2 million rows instead of the small subset it was doing before, and the execution time increases twenty fold.As this is running under a development environment we have tried everything from 1: Creating a similar index with all the fields (a very wide index and not my first choice), but this is ignored.2: Adding the additional fields to the existing index in the INCLUDE - also ignored.3: Dropped the previously working index - This did cause it to revert back to a better existing index - but obviously any other queries benefiting from it would now degrade, and the moment we recreated the index it was back to it's primary key scan.I've cleared the cache plans, updated statistics, rebuilt the indexes, everything I can think of.It will work if we put an index hint in place and tell it the index we think it should be using, and the performance is back to a couple of seconds. I know hints are the last resort, but I can't think of anything else I can investigate before we choose this as the final solution.Any advice would be greatly appreciated. :-D

T-SQL substring replace function Posted: 19 Mar 2013 11:59 AM PDT Hello, I have some data and need to extract the first 9 numbers from this pattern across multiple rows of data. I would think that using something like this:substring(value_expression, ,1,9)might work but am unaware of how to find the first 9 digits in this sequence:01J416781|1|2013|2|I am looking to find '01J416781', each row of data starts with this pattern. I would greatly appreciate any help.

Table Join using unique values or where Null = all values Posted: 20 Mar 2013 12:35 AM PDT Hi folks,I have a complex query that involves many tables but I am struggling with one particular join (TableA and TableB). There is only one field in each table where a join can be made. TableA.Area contains values that can be matched with TableB. However TableA may also contain a Null value in the joining column. If there is a Null value, then TableA.Area needs to join with [b]all[/b] values in TableB.ColId. [code="sql"]USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableA]( [Id] [tinyint] IDENTITY(1,1) NOT NULL, [Area] [nvarchar](10) NULL, [Item] [nvarchar](10) NOT NULL, [Description] [nvarchar](50) NOT NULL, CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A1', 'B1232', 'Thingymebobs');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A4', 'B4352', 'Whatits');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES(Null, 'B8769', 'Dunno');USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableB]( [ColId] [nvarchar](10) NOT NULL, [DeliveryName] [nvarchar](60) NOT NULL, CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ( [ColId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A1', 'Acme1');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A4', 'Acme4');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A8', 'Acme8');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A9', 'Acme9');[/code]The columns to be joined are TableA.Area AND TableB.ColId[code="sql"]SELECT b.DeliveryName, a.Item, a.[Description]FROM TableA a JOIN Tableb b ON --????;[/code]Any ideas please?Thanks in advance,

Help with query using exclusion table Posted: 20 Mar 2013 12:38 AM PDT I have a metrics database and a few reports running from it.One of them is to report on databases that haven't had a full backup run in the last week...It checks other tables for Production and Online dbs etc etc...My query looks something like:[code="sql"]SELECT bh.Server AS Server ,bh.database_name ,MAX(bh.backup_finish_date) AS last_db_backup_date ,datediff(dd,MAX(bh.backup_finish_date),Getdate()) AS Age ,bh.backup_type FROM Backup_History bh INNER JOIN ServerList_SSIS sl ON bh.Server = sl.ServerDisplayName AND sl.Environment = 'PROD' AND sl.BackupReports = 'Y' INNER JOIN Database_Info dbi ON bh.Server = dbi.Server AND bh.database_name = dbi.DatabaseName AND dbi.Status <> 'OFFLINE' WHERE bh.backup_type = 'Database' GROUP BY bh.Server ,bh.database_name ,bh.backup_type HAVING (MAX(bh.backup_finish_date) < DATEADD(dd, - 8, GETDATE())) ORDER BY Age DESC ,bh.Server ,bh.database_name[/code]The output right now is:Server database_name last_db_backup_date Age backup_typeSERVER1 DB1 2013-03-09 18:32:50.000 11 DatabaseSERVER1 DB2 2013-03-09 13:03:16.000 11 DatabaseSERVER1 DB1 2013-03-09 14:31:14.000 11 DatabaseSERVER2 DB2 2013-03-09 12:56:19.000 11 DatabaseSERVER2 DB3 2013-03-09 13:30:17.000 11 Database(Sorry about the formatting)Now I need to exclude the DB1 database so I created an exclusion table with Server and DB columns and added that server and database.I need help with the query to exclude that one from the report:I've tried:[code="sql"]AND (bh.Server NOT IN(SELECT SERVER from ReportExclusionList)AND bh.database_name NOT IN(SELECT DatabaseName FROM ReportExclusionList))[/code]After the WHERE clause but it excludes all databases from the specified server...I Just can't wrap my head around the sql... (Maybe change the NOT IN portion to include the database and server...)Any help would be appreciated.Thanks

help with query - am I going down the right path on this Posted: 19 Mar 2013 11:20 PM PDT I'm working on a query that will be comparing columns in tables in 2 seperate databases. If the where clause is met, I need to create a log file and then email it at the end of the procedure.here is my query:SELECT db1.*, db2.*FROM db1..ORGANIZATION db1inner join db2..ORGANIZATION db2ON db1.ORG_ID = db2.ORG_IDwhere db1.TITLE <> db2.TITLEordb1.CODE <> db2.CODEordb1.ACTIVE <> db2.ACTIVEif any of the criteria in the where clause is met, I need to create a log file and email it. I'd like to this is using SSIS, however, how can I have a return value of [true] returned in the where statement if something meant, plus the values?so for exampleif db1.organization <> db2.orgranization, I want the values from both databases, return true so I can create a log file, like I mentioned I'd like to do this using SSIS, but if I can't I'll be doing this in a stored procedure that will be kicked off nightlyAm I even going down the right direction with comparing values in the tables?

Incrementing code to add code Posted: 19 Mar 2013 05:25 PM PDT Hi,I am trying to join the table to itself many times on a condition. Is there a way to automate number of joins based on count of something from another table?Any help appreciable!Thanks,Akber.

How to read XML file with multiple children and load it in SQL Server Posted: 19 Mar 2013 08:31 PM PDT I have an XML file in the below given format. How can I import it into SQL Server using OPENXML or some other method ?----------------------------------------------------------------------------------------------------------------------<?xml version="1.0" encoding="utf-8"?><SRCL> <list> <AOSRC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SRC> <SRCNm> <string>Orders.xml_</string> </SRCNm> <SRCHsh> <string>KA-ORDRS-21-Jan-2013</string> </SRCHsh> <SRCRslts> <XSDOS /> </SRCRslts> </SRC> <SRC> <SRCNm> <string>OrderDetails.bin_</string> </SRCNm> <SRCHsh> <string>KJ-18-Mar-2013-WEGraded</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WarehouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jul-12 8:24:35 PM</string> </value> <weight> <int>2</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Closed and Roofed</string> </name> <value> <string>Type A</string> </value> <weight> <int>143</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>CandF_Section</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>PEandPaperCorrugated</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , With godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> <SRC> <SRCNm> <string>PendingOrders.bin_</string> </SRCNm> <SRCHsh> <string>KA-UE-04-Feb-2013</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WearhouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jan-13 8:10:15 PM</string> </value> <weight> <int>2</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Warehouse Type</string> </name> <value> <string>v8.0</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Forwarding Sections</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>ElectricalCarriage</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , Without godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>QuantityTransfer</string> </FieldNm> <value> <SRlt> <name> <string>QuantityTransfer</string> </name> <value> <string>yes</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>StoreID</string> </FieldNm> <value> <SRlt> <name> <string>StoreID</string> </name> <value> <string>Footware</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Sodexo</string> </FieldNm> <value> <SRlt> <name> <string>Sodexo</string> </name> <value> <string>Sodexo</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>AC Head</string> </FieldNm> <value> <SRlt> <name> <string>AC Head</string> </name> <value> <string>1</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> </AOSRC> </list></SRCL>

asp with sql server 2000? Posted: 19 Mar 2013 04:42 PM PDT hai friends, I m currently using sql server 2000 DB for my asp applicationmy table iscreate table ttr(startdate datetime,from_loctn varchar(100),to_location varchar(100),Modeoftravel nvarchar(100),seattype nvarchar(100))Modeoftravel and seattype is dropdownbox. if im choose it ll store value only not name(ex:insert into ttr('20-03-2013','abc','dbn','train','IIAC')but showing my table as modeoftravel value is 1 seattype value 1 how to modify

SP Help Posted: 19 Mar 2013 11:41 AM PDT Hello

How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2 Posted: 19 Mar 2013 09:34 AM PDT Hi, Need help on get month number from Date field, I am getting month number as 1 for Jan, 2 for FebBut I need 01 for Jan, 02 for Feb..like thisCan you please some one help me outThanksGrace

Design of database table and PK update Posted: 19 Mar 2013 11:05 AM PDT We are creating an address database to maintain student address info.I have two tables like this :The address table is a master table that holds all addresses. The studHomeAddress table will hold all students and their address- the addressID comes from the master address table. The primary key of the Address table is Address ID, the PK for studHomeAddress table is studentID and AddressID, we are going to have a screen for user to add or edit for each of the two tables.They can add and edit records, for both of the tables.For the first table Address, I think it is no problem. For the second table, if we want to change the address of the student to another one, then the AddressID will be changed for this record, but it is the PK, the PK for the row will be changed. Is that an OK design?Thanks,

event is occurring between today at 1:00am and tomorrow at 12:59am Posted: 19 Mar 2013 08:57 AM PDT Hello,I need a query that finds event is occurring between today at 1:00 am and tomorrow at 12:59 am. I have 2 columns.event_date datatype =date and event_time datatype= timeThis query gives me,SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(day, 0, GETDATE()), 102))--2013-03-19 00:00:00.000 but instead of 0.00 I need my time whihc is 1:00 AM and 12:59 AM of next day.

Substring with IF or CASE statement Posted: 19 Mar 2013 02:15 AM PDT Hi,I am trying to import into SQL Server 2008 R2 a list of strings as follows:=KET+N.207-13-F4001=KET+DG014-13-F4011=KET+RE002-36-MV009I need to split the strings so they are inserted into separate fields. One of the substring statements is:'SUBSTRING(xlCode; 15; 2) [if the second position is a number, and]'SUBSTRING(xlCode; 15; 1) [if the second position is anything else]AS GroupNoMy experience with T-SQL is just not enough to figure this one out. Can you tell me how the statement should be set up?Thanks in advance for any help.Maarten

How is SQL query processed in this example? Posted: 19 Mar 2013 04:04 AM PDT Hi,I have already posted the problem on Stackoverflow. Can you please help?[url=http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example]http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example[/url]Thanks much in advance!Regards,Nayan

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] best optimized query for the requirement

---

• best optimized query for the requirement
• Strange issue with one of the four Sharepoint databases that we Log ship to our DR site.
• Comparision of tables in two instances
• System.Data.SqlClient.SqlException (0x80131904): Timeout Expired
• BULK INSERT Different Data Types in the SAME Columns
• Check Data While Inserting
• Need help to read text file
• Not able to find historical data using SQL Server Profiler in SQL Server 2008
• Data not inserting
• Real time live stats
• SQL Script Help - Need to capture a single transaction from many possibilities
• SSMS User Interface Tip to Freeze Panes.
• table naming conventions
• sys.dm_io_virtual_file_stats - Number of Read and Writes
• Transactional replication problem
• Doubt about Shrinking process , Rebiulding index and fragmenatation
• Update YTD number with the sum of MTD numbers
• Substring with IF or CASE statement
• Can't get rid of Key Lookup in execution plan
• Problem having restoring database backup file on secondary server
• how to make a stored procedure return a value when there are no available values
• divide the percentage equally for every user- sql server stored procedure
• Bulk Insert via VBA - Operating system error 5
• SQL to divide one row by another row based on formula or rule.
• case statement in count?
• Combining Two Very Large Tables
• cache plan
• SSAS Count and not SUM
• SP execution time.

best optimized query for the requirement Posted: 19 Mar 2013 06:12 PM PDT I have the below query and need the best query to optimize the requirementSELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=1 AND product_code in (1)) AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=2 AND product_code in (3))AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=3 AND product_code in (2))AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=4 AND product_code in (1)))))

Strange issue with one of the four Sharepoint databases that we Log ship to our DR site. Posted: 18 Mar 2013 09:13 PM PDT On the server that hosts our Sharepoint databases we are log shipping 4 of them to a sql server instance at our DR site. The issue we are seeing is that for one of the 4 databases the first transaction log generated by the Log Shipping scheduled task after the full backup has been taken is the same size as the database itself (2.4Gb). Following this all subsquent files genrated are much smaller (200 Mb).I have checked for open / long running transactions but none are reported.Out of the 4 Sharepoint databases the one with the issue is the only one that has any Sharepoint Workflows configured. However they are configured to run in response to actions rather than at a certain time of day.Both Servers are SQL Server 2008 SP3 Standard 64 bit.Has anyone got any thoughts on what might be causing this behavoiur.

Comparision of tables in two instances Posted: 19 Mar 2013 07:12 PM PDT Am having two instances A and B, in that having a table called students in two databasess, but the records are not in match,i want to retrieve records which are having in "A" database and not having in "B" database.Instance : ADatabase : StudentsTable : Stud_InfoInstance : BDatabase : StudTable : Stud_Info.

System.Data.SqlClient.SqlException (0x80131904): Timeout Expired Posted: 19 Mar 2013 11:49 AM PDT I apologize if this question has been asked before, but we're really stumped and the answers I've seen posted don't really apply to our situation. We're getting the following error sporadically in a system that's being run by SQL 2008 R2 and connects to other databases on remote computers using the same version of SQL. The error message may be lengthy, but perhaps one of you will see something we don't: Error in GetScheduleByPFTID Type PAST:System.Data.SqlClient.SqlException (0x80131904): Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior.runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Dta.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataset, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Scheduling.GetScheduleByPFTID(String pType, String pReason, Int32 pPFTID, Int32 pLastTxtNo, String pRSWChartNumber)

BULK INSERT Different Data Types in the SAME Columns Posted: 19 Mar 2013 07:25 AM PDT Issue: I have a small csv file uploaded through a web page by an end-user. It has two INT columns. Sometimes, the user accidentally uploads the column headers - they are varchar. I am using BULK INSERT to get the rows into a temporary table. The bulk insert works fine when the column headers are NOT there. It silently fails when the column headers are there. I specifically changed the table definition to use NVARCHAR's for the input when I discovered that the end-users were uploading the column headers.[code="sql"]SET @Command = N'CREATE TABLE ##temp_123 ([Staging_ID] NVARCHAR(512), [LeadID] NVARCHAR(512))'EXECUTE sp_ExecuteSQL @CommandSET @Command = N'BULK INSERT ##temp_123 FROM ''' + @InputFileNameWithPath + N''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = '''')'EXECUTE sp_ExecuteSQL @Command[/code]I can see the temp table. When the input file has column headers, there is one row with both columns NULL. When there are no column headers, there are as many rows as in the user's file and they all have values.I'm stumped. Thanks in advance.- Seth

Check Data While Inserting Posted: 19 Mar 2013 06:24 PM PDT Dear All,I have a UI like,LineNo ProdID Discount(%) and a "Insert" Button-------- ------ --------I can insert a single or multiple lines in this UI.While inserting the following data, it will check whether there is any data in Discount Column greater than 18. If so, it will not insert any data and raise an error message.[code="plain"]LineNo ProdID Discount(%)------ ------ -------1 1000 02 1001 23 1002 194 1003 0[/code]I coded but it always checks LineNo1. Please help me to code whether I can check data that is greater than 18 in Discount Column against all the LineNo .Regards,Akbar

Need help to read text file Posted: 18 Mar 2013 11:49 PM PDT Hi,My text file as attachment ~ AG-Persekutuan-Bln-09-2012.txtHow to read this file as follows,1. Taxpayer data starting in the second row2. The 1st TaxPayer read as follows,[code="sql"]SUBSTRING('02029105500000326272560816105987 Samat Bin Sani 0000015004363 ',21,6) + '-' + SUBSTRING('02029105500000326272560816105987 Samat Bin Sani 0000015004363 ',27,2) +'-' + SUBSTRING('02029105500000326272560816105987 Samat Bin Sani 0000015004363 ',29,4) as newIdx,SUBSTRING('02029105500000326272560816105987 Samat Bin Sani 0000015004363 ',52,40) as myName[/code]3. The 2nd TaxPayer read as follows,[code="sql"]SUBSTRING('02011105700000488437551130055335 Jalaludin Bin Jalil 0000025004363 ',21,6) + '-' + SUBSTRING('02011105700000488437551130055335 Jalaludin Bin Jalil 0000025004363 ',27,2) +'-' + SUBSTRING('02011105700000488437551130055335 Jalaludin Bin Jalil 0000025004363 ',29,4) as newIdx,SUBSTRING('02011105700000488437551130055335 Jalaludin Bin Jalil 0000025004363 ',52,40) as myName[/code]4. And the next reading as like above5. After all the content is read, the result as follow[code="sql"]560816-10-5987 SAMAT BIN SANI 551130-05-5335 JALALUDIN BIN JALIL 590826-05-5028 MUKARRAMAH BT IBRAHIM 550317-05-5242 FATIMAH BT ISMAIL 540301-04-5163 ABU MANSOR BIN HJ MOHD NOR [/code]It's possible SQL Server 2008 R2 read text file? If yes, I really need help. Please help me. I'm stuck

Not able to find historical data using SQL Server Profiler in SQL Server 2008 Posted: 19 Mar 2013 05:10 PM PDT Hello All,I would like to capture the script that was ran by user on yesterday between particular time.But somehow i am not able to get the desire information.Can anyone please help me if i am missing anything.Template: Standard (No option with Default)LoginName: Name of userStartTime : Greater than > 2013-03-18 06:00:00.000EndTime : Less than < 2013-03-18 06:30:00.000.Please let me know if more information is required.

Data not inserting Posted: 16 Mar 2013 12:20 AM PDT Hi i have written a query to check the insertion but its not working neither getting any error :Declare @valuebased varchar(13)='0000000000'Declare @valueDiscount varchar(13)='0000000000'Declare @ProductDiscount varchar(13)='0000000000'Declare @IsBooklet BITDeclare @vouchertypeid int, @voucherno varchar(13), @denomination int, @createddate datetime, @expirydate datetime, @voucherstatus int, @quantity int, @isbookletid bitIf @IsBooklet = 1BEGIN DECLARE @count int SET @count =1WHILE (@count < 10) BEGIN SET @voucherno = 'VVB'+CONVERT(varchar(10),MAX(@valuebased)+1) INSERT INTO GV_Voucher (VoucherTypeId,VoucherNo, Denomination, CreatedDate, ExpiryDate, VoucherStatusId, TransactionID, Quantity, IsBooklet) Values (1,@voucherno,10,GETDATE(),GETDATE(),1,'a1',12,0) SET @valuebased = @valuebased + 1 SET @count = @count +1 ENDEND

Real time live stats Posted: 19 Mar 2013 03:55 PM PDT Icecube have now upgraded our statistics on live streaming. Now you can even track how many people are viewing your live broadcast real time. You can among other things see how many at any one time, how many total. how long they see, where they are from and more Please contact us for a test

SQL Script Help - Need to capture a single transaction from many possibilities Posted: 19 Mar 2013 01:44 PM PDT Here is the DLL[code="sql"]IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XX_TEMP_AR_INSERT]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[XX_TEMP_AR_INSERT]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] [varchar](32) NULL , [PAYER_ID] [varchar](15) NULL , [AMOUNT] [decimal](12 , 2) NULL , [BILLING_SITE_ID] [varchar](15) NULL , [TRANSFER_ID] [varchar](32) NULL )ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( 'DA6773113801A85B803C0F3FAC1D1A8D' , '0466' , '-100.78' , '400' , '348C701138017AAD2203767EAC1B1238' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( 'FA6773113801D9A6833C0F24AC1D1A8D' , 'SELF' , '100.78' , '400 ' , '348C701138017AAD2203767EAC1B1238' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '8411E299A994420681210E3AB07E8ABF' , 'SELF' , '-100.78' , '400' , '2F525D70E0B74634A82E26983E3EBCA9' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '42FAE87AC9774132ADDA13570402AC1C' , 'SELF' , '100.78' , '900' , '2F525D70E0B74634A82E26983E3EBCA9' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '59BBC4473A013DCEAD3009B4AC1D1A8C' , '0466' , '-400.49' , '400' , '8620BF473A01AFE3BE03AF49AC1B1339' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '88BBC4473A012F73B0300949AC1D1A8C' , 'SELF' , '400.49' , '400' , '8620BF473A01AFE3BE03AF49AC1B1339' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( 'B84A37A6E93B4882B6289C016EC47868' , 'SELF' , '-400.49' , '400' , '9EEFA7DEE2CA4318B7B140148E111293' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( 'E49651E5715046269AF023332CB50C40' , 'SELF' , '400.49' , '900' , '9EEFA7DEE2CA4318B7B140148E111293' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '758237E78D5A4458A4DD6B2BAD3F602D' , 'SELF' , '501.27' , '901' , '28F880EDC0D148E9B2C3393161CA509A' )INSERT INTO [dbo].[XX_TEMP_AR_INSERT] ( [SYS_ID] , [PAYER_ID] , [AMOUNT] , [BILLING_SITE_ID] , [TRANSFER_ID] ) VALUES ( '916E1F1F238E4EA48D1C31525C846372' , 'SELF' , '-501.27' , '900' , '28F880EDC0D148E9B2C3393161CA509A' )[/code]Here is query that I'm working with[code="sql"]SELECT [JArd1].[PAYER_ID] AS TRANSFER_FROM , [JArd1].[AMOUNT] , [JArd1].[SYS_ID] , [JArd2].[PAYER_ID] AS TRANSFER_TO , [JArd2].[SYS_ID] , [JArd2].[AMOUNT] FROM [dbo].[XX_TEMP_AR_INSERT] AS Ard JOIN ( SELECT [Ard2].[SYS_ID] , [Ard2].[PAYER_ID] , [Ard2].[TRANSFER_ID] , [Ard2].[AMOUNT] FROM [dbo].[XX_TEMP_AR_INSERT] AS Ard2 WHERE [Ard2].[AMOUNT] < 0.00 ) JArd1 ON [Ard].[SYS_ID] = [JArd1].[SYS_ID] JOIN ( SELECT [Ard3].[SYS_ID] , [Ard3].[PAYER_ID] , [Ard3].[TRANSFER_ID] , [Ard3].[AMOUNT] FROM [dbo].[XX_TEMP_AR_INSERT] AS Ard3 WHERE [Ard3].[AMOUNT] > 0.00 ) JArd2 ON [JArd1].[TRANSFER_ID] = [JArd2].[TRANSFER_ID] WHERE [JArd1].[PAYER_ID] <> [JArd2].[PAYER_ID][/code]What I'm trying to capture is the last transaction to occur in XX_TEMP_AR_INSERT where the balance goes from payer "X" to payer "Y" and payer "X" <> payer "Y". When you run SELECT * FROM XX_TEMP_AR_INSERT, I want the query above to capture data from rows 5 & 6 only. Ignore rows 1 & 2 because they are the first occurence of the a transfer. These are the results that I want the query to return:0466 -400.49 59BBC4473A013DCEAD3009B4AC1D1A8C SELF 88BBC4473A012F73B0300949AC1D1A8C 400.49

SSMS User Interface Tip to Freeze Panes. Posted: 18 Mar 2013 08:39 PM PDT Accidentally discovered this, and thought I should share it, as I don't think I've seen it mentioned before.In Management Studio, in a sql window, if you click on the little rectangular block at the top right of the window (below the X to close), and pull down, the current window splits into panes, allowing you to look at 2 parts of the same procedure simultaneously.I don't expect it will change your life, but you never know!

table naming conventions Posted: 19 Mar 2013 09:38 AM PDT we are creating a new database maintaining address and in the future may have more into the databases.I have a question about the nameing convention for database tables.We used to have a standard to give a prefix of 3 letters for a table to group them together functionally, like schSchoolProgram, schAssignment, appProcess, secUsers.Is this a good way to name tables, I found sometimes it is hard to mention the table, I have to spell out the first 3 characters when I talk to someone about the tables.For the new database, when creating tables, shall I just use the plain names, like Address instead of addrAddress, StudentHomeAddress instead of addrStudentHomeAddress?Also for domain tables shall I just: City, County instead of domCity, domCounty?Thanks

sys.dm_io_virtual_file_stats - Number of Read and Writes Posted: 19 Mar 2013 03:57 AM PDT I looked up http://msdn.microsoft.com/en-us/library/ms190326.aspx for details on sys.dm_io_virtual_file_stats table.and see following descriptions:num_of_reads - Number of reads issued on the file.num_of_writes -Number of writes made on this file.Is it ok to assume that number of reads translate into number of select queries? andnumber of writes translate into number of insert queries?thanks

Transactional replication problem Posted: 19 Mar 2013 07:49 AM PDT Hi everyone,We encountered some strange error when we were trying to add a subscriber.Following steps were performed:1. Publsher and distributor (2 different machines) were created2. Publication was created3. Few (12) subscribers were successfully added to the publicationWhen some time after everything is successfully running we tried to add 2 or more subscribers and got an error:MessageThe replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.Checking replication monitor it sends us to job and job sends us to replication monitor. Removing newly added subscribers make everything looks good again.Any ideas if we need to check some settings?Thanks.

Doubt about Shrinking process , Rebiulding index and fragmenatation Posted: 19 Mar 2013 06:25 AM PDT hi i know that shrinking data base increase fragmentation , so logically shrinking data files would two result in fragmentation but does shrinking log file increases fragmentation. i hope answer is NO.Second question is rebuilding an index reduces fragmentation but it increase size of database.I have seen increase in size of log file but does data file size to increases . Again i hope ans is No.Plz correct me on this

Update YTD number with the sum of MTD numbers Posted: 19 Mar 2013 06:57 AM PDT I have a table with budget data. The month to date number has been loaded. I need to update the YTD value. So, the October YTD number would be the same as the MTD. I got that already. Tricky part is......... November's YTD has to be October's YTD plus November's MTD. December's YTD has to be November's YTD plus December's MTD and so on.Any thoughts?

Substring with IF or CASE statement Posted: 19 Mar 2013 06:02 AM PDT Hi,I am trying to import into SQL Server 2008 R2 a list of strings as follows:=KET+N.207-13-F4001=KET+DG014-13-F4011=KET+RE002-36-MV009I need to split the strings so they are inserted into separate fields. One of the substring statements is:'SUBSTRING(xlCode; 15; 2) [if the second position is a number, and]'SUBSTRING(xlCode; 15; 1) [if the second position is anything else]AS GroupNoMy experience with T-SQL is just not enough to figure this one out. Can you tell me how the statement should be set up?Thanks in advance for any help.Maarten

Can't get rid of Key Lookup in execution plan Posted: 19 Mar 2013 04:42 AM PDT I have a query that has a very costly Key Lookup step in its execution plan. It looks like this:[code="sql"]SELECT B.BenefitID, PW.WaiverReason, EW.DateWaived, EW.DateEntered, CBA.OrderID FROM EmployeeWaivers EW JOIN CompanyBenefitAssoc CBA ON CBA.BenefitID = EW.BenefitID AND CBA.CompanyID = YYYY LEFT JOIN PlanWaivers PW ON PW.WaiverID = EW.WaiverID LEFT JOIN Benefits B ON B.BenefitID = EW.BenefitID WHERE EW.EmployeeID = XXXX AND EW.Overwritten = 0 ORDER BY CBA.OrderID, PW.WaiverReason, EW.DateEntered[/code]The Key Lookup is on the EmployeeWaivers table. I created the following covering index:[code="sql"]CREATE NONCLUSTERED INDEX [IX_EmployeeWaivers_BenefitID_WaiverID_EmployeeID_Overwritten_incl_DateWaived_DateEntered] ON [dbo].[EmployeeWaivers] ( [BenefitID] ASC, [WaiverID] ASC, [EmployeeID] ASC, [Overwritten] ASC)INCLUDE ( [DateWaived],[DateEntered])[/code]I still have the Key Lookup as part of the execution plan. What did I miss?

Problem having restoring database backup file on secondary server Posted: 19 Mar 2013 06:41 AM PDT Hi for last two days at one of our client side we have been trying figure out a why its taking two days two restore database backup files on secondary server. restore process is till going on and is stuck at 98% and no other process are there on secondary server.using sp_who2 i could find only two lastwaittypes as BACKUPTHREAD and BACKUPIO. how should i proceed ahead with analysis . i mean does disk has some bottleneck problem ?/the database of which we are trying to restore has file group.does it had to anything with filegropu

how to make a stored procedure return a value when there are no available values Posted: 19 Mar 2013 01:04 AM PDT Here is the script for a stored procedure that i have, i want the stored procedure to return a value of 1,if it is a null or if there are no values for it to return ,Create PROCEDURE [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd] @BatchStatusCd NVARCHAR(5) ,@ExtractSiteCd NVARCHAR (10) ,@ExtractAsOfDate DATETIME AS SELECT case when @ExtractAsOfDate = isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01') then 0 when @ExtractAsOfDate <> isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01')then 1 else 1 end as ExtractSiteExtractFileBatchPreviousSuccessInd FROM ExtractSiteExtractFileBatch WHERE BatchStatusCd = @BatchStatusCd and ExtractSiteCd =@ExtractSiteCd AND ExtractAsOfDate = @ExtractAsOfDategohere is the exec statement for the stored procedure, i want it to return a value of 1, if the value does not exist in the sample data.EXEC @return_value = [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd] @BatchStatusCd = N'sb', @ExtractSiteCd = N'CEPOK', @ExtractAsOfDate = N'2013-03-18 00:00:00.000'SELECT 'Return Value' = @return_valueGObelow is the sample dataSELECT '[ExtractSiteExtractFileBatchID]','[ExtractSiteCd]','[ExtractAsOfDate]','[BatchStatusCd]','[InsertedDate]','[UpdatedDate]' union all select '1442','CEPOK','2012-07-12 00:00:00.000','SB','2012-07-13 06:04:41.843','2012-07-13 07:30:29.660' union all select '1443','CFTRT','2012-07-12 00:00:00.000','SB','2012-07-13 07:30:30.077','2012-07-13 08:29:40.433' union all select '5472','CEPED','2012-07-12 00:00:00.000','SBR','2013-03-18 17:34:12.047','2013-03-18 17:34:12.047'

divide the percentage equally for every user- sql server stored procedure Posted: 19 Mar 2013 03:55 AM PDT I have written a stored procedure. as of now it inserts 10 percent for every user as below. orderid processid uid ordervalue perwet(percent wieghtage)1 1 1 10000 101 1 2 10000 101 1 3 10000 10I want that if more than 1 users are involved in 1 process in same order it should divide the percent equallythat means it must insert like thisorderid processid uid ordervalue perwet(percent weightage)1 1 1 10000 3.33 1 1 2 10000 3.331 1 3 10000 3.33structure of tables CREATE TABLE [dbo].[temp_calpoints1]([orderid] [int] NULL,[processid] [int] NULL,[uid] [int] NULL,[ordervalue] [bigint] NULL,[perwet] [int] NULL) ON [PRIMARY]CREATE TABLE [dbo].[process]([processid] [int] NULL,[processdesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[perwet] [int] NULL) ON [PRIMARY]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[CalculatePointsAllorder]@fromDate varchar(10), @toDate varchar(10)ASBEGINprint @fromdate;print @todate; delete from temp_calpoints1; delete from temp_users;delete from temp_OrderMaster;insert into temp_usersSELECT uid FROM UserInfo where exists (select * fromOrderMaster where UserInfo.uid = OrderMaster.uid);insert into temp_OrderMasterselect * from OrderMaster where orderlogindate between @fromDate and @toDate DECLARE @t_orderid intDECLARE @t_processid intDECLARE @t_uid intDECLARE @t_ordervalue bigintDECLARE db_cursor CURSOR FOR SELECT orderid FROM temp_OrderMaster;--select orderid from OrderMaster where CONVERT(VARCHAR(10),orderlogindate,110) between --@fromDate and @toDate;--where orderlogindate --between @fromDate and @toDate;DECLARE db_cursor1 CURSOR FOR SELECT processid FROM process;--DECLARE db_cursor2 CURSOR FOR select uid from temp_users;DECLARE db_cursor2 CURSOR FOR select uid from userinfo;OPEN db_cursor FETCH NEXT FROM db_cursor INTO @t_orderid WHILE @@FETCH_STATUS = 0 BEGIN --print 'order ' -- PRINT @t_orderid;OPEN db_cursor1 FETCH NEXT FROM db_cursor1 INTO @t_processid WHILE @@FETCH_STATUS = 0 BEGIN --print 'process*******' --PRINT @t_processid;OPEN db_cursor2 FETCH NEXT FROM db_cursor2 INTO @t_uid WHILE @@FETCH_STATUS = 0 BEGIN--print '***'--print 'user' --print @t_uid--print @t_processid --print '***'if @t_processid = 1begin--print 'in processid ' --print @t_uid--print @t_processid --print '***'set @t_ordervalue = 0;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue-- @t_uid = b.uid from temp_OrderMaster a,EnquiryMaster b wherea.EnquiryId = b.enquiryid and b.uid = @t_uid and a.orderid = @t_orderid --)if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endif @t_processid = 2beginset @t_ordervalue = 0;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue --@t_uid = b.uid from temp_OrderMaster a,Requirement b wherea.requirementid = b.RequirementID and b.uid = @t_uid and a.orderid = @t_orderid --)if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endif @t_processid = 3beginset @t_ordervalue = 0;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue --,@t_uid = b.uid from temp_OrderMaster a,Proposal b wherea.proposalid = b.proposalid and b.uid = @t_uid and a.orderid = @t_orderid --)if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endif @t_processid = 4beginset @t_ordervalue = 0;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue --,@t_uid = uid from temp_OrderMaster whereorderid = @t_orderid and uid = @t_uid--)if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endif @t_processid = 5beginset @t_ordervalue = 0;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue --,@t_uid = b.uid from temp_OrderMaster a,OrderVendor b where b.orderid = @t_orderid and b.uid = @t_uid--)if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endif @t_processid = 6beginset @t_ordervalue = 0;--set @t_uid = 0;print 'in processid 6 'print @t_uid;PRINT @t_orderid;--insert into temp_calpoints1 (ordervalue) values(select @t_ordervalue = ordervalue -- ,@t_uid = b.uid from temp_OrderMaster a,CollectionFollowUp b where b.orderid = @t_orderid and b.uid = @t_uid--)print @t_ordervalue ;if @t_ordervalue <> 0 insert into temp_calpoints1 (orderid,processid,uid,ordervalue) values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)endFETCH NEXT FROM db_cursor2 INTO @t_uidENDCLOSE db_cursor2FETCH NEXT FROM db_cursor1 INTO @t_processidENDCLOSE db_cursor1 FETCH NEXT FROM db_cursor INTO @t_orderid END CLOSE db_cursor update temp_calpoints1 set perwet = (select perwetfrom process where processid=temp_calpoints1.processid) END--update query set on count of group by claue from another table in sql--select count(*) from temp_calpoints1 group by processid,orderid--EXEC [CalculatePointsAllorder] @fromDate = '2012-09-10' ,@toDate = '2013-11-13'--select * from temp_calpoints1 order by orderid,processid,uid--select * from temp_OrderMaster

Bulk Insert via VBA - Operating system error 5 Posted: 19 Mar 2013 03:58 AM PDT Hi,I hope this is the correct location for my question.I have written a simple piece of VBA to convert some data and save as a csv file which is then to be bulk instered into a database.I get the error 'Cannot bulk load because file "filename" could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).'When I copy the SQL string into SQL Studio is completes correctly and I have fully named the file and location or it. I have seen elsewhere on the forum similar issues which people put down to permissions, given the studio can complete the code without trouble that seems odd to me.Any ideas?

SQL to divide one row by another row based on formula or rule. Posted: 19 Mar 2013 03:35 AM PDT Hi All,I have a below table and need to divide one row by another row based on the formula.Date Name ID Result3/18/2013 A 1 53/18/2013 B 2 163/18/2013 C 3 213/18/2013 D 4 113/18/2013 E 5 453/18/2013 F 6 223/18/2013 G 7 53/19/2013 A 1 23/19/2013 B 2 73/19/2013 C 3 153/19/2013 D 4 93/19/2013 E 5 193/19/2013 F 6 123/19/2013 G 7 3Please suggest how to proceed to get below result. Formula Column is used to get Result(%) column.Formula Date Result(%)B/A 3/19/2013 0.285714286D/C 3/19/2013 0.6F/(E-G) 3/19/2013 0.75B/A 3/18/2013 0.3125D/C 3/19/2013 0.523809524F/(E-G) 3/19/2013 0.55Thanks in advance.

case statement in count? Posted: 19 Mar 2013 03:55 AM PDT Hi trying to do the followingSELECT Date, Year, CASE DATEPART(mm, date) WHEN 07 THEN 6 END AS JulyMaxhours, KindOfDay, Month,[b]COUNT(CASE DATEPART(mm, date) WHEN 07 )[/b]FROM Auxiliary.CalendarWHERE (KindOfDay = 'Weekday') AND (Year = 2013) AND (Month = 7)What I want in the case is the count of days = 07 even better is if I can multiply that count by 6 ThanksJoe

Combining Two Very Large Tables Posted: 19 Mar 2013 02:54 AM PDT I have two tables that each contain approximately 90 million records. The second table represents an "update" of the second table; however, no unique identifier exists within either population to identify which records in the new table are added or changed from the previous table. Each table contains the following fields:1. First Name2. Middle Name3. Last Name4. Social Security Number5. Birth Date7. ID (unique to the table, but not across tables)The SSN is unique in both tables; however, an SSN may be revised for a distinct record from one table to the next based on updated information, etc. that is received. We currently have the following indexes assigned to each table:1. Social Security Number (Clustered Index)2. First Name, Middle Name, Last Name, Birth Date, ID (Non-Clustered Indexes)I am trying to identify the most efficient way to:1. Combine the tables, and;2. De-duplicate records within the combined table based on a combination of different fields (e.g., same SSN; same first name, last name, birth date; etc.)We've accomplished #1 in the past by partitioning each table into chunks based on SSN and then unioning those chunks. For example: SELECT Social_Security_Number, Last_Name, First_Name, Middle_Name, Death_Date, Birth_DateINTO ##tbl_20110901_FullFile_00_04 FROM tbl_20110901_FullFileWHERE LEFT(Social_Security_Number,2) IN('00','01','02','03','04')SELECT Social_Security_Number, Last_Name, First_Name, Middle_Name, Death_Date, Birth_DateINTO ##tbl_20120301_FullFile_00_04 FROM tbl_20120301_FullFileWHERE LEFT(Social_Security_Number,2) IN('00','01','02','03','04')SELECT * INTO ##tbl_Combined_00_04 FROM ##tbl_20110901_FullFile_00_04 UNION SELECT * FROM ##tbl_20120301_FullFile_00_04Is there a better way to accomplish this?Thanks,James

cache plan Posted: 19 Mar 2013 01:39 AM PDT what is query cache plan , list the dmv's to trouble shoot poor performance of a query in sql 2005/2008?

SSAS Count and not SUM Posted: 19 Mar 2013 03:49 AM PDT HiHopefully this will be an easy one for the SSAS heads out there.I am new to cubs, i have set up some dimention and measures and i am browsing my cube nicely.problem is it is summing the values and i would like a count instead. I can see for the life of me where you change this to a count.Thanks in advance

SP execution time. Posted: 19 Mar 2013 02:14 AM PDT How can I find how long a particular SP runtime?SueTons.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] How can I change the value for Colunm X to 0 where Column Y is '99' in my Query while leaving the actual value as is?

---

• How can I change the value for Colunm X to 0 where Column Y is '99' in my Query while leaving the actual value as is?
• New Database Request Form
• Creating Email "Alerts" based on reporting

How can I change the value for Colunm X to 0 where Column Y is '99' in my Query while leaving the actual value as is? Posted: 19 Mar 2013 08:44 AM PDT I'm still pretty new to SQL and any help would be appreciated. I'm using the following statement:SELECT EMPL_ID,PAY_TYPE_ID,SUM(LABOR_HRS) AS 'HOURS',SUM(LABOR_COST) AS 'Labor Cost'FROM LABORWhere TIMESHEET_DATE >= DATEADD(day, (DATEDIFF (day, '19800104', CURRENT_TIMESTAMP) / 7) * 7, '19800104') -6AND TIMESHEET_DATE <= DATEADD(day, (DATEDIFF (day, '19800104', CURRENT_TIMESTAMP) / 7) * 7, '19800104')GROUP BY PAY_TYPE_ID, EMPL_IDWhich returns the following values below:EMPL_ID001002003PAY_TYPE_IDAB99HOURS404040Labor Cost40095052How can I change the labor cost for any pay type using '99' to return 0, while leaving the actual value as is?For example, I would like it to be something like this... EMPL_ID001002003PAY_TYPE_IDAB99HOURS404040Labor Cost4009500I've tried using CASE previously without any luck (feeling pretty ignorant at this point).

New Database Request Form Posted: 15 Sep 2010 04:01 AM PDT Hi guys,Just wondering if anyone has created a new SQL Server Database Request Form? What I'm looking for is a template or form to give to Project Managers in the early stages of a project as soon as they determine that they will be needing a new SQL Server Database or installation.Thanks,Jody

Creating Email "Alerts" based on reporting Posted: 19 Mar 2013 12:13 AM PDT Please help a newbie figure out what is possible.I have created a set of color-coded "heat maps" based on server performance. Essentially, 1-84.99% = green, 85-94.99%= yellow, and 95-100%= red.Is there a way to "read" the report and generate emails based on the results?Advice, links, books, etc. all gratefully received.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610