Great New Resource for Books Online Information

SQL Server Documentation

Index ‹ SQL Server

- Previous
  - 1
    - Lookup concurrency issue in packages running simultaneously in parallel Phil Brammer wrote: As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS. Phil is absolutely correct. Don't assume that selecting from the dropdown is the same as issuing a "SELECT * FROM...". As explained here: SELECT *... or select from a dropdown in an OLE DB Source component http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx -Jamie Tag: Great New Resource for Books Online Information
  - 2
    - getting a total from a total well let's see. I have records in the detail field.. IE: Name Amount Open Credit Limit Date ABCD1 100.00 5000.00 01/01/07 ABCD1 200.00 5000.00 02/01/07 ABCD2 100.00 5000.00 01/01/07 ABCD2 200.00 5000.00 02/01/07 ABCD3 100.00 5000.00 01/01/07 ABCD3 200.00 5000.00 02/01/07 and so on.. So in the group 2 I select the max from each detail credit limit field. Which in this case is 15000.00. Becuase I am selecting only 1 value from each disticnt name. so that works for group2, but group one is based on a different group value and doesn't allow me to add up the values from the credit limit field in group2. Make sense Tag: Great New Resource for Books Online Information
  - 3
    - order by cluase cause wrong results to be returned. Since you said there is only one row, I reverse-engineered it and created the following: Code Snippet USE tempdb; GO /****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[deletethisTempOut]( [ThemeName] [varchar](60) NULL , [intLocationCount] [int] NULL , [dblRepValueA] [float] NULL , [dblRepValueB] [float] NULL , [dblRepValueC] [float] NULL , [dblRepValueD] [float] NULL , [dblTotalRepValue] [float] NULL , [dblLimit1] [float] NULL , [dblLimit2] [float] NULL , [dblLimit3] [float] NULL , [dblLimit4] [float] NULL , [dblTotalLimit] [float] NULL , [fltEmployeecount] [float] NULL , [intAreaLevel1] [tinyint] NOT NULL , [strFullName] [varchar](13) NOT NULL , [strAreaLevel2] [varchar](20) NOT NULL , [strAreaLevel3] [varchar](20) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF Go INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3, dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3 ) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1) GO -- Query 1 SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3, dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3 FROM deletethisTempOut ORDER BY strAreaLevel2, strAreaLevel3 GO -- Query 2 SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3, dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3 FROM deletethisTempOut ORDER BY ThemeName GO I couldn't repro the problem you are having. Is this similar to what you have Are you missing out a WHERE clause in one of the queries Tag: Great New Resource for Books Online Information
  - 4
    - Error 87 while installing SQL Server 2005 Whoa, this was more than 1 year ago. I don't even work for that company anymore. Well...going back to my issue, as far as I remember I had to re-burn de DVD using another dvd-burner (a different pc). If that doesn't work then you can try by mounting the .iso (you'll may have to download another software in order to perform this action) And if you're talking about the beta/old versions then you only have to uninstall it. I hope this helps. Tag: Great New Resource for Books Online Information
  - 5
    - Catching Events Are the errors immediate or near immediate This scenario of "package level event level handler not firing and empty package logger file" sounds as if you're receiving errors on package load or on package level validation, very early in the attempted load/validate/execute execution sequence. If particular, what kind of error events are being you receiving Please attempt to run the package via dtexec (32 or 64 as appropriate) as well and then post the text of the first few errors. Tag: Great New Resource for Books Online Information
  - 6
    - Vista SQL 2005 SP2 client to Windows XP SQL 2005 (non-sp2) workstation. What's your connection string and error msg Tag: Great New Resource for Books Online Information
  - 7
    - Trigger(its very urgent) thx alot i make it like l this im sure now its ok right update department set No_Of_Emp = isnull ( No_Of_Emp , 0 ) + isnull (( select isnull ( count (*), 0 ) from inserted as inserted1 where inserted1 . Department_Id = inserted . Department_Id ), 0 ) from inserted , department where inserted . Department_Id = department . Department_Id -- Tag: Great New Resource for Books Online Information
  - 8
    - Cant find data source Hi, I use Visual Studio 2005 Pro. I installed Microsoft SQL Server Compact Edition Developer Software Development Kit . When i try to make a connection , i can't change the datasource to the compact version.(Not vissible) What could be the problem. Sorry for my bad english, Thx JoskeXP Tag: Great New Resource for Books Online Information
  - 9
    - Unable to Edit or Create SSIS packages or Maintenance Plans Hi, I am running SQL 2005 Enterprise with SP2. In the past I have been able to successfully create SSIS packages using the BIDS and also Maintenance Plans from within Management Studio. Today I tried to edit a maintenance plan and got the following error message:- TITLE: Microsoft SQL Server Management Studio ------------------------------ Retrieving the COM class factory for component with CLSID {0BE35203-8F91-11CE-9DE3-00AA004BB851} failed due to the following error: 80040154. (Microsoft.DataWarehouse) ------------------------------ BUTTONS: OK ------------------------------ This error also occurs if I try to create a new Maintenance Plan. I then tried opening an existing SSIS package in BIDS and got the following error:- ------------------------------------------------------------------------------------------ Microsoft Visual Studio is unable to load this document Index (zero based) must be greater than or equal to zero and less than the size of the argument list. ------------------------------------------------------------------------------------------ These errors only occur on my PC so I know that the problem is with my PC. I have totally uninstalled SQL 2005 components from my PC and reinstalled them with no luck. To my knowledge, I haven't installed any other programs since creating these packages, although there's a possiblilty my PC may have had some patches applied through SMS. I am running Windows 2000 Professional. Can anyone help me resolve this problem Thanks. Martin Tag: Great New Resource for Books Online Information
  - 10
    - Select expression Type Hi, I'm executing the following Query : SELECT FC3 . Quarter , FC3 . Service , FC3 . TOTALIS , FC2 . Quarter AS Expr1 , FC2 . Service AS Expr2 , FC2 . FCtIS , cast (( FC2 . FCtIS / FC3 . TOTALIS ) as decimal ( 4 , 2 )) as Value , getdate () as DateUpdate --INTO FC4 FROM FC3 LEFT OUTER JOIN FC2 ON FC3 . Quarter = FC2 . Quarter AND FC3 . Service = FC2 . Service result: Quarter Service TotalIS Quarter Service FCtIS Value DateUpdate 2007Qt1 Executive contracts 210 2007Qt1 Executive contracts 166 0.00 2007-05-11 15:23:08.100 My problem is that ' can’t get a decimal Value on the VALUE column .... If i don't use the cast function i get a 0 , forcing a decimal value with cast i get 0.00 .. This column is intended to retrieve a percentage , like 0,5(50%) ; 0,45 (45%), what is happening Tag: Great New Resource for Books Online Information
  - 11
    - The dreaded subreport could not be shown error I seem to have run across the feared "Error: Subreport could not be shown" problem. I have very carefully checked everything I could find on the issue, but nothing seems to help--and I must admit, I'm at my wits' end here (though it's probably something absurdly simple). I have a main report with one subreport. I am running ASP.NET, with a ReportViewer control on my ASPX page. The main report and subreport are both local reports, and pull data from SQL data sources. Initially, I had the parent report pass a parameter to the subreport, but in the interest of keeping things simple I removed that--even though I verified that my handler for the subreport processing was valid and was obtaining valid values for the parameter. Here's the applicable code from the CodeBehind's class: Code Snippet DataView vw; protected void Page_Load(object sender, EventArgs e) { vw = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.None); rptAllCustomers.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(ProcessSubReports); } protected void ProcessSubReports(object sender, SubreportProcessingEventArgs e) { //vw.RowFilter = "SALES_INDEX = " + e.Parameters["SaleIndex"].Values[0]; e.DataSources.Add(new ReportDataSource("SALESConnectionString", vw)); } And the applicable ASPX code: Code Snippet <rsweb:ReportViewer ID="rptAllCustomers" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="100%"> <!%--<localreport reportpath="App_GlobalResources\SalesByProduct.rdlc"> </localreport>--%> <localreport reportpath="App_GlobalResources\BySale.rdlc"><datasources> <rsweb:ReportDataSource DataSourceId="SqlDataSource1" Name="SalesData_SALES"></rsweb:ReportDataSource> </datasources> </localreport> </rsweb:ReportViewer> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sales %>" SelectCommand="SELECT * FROM SALES WHERE SALE_DELETED = 'false'"> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:sales %>" SelectCommand="SELECT * FROM CUSTOMER_PRODUCTS"> </asp:SqlDataSource> Through debugging and other means, I have found the following: 1. The data being passed through vw in the ProcessSubReports is valid. 2. The subreport runs perfectly when placed into its own ReportViewer 3. The reports are all in the App_GlobalResources folder 4. The main report references a subreport by the name of SalesByProduct 5. App_GlobalResources/SalesByProduct.rdlc is the name of the report file I should be able to provide more information on request. Anyone have any clues Tag: Great New Resource for Books Online Information
  - 12
    - Looking for a component I'd like to incorporate the "package component tree" UI component that is used within the Visual Studio designers into an SSIS utility I'm building. This is the one I'm talking about: Edit: Apparently using the IMG tag on these forums works in the editor preview, but not in the actual posts, so I've replaced images with links... Example 1 Example 2 I've done some searching online, but have not found any information about where this UI is implemented, or if it is reusable. Does anyone here know if it is possible to re-use this component in a .NET application Thanks in advance! Tag: Great New Resource for Books Online Information
  - 13
    - Can we use Excel file (source) for Bulk Insert Task Can an excel file be used as a source for a Bulk Insert Task in SSIS instead of a delimited flat file Tag: Great New Resource for Books Online Information
  - 14
    - Identity Primary Field in Merge Replication Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity. What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e On Server 1 - Values start from 1 then 2,3,4 etc etc Server 2 - 24001, 24002, 24003 etc etc Server 3 - 46001, 46002, 46003 etc etc Server 4 - 68001, 68002, 68003 My question is what happens when these ranges eventually conflict Do they automatically gain a different range such as 142 001, 142 002 etc etc Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app. cheers Tag: Great New Resource for Books Online Information
  - 15
    - An internal error occurred. [ ID = 3639 ] on delete query Repair can be done from SSMS(E), right click the database, select Properties and the Shrink/Repair tab. Also, the SqlCeEngine object has a Repair method. Tag: Great New Resource for Books Online Information
  - 16
    - JULY CTP (WORKSTATION COMPONENTS FAIL INSTALL) AMD ATHLON 64-BIT WINDOWS XP PRO X64 EDITION SQL SERVER KATMAI X64 BIT INSTALLTION (JULY CTP) I've been trying to install the WorkStation components from the JULY CTP. I previously had the JUNE CTP installed. The entire instllation of the database engine and integration services went fine. The workstation components failed. After this failure, I removed the JUNE CTP workstation components and rebooted the PC. I attempted to install JUST the workstation components and the install failed. I then UNINSTALLED all SQL Server components. ALL SQL 2005 and SQL KATMAI. Another reboot. On the 3rd attempt to install just the Workstation components, the install again failed. Here's the error (summary.txt) Machine : 24331036PC Product : Workstation Components, Books Online and Development Tools Error : Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error -------------------------------------------------------------------------------- Machine : 24331036PC Product : Microsoft SQL Server 2008 Tools (64-bit) Product Version : 10.0.1049.14 Install : Failed Log File : C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\LOG\Files\SQLSetup0009_24331036PC_Tools.log Last Action : InstallFinalize Error String : Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error Error Number : 29547 MORE LOG INFO (SQLSetup.log) Doing Action: Do_sqlCopyXML PerfTime Start: Do_sqlCopyXML : Fri Aug 03 09:47:28 2007 <Func Name='Do_sqlCopyXML'> _LoadXMLFile(C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config).IID_IXMLDOMDocument->load returned 2147500037. Error Code: -2147467259 Windows Error Text: Unspecified error Source File Name: sqlca\sqlxmlca.cpp Compiler Timestamp: Wed Jul 25 09:56:12 2007 Function Name: Do_sqlCopyXML Source Line Number: 863 ---- Context ----------------------------------------------- Setting status of unmanaged components and removing unmanaged resources Error Code: -2147467259 MSI (s) (58!1C) [09:47:35:841]: Product: Microsoft SQL Server 2008 Tools (64-bit) -- Error 29547. Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error Error 29547. Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error Tag: Great New Resource for Books Online Information
- Next
  - 1
    - VERY slow stored procedure performance on datetime parameter eralper - I had already tried running the sproc with the recompile option each time. It does help improve performance and would, depending on the parameters, run in as little as 14 seconds. However, that's still far from the 0-2 of the normal SQL. As for the date range specified, I'm honestly not clear on how the range of dates would effect execution plan, but in this particular case it isn't relevant because the date range was only a day. Every time the query has been run the range is between 1-7 days. Thanks for the input. Chris Tag: Great New Resource for Books Online Information
  - 2
    - query format date Hi I wrote query to get date from database SQL 2000 server. Sometime I get different date format not MMDDYYYY but number as Julian calendar Do you know what wrong with this How can I wrote SQL so that all date will be MMDDYYYY Thanks Daniel Tag: Great New Resource for Books Online Information
  - 3
    - How do i start playing with sql server??? Hi all, please help me!! I've downloaded and installed the express edition file (SQL Server 2005 Express Edition with Advanced Services - 234mb) but all I'm getting on the program menu are the configuration tools! Something appears to be missing - can someone shed any light on this THanks in advance Tag: Great New Resource for Books Online Information
  - 4
    - Foreach Loop - Value back to 0 Hi, Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data. Somewhere inside the Foreach Loop, you set an int variable MyInt to a value. My question: Is it possable when each iteration begins to set MyInt to 0 Thanks in advance. Tag: Great New Resource for Books Online Information
  - 5
    - How to set douplicate id in sql server 2005? See if this helps you. Take a look at my post: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1211015&SiteID=1 Tag: Great New Resource for Books Online Information
  - 6
    - Programmatically aborting a package There is a cleaner method. Call RunningPackage.Stop(). Tag: Great New Resource for Books Online Information
  - 7
    - SQL 2005 Management Studio Query Designer suggestions / questions Thanks Steve for that - good to know about the SQL Server product feedback site - I'll definitely post them, but gonna use this as a sort of a blog to first get my thoughts in order (so what I say is consistent As to item 6 , one can already check the * box in the graphical query designers, and they do a good job blocking a filter condition for the * row in the criterior pane as expected - and as SQL 2000 Enterprise Manager did so well - it just makes sense (to me at least) to have me tell the designer what I'm trying to do, not the other way round. When I'm ready to list all fields, then ... it will cooperate (evil grin) If you have access to any of the old printed MS Excel manuals - about '96 or '98 I think, there is a very clear explanation n the forward of Microsoft's "for dummies" philosophy with that product - that the program tries to figure what you're trying to do, so you don't have to understand how computers and technology work - you can just get the job done as quickly as possible. It seems however to be an all encompassing MS philosophy since early days till now - taking away the programmers' power - GRRRRRRRRRRR Mmmmmmm - will have to think about what you say for production code to list all fields - yes I suppose it makes it clear what the query is doing. (also makes it more difficult to edit the query, as well as dependent views etc.) As to Item 7 - aha yes of course you're right - thanks As to Item 3 : Open Table Query Designer New Query Remarks A Graphical Query Designer Another Graphical Query Designer Non-graphical editor with full T-SQL capabilities – ie: not just a query designer ctrl-1, 2, 3, 4 do what Show or hide the Diagram, Criterior, SQL and Results Pane Same, but no Results pane, so ctrl-4 does nothing As per settings in Options > Environment > Keyboard F5 works No No Yes Modal No Yes No Can see results Yes No Yes Table Properties in context menu No Yes N/A Tag: Great New Resource for Books Online Information
  - 8
    - Error on Date Data During Insert Have you redirected your error output to a data viewer to be able to inspect the error row(s) Have you previewed the data in the flat file connection Does it all look correct Can you post a sample row of data Tag: Great New Resource for Books Online Information
  - 9
    - Auto Filter in 2005 Reporting Services Hello, Thats a good suggestion abt using Report Model. If I can use a filter on Report Model, I can create a parametric report with default parameters and let the users choose column values from parameters but appreciate the idea though Secondly, is there a way to have a standard template for Report Model where users can create ad hoc reports and all of them have a similar look.(Using RS 2005 Ent with SQL 2005) Lastly, I have found this link which promises to do something similar but cannot get it to work. Does this make sense to u guys http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx Regards Tag: Great New Resource for Books Online Information
  - 10
    - ForEachLoop task not behaving as expected Hi, I have a ForEach Loop that has 3 script tasks in it. I have them set up so that they execute in order, such as: script1 ---> script2 ---> script3 script1 creates a file script2 creates a file script3 compares the files using a diff command Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet. The error is "The system cannot find the file specified". Thanks Tag: Great New Resource for Books Online Information
  - 11
    - The variable "System::LocaleID" is already on the read list. lumbule wrote: I did copy and paste on a few packages and I regenerated the IDs. That didn't help, so I went back and re-created the problem packages from scratch. That didn't work as well. That's where I was headed... Can you be in a situation where a given package is still running when the "custom service" comes around again and executes the package Another question. How are you executing the packages from within your service Tag: Great New Resource for Books Online Information
  - 12
    - Import and Export Data Wizard missing in SQL Server 2005 Express Edition Go into Add/Remove Programs, find Microsoft SQL Server 2005 and click Change. When the wizard opens, there should be a Report button, clicking this will give you a report of everything you have installed and what version it is. Try these references for using BCP: http://support.microsoft.com/default.aspx/kb/67409 http://www.devarticles.com/c/a/SQL-Server/An-Introduction-To-The-Bulk-Copy-Utility/ Mike Tag: Great New Resource for Books Online Information
  - 13
    - Query problems - Group By and Latest date Thanks, That is exactly what i wanted to do. Works like a charm. Grant Tag: Great New Resource for Books Online Information
  - 14
    - Reporting Service Cache Once you create a snapshot report, all subsequent requests will be served using the cached instance irrespective of the parameters passed. That's because a snapshot report is cached only for the default parameter values specified when the snapshot is generated. In contrast, session caching is performed for each parameter combination. i haven't enabled session cache coz i not displaying my rdl in browser Session caching is always enabled. You can control only the session timeout (the minimum is 60 seconds). If you pass the session identifier back to the report server you can take advantage of the session caching when you call the RS web service. Tag: Great New Resource for Books Online Information
  - 15
    - Newbie Parameter Problem Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily): SELECT DISTINCT AccountFamily FROM CallDataRecords The table on the form contains the following DataSet: SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily FROM CallDataRecords WHERE AccountFamily = @CompanyName AND ModifiedOn BETWEEN @StartDate AND @EndDate The error I get is: "Query execution failed for data set (one directly above)". "Must declare the scalar variable "@CompanyName". Can anybody shed light please Thanks, Dan Tag: Great New Resource for Books Online Information
  - 16
    - Backup Database HI i have created job which executes following Store Procedure, my Qs is after 1-day has passed shldnt it overwrite , i mean i have 1-GB Database my job runs for 3-times a day so end of day i have 3-GB so wen time goes past 12.00 am shldnt it becomes 1-GB again Alter Procedure sp_DBBackup as BACKUP DATABASE [XYZ] TO DISK = N'D:\Office Purpose\Database\Backups\XYZ\Backup-Method (Append-Expiry)\XYZ_Complete_Append.Bak' WITH NOINIT , NOUNLOAD , RETAINDAYS = 1, NAME = N'XYZ backup', NOSKIP , STATS = 10, NOFORMAT Tag: Great New Resource for Books Online Information

Buck Woody - MSFT

The SQL User Edutcation Team has been working hard with our teams here to get better/more/good information out to our users. We’ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback:

http://search.live.com/macros/sql_server_user_education/booksonline

We want to generate as much traffic as possible here so that we can see if this is useful to our users. We’d love to hear back from everyone we can!

Top

Re: Great New Resource for Books Online Information

tosc

Hi,

looks like a great job you have done.

tosc

InsideSQL.de

Top

Re: Great New Resource for Books Online Information

Buck Woody - MSFT

Thanks! There's lots more innovations listed here:

http://blogs.msdn.com/buckwoody/archive/2007/06/14/books-online-innovations.aspx

Top