Wednesday, April 24, 2013

[SQL Server 2008 issues] Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

[SQL Server 2008 issues] Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Posted: 21 Apr 2011 12:49 PM PDT

Anyone have a ready-made function that will add [x] number of days to a date and then adjust it to the nearest business day (Mon-Fri)?The code below is a VB.NET version of what I'm looking for. It allows for [n] days to be added (or subtracted if a negative number) to a date and then optionally adjusted if the result falls on a Saturday or Sunday.Its late, I'm tired and yes, I'll probably play around myself. But thought I'd ask anyway.[code] Public Shared Function getCalculatedDate(ByVal CalculationBaseDate As Date, ByVal Interval As Integer, Optional ByVal AdjustToNearestBusinessDate As Boolean = False) As Date Dim NewDate As Date NewDate = DateAdd(DateInterval.Day, Interval, CalculationBaseDate) If AdjustToNearestBusinessDate Then If Interval < 0 Then If DatePart(DateInterval.Weekday, NewDate) = 1 Then NewDate = DateAdd(DateInterval.Day, -2, NewDate) End If If DatePart(DateInterval.Weekday, NewDate) = 7 Then NewDate = DateAdd(DateInterval.Day, -1, NewDate) End If End If If Interval > 0 Then If DatePart(DateInterval.Weekday, NewDate) = 1 Then NewDate = DateAdd(DateInterval.Day, 1, NewDate) End If If DatePart(DateInterval.Weekday, NewDate) = 7 Then NewDate = DateAdd(DateInterval.Day, 2, NewDate) End If End If End If getCalculatedDate = NewDate End Function[/code]

Returning datasets in groups of 3 in SQL

Posted: 23 Apr 2013 06:47 PM PDT

I have a reporting services report that needs to generate multiple forms for the same report. Each report generated needs to always have 3 rows in it irregardless of the number of rows returned by the underlying query. That is, if my query returns 7 rows, I need to generate 3 reports for the with first 2 having 3 rows each and the third one having only 1 row in it. I have tried using NTILE with the parameter 3 but this only splits my set into 3 groups with the first having 3 rows and the remaining 2 groups having 2 rows each when my dataset of 7 rows. Is there any other way to split the records without using NTILE? Please help.

Building and shipping applications with SQL Server2008 R2 Express

Posted: 23 Apr 2013 05:23 PM PDT

If I am including sql server 2008 r2 express in my application then i have to signup for free distribution rights according to download page of sql server 2008 r2 express(http://www.microsoft.com/en-in/download/details.aspx?id=23650) I have downloaded the licence terms from [url=http://www.microsoft.com/en-us/download/details.aspx?id=29693]here[/url] . But whether I need to inform Microsoft or have to do anything to include sql server express in my application?

Help with "There is insufficient system memory in resource pool 'internal' to run this query"

Posted: 23 Apr 2013 11:01 AM PDT

Hi Experts I am getting this error when Running SSID job please helpHi,We are trying to run an SSIS job by pointing to "APPLICATION". It is failing because of insufficient system memory (please see below for additional details). This appears to be a problem on DATABASE.Time: Tue, Apr 23, 2013 01:44:35 PMJob: jbSpecCapMMCalcEarningsStep: FACETS - Generate MMStatus: failedServer: CDDEVDBV001Message: Executed as user: Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:44:35 PM Error: 2013-04-23 13:45:43.38 Code: 0xC0202009 Source: Load ods_PCPNeighborhood FACETS_SUPP_vwPRK_PROVIDER_NEIGHBORHOOD_MAPPING [5886] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is insufficient system memory in resource pool 'internal' to run this query.". End Error Error: 2013-04-23 13:45:43.39 Code: 0xC004701A Source: Load ods_PCPNeighborhood SSIS.Pipeline Description: component "FACETS_SUPP_vwPRK_PROVIDER_NEIGHBORHOOD_MAPPING" (5886) failed the pre-execute phase and returned error code 0xC0202009. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:44:35 PM Finished: 1:45:43 PM Elapsed: 68.344 seconds. The package execution failed. The step failed.Time: Tue, Apr 23, 2013 01:44:34 PMJob: jbSpecCapMMCalcEarningsStep: (Job outcome)Status: failedServer: CDDEVDBV001Message: The job failed. The Job was invoked by User TIDALUSER. The last step to run was step 1 (FACETS - Generate MM).Completed at 04/23/2013 01:45 PM

Best Solution?

Posted: 23 Apr 2013 05:05 AM PDT

I have the following issue:1. I work for a company that has boutique stores in various locations around the world. We also sell our products to other retailers, our trading partners. In some cases, we have small kiosks within our trading partners' stores. 2. I have built a data warehouse in SQL Server 2008 R2 for the sales of our products, both wholesale and retail. Actually, version 1 of the data warehouse was already underway when I was hired.3. In the staging database of our system, we have a table that contains information about both our boutiques and our kiosks inside our trading partners' stores. This table ("Call Cycle") contains the stores status (open/closed), Trading Partner's name and ID if applicable, store name within the retail chain, store opening date, store closing date, etc.4. The Call Cycle table also includes our sales organization's hierarchy that covers the particular store (VP, regional manager, etc.). 5. Currently, a business user creates a Excel spreadsheet (.xlsx) that contains the information described above. Then an SSIS package loads the .xlsx into the table in our staging database. The spreadsheet is updated intermittently, so the SSIS package is launched manually. Once the .xlsx file is loaded, the business user who modified the file can't look at the table himself to see what was loaded.Needless to say, this process is inefficient. The question is, what is a good way to modify or replace this process? I was considering Master Data Services since we are using SQL Server 2008 R2. But is MDS overkill for one table? Is there another way that requires less overhead and can be up and running quickly? I am the only developer working on this and I have project manager duties along with my SQL Server developer duties (I think we are woefully understaffed). May I please have some advice on this?

Installing a named Instance of SQL Server 2012, on a server where it already had SQL Server 2008 R2 Instance.

Posted: 23 Apr 2013 07:56 AM PDT

Hi Expertts,I got a requirement to install a new SQL Server 2012 on a server where it already had SQL Server 2008 R2 installed.My question is: what care i have to take before and after installing the new instance.The first instance is installed on C:can i install the second instance on the same drive leaving the default location as it is?

Database Data File Physical Fragmentation From Small Auto-Growth Setting

Posted: 13 Jan 2013 04:42 AM PST

To All,I need some advice. I've just started a new job and one of the first problems I've encountered is that a 365 GB database has auto-grown 1 MB at a time since the day it was created.3 years ago, the database was on direct attached storage. Then, it was moved to a Dell-Compellent SAN, a system for which I am a certified administrator. The database migration involved a database backup and a restore to the SAN which eliminated the physical fragmentation. But after that, it kept auto-growing in 1 MB increments. So, it probably has at least 100,000->200,000+ 1MB file fragments scattered all over multiple disk drives. I am going to review the performance implications of this condition with Dell-Compellent.In the meantime, I'd like to hear from any of the community on this forum:1. If you've had this situation, how did you respond to it?2. Is the potential for server performance degradation reduced if the database data file is on a SAN?3. If I must take action to rectify this condition, what are my best options?On the last question, I'm considering 3 options:1. A full backup and restoration of the database:1a. Backup the database.1b. Take the database out of production.1c. Delete the database.1d. Recreate the database with appropriate initial and auto-growth settings.1e. Restore the database.1f. Reconnect the Login and User SIDs.1g. Resume production.2. Take a SAN based snapshot of the database and use it to replace the existing database. (I don't think this will produce the desired results and I'm going to review it with Dell-Compellent.)3. File defragmenting software. There is a file defragmenting program available from Microsoft that is specifically designed to turn physically fragmented files into contiguous files: http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx. Contig.exe Version 1.7. It sounds promising. I would be very interested in your responses, especially if you've actually had to solve a similar problem.

concatenate

Posted: 23 Apr 2013 01:51 AM PDT

Can not figure out what I am doing wrong in table on I have FirstName and PhoneNumber and in the second table I have Carrier with @txt.***.Com etc. So when I write a proc to send text I want to be able to concatenate phonenumber and @txt.***.com togetherExampleSelect FirstName,PhoneNumber + SMSfrom Names as Ninner Join Carrier as Con N.CarrierID = C.CarrierID

Need Computed Column / Trigger / Function to insert data from another table

Posted: 23 Apr 2013 06:10 AM PDT

Hi,I've got the following Problem, which I'd like to solve in SQL Server 2008:I got a Table 'Products', each product with a range of up to 3 different areas, which contains Information about the Total Amount of items for this specific product.So the first product contains of a Total of 10 different Parts, whereas the 2nd product contains out of 5 and so on … PROD_ID : ACA : CW : DIVE : BR_ID1 : 5 : 1 : 4 : 102 : 0 : 0 : 5 : 113 : 1 : 0 : 2 : 124 : 2 : 1 : 2 : 10I got a second table named 'Breakdown' which defines the commissions paid for each different Product-Part ... BR_ID : ACA : CW : DIVE10 : 85 : 425 : 21311 : 0 : 0 : 29012 : 850 : 0 : 325This table is fix and used to calculate the Comms for each Individual – it's NOT editable by the USER.My third table 'Orders' contains all the ORDERS placed by the Customers ...ORD_ID : PROD_ID : Date 100 : 1 : 01-Apr 101 : 1 : 01-Apr 102 : 2 : 02-Apr 103 : 3 : 02-Apr 104 : 1 : 03-Apr In each of the 3 tables the first column is IDENTITY and as such definitely not entered twice.Here my problem:In my fourth table 'Comms' I need to get all the Commissions correctly entered and it's done by the Receivers themselves AFTER the Product has been sold and PAID- obviously it's unlikely that Comms are not entered at all seeing that people want to collect those - but it's more likely that people enter those multiple times/multiple people enter the Comms twice ... What I try to achieve is a Calculated Field to validate the entries in that table on SAVE Action, with multiple criterias.I want to only allow Users to enter the maximum allowed Amount of Prod.-Parts from the first table (ACA/CW/DIVE) for a single ORDER_ID from Table 3, so for example if Order_ID 100 refers to PROD_ID 1 (which contains 5 Parts ACA), then a Maximum of total Entries of ACA could not exceed 5 in ALL rows entered for this ORDER_ID or an error message is thrown …My Table 4 'Comms' looks like this so far:CREATE TABLE [dbo].[Comms]( [Comm_ID] [int] IDENTITY(65000,1) NOT NULL, [Cert_Date] [datetime] NOT NULL, [Ins_ID] [int] NOT NULL, [ORD_ID] [int] NOT NULL, [Amount_Aca] [int] NOT NULL, [Amount_Confined] [int] NOT NULL, [Amount_Dives] [int] NOT NULL,What I would like now is a Computed Column of the style like "Compare the Records for this Order_ID, find the Product and Breakdown-Values for it from Table 1 & 2 and then search for existing Records with that Order_ID in the 'Comms' Table itself. If no entry has been made, return the Maximum Parts as defined in the 'Products' Table. If someone entered for example already 2 ACA for PROD_ID 1 under ORD_ID 101, then return the remaining Value as 5 (MAX ITEMS ACA) - 2 = 3 ...A simple View can do this, but unfortunately I couldn't find a way to Create it with Computed Columns (as they can't refer to other Tables) or Functions/Triggers, as well as it has to be done more or less on Record Creation.The ID-Fields on all Tables are set IDENTITY, and FK are set in between them.On a positive side: This application is not for millions of Transactions at the same time, so it's a chance of about one million to one, that two users will enter their Comms at the same time, so double entries/engine performance shouldn't be a problem. ...Any help with this would be really appreciated, I'm new to this stuff, so any extra information that could help I'd love to provide. Thanks a lot already! :)

Script

Posted: 22 Apr 2013 09:20 PM PDT

Script to find the all the db roles of all the users in all the databases in sql 2005\2008

Backup taking too long after migration to SQL 2008 R2

Posted: 23 Apr 2013 06:40 AM PDT

Hello,We recently migrated databases from SQL 2005 to SQL 2008 R2. It's a nice shiny hardware, much better than the old server. It used to take 40 minutes to backup 550 GB database on old crap hardware with SQL 2005 and now it takes 7+ hours. I also tried with RedGate SQL Backup professional and it also takes 7 or more hours for the same backup. Compatibility mode is changed to 100 as well.Thoughts? Thanks for your help in advance.

Inserting records with a TIMESTAMP field for Archiving Table Data

Posted: 23 Apr 2013 06:14 AM PDT

Because I am paranoid (well, I call it 'realistic'), I like to backup my data before I delete anything and so I have quite a few examples of[code="sql"]INSERT INTO _Archive_TableSELECT * FROM Data_Table WITH (nolock)WHERE Last_Modified_Date < @Archive_Date[/code]Then, when I do the actual delete from Data_Table, I only delete the records that have a Unique Identifier already in the _Archive_Table.However, since a recent upgrade, this does not work on some tables because there is a field with the TIMESTAMP data type. I like using the asterisk, especially when there are 50 fields so specifying the fields is not acceptable.I have found the following solution;1. Get Query Analyser to generate the SQL script to Create the original table (Data_Table in my example above) and then edit the script as follows;a) Use _Archive_Table as the table name (use _Archive_Table_New as the name if an old _Archive_Table already exists)b) Replace the TIMESTAMP datatype with BINARY(8)c) Add a field to the end called Archive_Date with the type DATETIME2. Run the CREATE TABLE _Archive_Data SQL Script.3. If you have an existing archive table that you want to keep the data from, run the following SQL Scripts in order;[code="sql"]INSERT INTO _Archive_Table_NewSELECT *, GETDATE()FROM _Archive_Table WITH (nolock)TRUNCATE TABLE _Archive_TableDROP TABLE _Archive_TableSELECT *INTO _Archive_TableFROM _Archive_Table_New WITH (nolock)TRUNCATE TABLE _Archive_Table_NewDROP TABLE _Archive_Table_New[/code]4. Edit the archiving script to include a GETDATE() field after the asterisk;[code="sql"]INSERT INTO _Archive_TableSELECT *, GETDATE() FROM Data_Table WITH (nolock)WHERE Last_Modified_Date < @Archive_Date[/code]The TIMESTAMP field will implicitly convert to BINARY(8). The Archive_Date field is a useful way to find when a record was deleted and potentially allows you to delete the data from the _Archive_Table a year or two later once you are sure that no-one is going to want to recover it. I tend to use an initial underscore for backup tables in order to easily find them. If the backup table is a complete copy of an existing table, I usually just add the underscore as the prefix and the current date _YYYYMMDD as the suffix to the original table name. This makes it easy to know how old the backup table is for later pruning.A similar approach will work if the original Data_Table structure changes and you want to keep the old _Archive_Table active.

Subquery

Posted: 23 Apr 2013 05:10 AM PDT

I have gathered employee dataSELECT EmpID,LName,FName,StationIDFROM emp.employeesProvides me with my employee list of 2475This works fine until I doSELECTa.EmpID,a.LName,a.FName,a.StationID,b.RegionFROM emp.employees aJOIN emp.division bON a.StationID=b.StationIDI am getting multiple records back for the same person. I even tried a left join.Thoughts on how to make sure I can add the Region and only get the 2475 records back?

Is there a way to find who added data and log file to database

Posted: 23 Apr 2013 05:33 AM PDT

Hi SQL Guru's,Is there any way to find out who has added any new data file or log to the database without enabling DDL triggers.RegardsMd.S.Hassan

Is this data lost?

Posted: 23 Apr 2013 01:14 AM PDT

Hi, Have a database that is set to recovery model=full. Some data was inserted then immediately deleted, is it possible to get the inserted data back? A Full backup has been done since this happened so I'm assuming that the tail of the transaction log has now been "flushed". It was a Sql 2005 express installation.

Aggressive Indexes

Posted: 23 Apr 2013 05:54 AM PDT

Hi everyone. I used Brent Ozar's excellent sp_blitzindex script to discover the below issue. There is a link in the script to explain the output but I'm still a bit confused. Is it telling me that the index had to wait 11,40 times to get a page lock totaling 86 minutes since the last time SQL Server restarted? It has been almost a month since restart so does that means that 86 minutes of processing time has been lost? With an avg duration of 0, should I be concerned?Thanks for reading.HowardAggressive Indexes: Total lock wait time > 5 minutes (row + page)dbo.OAD.JJ_032913_DTA1 (12): Page lock waits: 11,480; total duration: 86 minutes; avg duration: 0 seconds; Lock escalation attempts: 10,601; Actual Escalations: 7.

Copy db from server a to server b nightly

Posted: 23 Apr 2013 02:50 AM PDT

Hello - I have a db on one server, and I'd like to copy it over to a different server every night to use as a backup/reporting instance. These are both SQL Server 2008 R2. (Not sure what else is relevant information.)Anyway, if there is something that details the steps to do this, I would be happy to go there and read that - does anyone have any recommendations? I did search the forums, but what I saw wasn't ss2008, so...thought I'd better ask.Thank you in advance for any guidance/advice/knowledge-sharing --Donnap.s., you can take it as given that my "dba skills" are somewhat short of novice. :( I get the security stuff (somewhat), but the manipulation/creation, etc....pretty much zip. Hence the cluelessness.

Trouble grabbing values for multiple IDs

Posted: 23 Apr 2013 04:28 AM PDT

Trying to get a total of instances of events and assigning it an alias [b](SELECT COUNT(id) from Finish f1 where PerfDate = '2013-04-18' and NoEvent = 'yes' ) AS numbercancelled[/b] ...but if there are more than one ID that had a NoEvent = 'yes' , i dont want the grand total of both IDs, but individual totals. group by here don't work because I just need one value for numbercancelled, I'm wondering if i need an sql function to return a value for each ID, where result would be something like:ID numbercancelled100 3115 7

Optimizer and stale statistics

Posted: 23 Apr 2013 02:53 AM PDT

The question is, what happens when statistics on NORECOMPUTE set tables go stale? i.e. pass the rowmodctr > (unfiltered rows * 0.2) + 500 (assuming unflitered rows > 500), i.e. go past the point where they would be automatically recomputed if the NORECOMPUTE wasn't set.Normally, automatic recomputation of the stats would force a recompile. Does the optimizer even check the staleness of the stats if NORECOMPUTE is set? If it does and notices they are out of date, but it can't recompute them, does it recompile the plan anyway? When, for any reason, a plan is recompiled and the optimizer finds the stats it had previously used were out of date, does it use them anyway? Or does it ignore them and act as if no stats existed or could be automatically created?And yes, before anyone says, I'm well aware that NORECOMPUTE is not advised. That's not the question.

Urgent - Reporting services migration - Subscription jobs did not get created

Posted: 23 Apr 2013 04:15 AM PDT

We are moving to a new data center. We have RS install on appweb server that points to different sql server for Reportserver and Reportservertempdb. So, We created a new virtual appweb server and new physical database sql server 2008 r2 at new DC. I did a fresh install of new reporting services 2008 r2 on appweb server. I backed up and restored Reportserver and reportservertempdb dbs from old 2005 sql server to new 2008 r2 database server.During new RS configuration, I pointed databases to the new restored databases and restored encryption keys. When launched reporting services URL, it works fine.I stopped and restarted reporting services after configuration.However, I am not seeing any single job created on new database server. Old server had 20 jobs.How do I get these subscriptions back to the new install? I thought this should be created with migration and I do not have to do anything.One odd thing I had to do during config was that:In scale-out deployment tab in RS configuration, it was showing both the new and old appweb server name as status joined. I was unable to remove the old server name by clicking 'remove server' there. So, I deleted old server name from keys table in Reportsserver as suggested by some forums to have a clean installation. Does it have anything to do with jobs not created?What do I need to do now to get subscripts jobs created. Ther eare only 20 jobs here but another RS server has over 800 subscriptions that I can not mess up. So, need to find the issue and correct it before I can move on to the another RS instance.We are under severe time crunch. I apprecite your help.Thanks,

Need an update statement

Posted: 22 Apr 2013 10:36 PM PDT

[code="sql"]/****** Object: Table [dbo].[PhoneTable] Script Date: 4/23/2013 4:55:46 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[PhoneTable]( [PhoneNumber] [varchar](10) NULL, [PhoneType] [nvarchar](4) NULL, [NameID] [int] NOT NULL, [AssocID] [int] NOT NULL, [SortOrder] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2626770315', N'HOME', 1000, 20, 0)GOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2626270104', N'CELL', 1000, 20, 1)GOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'0003343553', N'HOME', 1000, 10, 0)GOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'0003354427', N'WORK', 1000, 10, 2)GOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2628479870', N'CELL', 1000, 30, 1)GOINSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'3458790', N'WORK', 1000, 30, 2)GO/****** Object: Table [dbo].[NamePhone] Script Date: 4/23/2013 4:56:19 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[NamePhone]( [PhoneNbr1] [varchar](20) NULL, [PhoneType1] [char](15) NULL, [PhoneNbr2] [varchar](20) NULL, [PhoneType2] [char](1) NULL, [NameID] [int] NOT NULL, [AssocID] [int] NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 20)GOINSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 10)GOINSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 30)GO[/code]Hi Friends, I want to update Phone number 1 and phone number 2 in NamePhone table from Phone Table....Requirements: 1. If a AssocID has a Home number , it has to update to phone number1 else cell number else work number.......as you can see the sortorder field in PhoneTable.... 2. If Phone number 1 field is having home number then update phone number 2 field with cell number else work number....if phone number 1 has cell number then update phone number 2 with work number....I tried to do this with case statement, but some how I failed....So i need your suggesstions friends.....

Need to change NDF initial size to reclaim disk space

Posted: 23 Apr 2013 02:16 AM PDT

I've inherited a server with several databases, one which has 7 NDF files. The initial size for most of the NDF files are 500 MB, with one of them having an initial size of 104G! I cannot shrink the database or file to recover the space that this .NDF using, and it's up to 55G.So my question is this, can I change the INITIAL SIZE in SSMS for that NDF data file without taking the DB down and losing data? How can I get that NDF to move data out into the other NDF files? Perhaps I need to increase all the NDF fileS initial growth size to something more manageable?

snapshot agent failing

Posted: 23 Apr 2013 02:12 AM PDT

We are currently having a problem with snapshot replication which we use load data into out Reporting servers.Would the fact the main data file is 98.93% of the databse we are snapshoting full cause the snapshot agent to fail as we are having issues with setting up the replication.Any advice would be helpful.Thanks:-)

Language British

Posted: 22 Apr 2013 10:36 PM PDT

I have one of dozen users who is getting the date in US format, this despite running a script to change her langauge, also by checking Security > Logins she is showing the langauge as British, she is running Windows 7 and if she uses another machine the date shows (and edits) correctly.

Help with Second highest date

Posted: 22 Apr 2013 10:23 PM PDT

I have a stored procedure and I am selecting Renewal Date and the Last Renewal date (second last greater date)[code="sql"]ALTER PROCEDURE [rept].[spRenewalDates] @startDate DATETIME, @endDate DATETIMEASBEGINSET NOCOUNT ON;--DECLARE @system INT SELECT tblrenewals.SystemNum, (case when Year([Date]) = 1900 or Year([Date]) = 2999 then '' else ISNULL(CONVERT(VARCHAR, DATE, 103) + ' ' + left(CONVERT(VARCHAR, DATE, 108), 5),'') end ) AS [Renewal Date] ,(SELECT MAX( date ) FROM tblRenewalsWHERE date != ( SELECT MAX( date ) FROM tblRenewals where SystemNum = tblSystem.SystemNum ) and SystemNum = tblSystem.SystemNum) AS [Last Renewal Date] ,ClientName, Name AS [Ot's] FROM tblRenewals INNER JOIN dbo.tblSystem ON dbo.tblRenewals.SystemNum = dbo.tblSystem.SystemNum INNER JOIN dbo.tblClient ON dbo.tblSystem.ClientNumber = dbo.tblClient.ClientNumber INNER JOIN dbo.tblReps ON dbo.tblClient.RepType1 = dbo.tblReps.Id WHERE (DATE BETWEEN @startDate and @endDate) ORDER BY Date DESC END[/code]To get the correct format for the renewal date I am using[code="sql"] ISNULL(CONVERT(VARCHAR, DATE, 103) + ' ' + left(CONVERT(VARCHAR, DATE, 108), 5),'')[/code]Can someone please edit the above stored procedure so I get the Last Renewal date in the same format as the Renewal Date.Thank you

double hopping

Posted: 22 Apr 2013 10:15 PM PDT

can any one explain me what is double hopping

Qurery to know tha no of instances

Posted: 22 Apr 2013 09:24 PM PDT

How to know No. of instances in my sever using Query

storing output of a Stored Procedure in a vairable

Posted: 23 Apr 2013 12:34 AM PDT

Hi,I am inserting value in a table Gv_storeAllocation for column STNNo i have created a stored procedure. Can anyone tell me how to store output of that stored procedure in a variable @StnNo? INSERT INTO GV_StoreAllocation (StoreId, STNNo, FirstVoucherNo, LastVoucherNo, Quantity)SELECT DISTINCT @storecode,EXEC @STNNo = dbo.GetSTNValue @storecode , 0 ,'',@FirstVoucherID,@lastvoucherID,@quantityFROM GV_Voucher gv JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusIdWHERE gvs.VoucherStatus = 'Active at HO

Error="8952" Severity="16" State="1 what is error number 8952 stands and solution?

Posted: 22 Apr 2013 10:52 PM PDT

What is the error number 8952 and remedy to fix the same.<DbccResults> <Dbcc ID="0" Error="8952" Severity="16" State="1">Table error: table 'backupmediaset' (ID 1858105660). Index row in index 'backupmediasetuuid' (ID 2) does not match any data row. Possible extra or invalid keys for:</Dbcc> <Dbcc ID="1" Error="8956" Severity="16" State="1">Index row (1:952:151) with values (media_uuid = 'F039C1AB-8103-4D9D-B0F6-86D4968938C3' and media_set_id = 22433) pointing to the data row identified by (media_set_id = 22433).</Dbcc> <Dbcc ID="2" Error="8952" Severity="16" State="1">Table error: table 'backupmediaset' (ID 1858105660). Index row in index 'backupmediasetuuid' (ID 2) does not match any data row. Possible extra or invalid keys for:</Dbcc> <Dbcc ID="3" Error="8956" Severity="16" State="1">Index row (1:1127:36) with values (media_uuid = '9F1D7A44-16E9-417B-B58C-DF4CEB8ACFBD' and media_set_id = 22431) pointing to the data row identified by (media_set_id = 22431).</Dbcc>

Add x amount off days and don't count Weekends

Posted: 22 Apr 2013 09:39 PM PDT

Good DayI am struggling to figure this one out. I have a column with Startdate, I want to add x amount of days to that date but don't want to count weekends. if date is 2012/04/23 and I add 7 days straight it comes to 2012/04/23 without the weekends 2012/05/02 (Correct Way). I did look at [code="plain"]WHERE WEEKDAY(date) '>' 0 AND WEEKDAY(date) '<' 5)[/code] but don't know how to implement it.Thanks

Excel 64 bit driver

Posted: 14 Jan 2013 03:51 PM PST

Hi all, I am running the following query in sql server 2008 r2(64-bit),MS office 2007 excel file.SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=<loc>', 'SELECT * FROM [Sheet1$]')its showing errorMsg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.i run the following codesp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGURE;GObut still its showing the same error...any help....thanks and regardsSathiyan R

Similar strings.

Posted: 22 Apr 2013 10:33 PM PDT

In the past week, I saw somewhere a string similarity function.(I think I saw it in my 'spare' time when I am not focused on my work but still am reading about SQL-server to keep up. So did not note the link, the book or whatever.)Depending on the longest string which was shared between two strings a value between 0 and 1 was given. This dependend on the length of the string(s) and the similarity. A tresshold was set to get a ?? good starting point??One example were it was used was to compare chemical formula. And find the same formula but which was not written the same.Does anybody recognise were I could have seen this?Other (or same) techniques to find similar strings?We want to use this technique to find questions which are similar but formulated differently. Does not have to be perfect, but is to be used a a help. (I know the Soundex and Difference functions).Thanx in advance,ben brugman
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog