Search Results

Search found 27905 results on 1117 pages for 'sql authority'.

Page 222/1117 | < Previous Page | 218 219 220 221 222 223 224 225 226 227 228 229  | Next Page >

• Map inheritance from generic class in Linq To SQL

  - by Ksenia Mukhortova

  Hi everyone, I'm trying to map my inheritance hierarchy to DB using Linq to SQL: Inheritance is like this, classes are POCO, without any LINQ to SQL attributes: public interface IStage { ... } public abstract class SimpleStage<T> : IStage where T : Process { ... } public class ConcreteStage : SimpleStage<ConcreteProcess> { ... } Here is the mapping: <Database Name="NNN" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"> <Table Name="dbo.Stage" Member="Stage"> <Type Name="BusinessLogic.Domain.IStage"> <Column Name="ID" Member="ID" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" /> <Column Name="StageType" Member="StageType" IsDiscriminator="true" /> <Type Name="BusinessLogic.Domain.SimpleStage" IsInheritanceDefault="true"> <Type Name="BusinessLogic.Domain.ConcreteStage" IsInheritanceDefault="true" InheritanceCode="1"/> </Type> </Type> </Table> </Database> In the runtime I get error: System.InvalidOperationException was unhandled Message="Mapping Problem: Cannot find runtime type for type mapping 'BusinessLogic.Domain.SimpleStage'." Neither specifying SimpleStage, nor SimpleStage<T> in mapping file helps - runtime keeps producing different types of errors. DC is created like this: StreamReader sr = new StreamReader(@"MappingFile.map"); XmlMappingSource mapping = XmlMappingSource.FromStream(sr.BaseStream); DataContext dc = new DataContext(@"connection string", mapping); If Linq to SQL doesn't support this, could you, please, advise some other ORM, which does. Thanks in advance, Regards! Ksenia

  Read the article

• Create table and call it from sql

  - by user1770816

  I have a PL/SQL function which creates a new temporary table. For creating the table I use execute immediate. When I run my function in oracle sql developer everything is ok; the function creates the temp table without errors. But when U use SQL: Select function_name from table_name I get an exceptions: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML ORA-06512: at "SYSTEM.GET_USERS", line 10 14552. 00000 - "cannot perform a DDL, commit or rollback inside a query or DML " *Cause: DDL operations like creation tables, views etc. and transaction control statements such as commit/rollback cannot be performed inside a query or a DML statement. Update Sorry, write from tablet PC and have problems with format text. My function: CREATE OR REPLACE FUNCTION GET_USERS ( USERID IN VARCHAR2 ) RETURN VARCHAR2 AS request VARCHAR2(520) := 'CREATE GLOBAL TEMPORARY TABLE '; BEGIN request := request || 'temp_table_' || userid || '(user_name varchar2(50), user_id varchar2(20), is_administrator varchar2(5)') || ' ON COMMIT PRESERVE ROWS'; EXECUTE IMMEDIATE (request); RETURN 'true'; END GET_USERS;

  Read the article

• C# SQL Data Adapter Fill on existing typed Dataset

  - by René

  I have an option to choose between local based data storing (xml file) or SQL Server based. I already created a long time ago a typed dataset for my application to save data local in the xml file. Now, I have a bool that changes between Server based version and local version. If true my application get the data from the SQL Server. I'm not sure but It seems that Sql Adapter's Fill Method can't fill the Data in my existing schema SqlCommand cmd = new SqlCommand("Select * FROM dbo.Categories WHERE CatUserId = 1", _connection); cmd.CommandType = CommandType.Text; _sqlAdapter = new SqlDataAdapter(cmd); _sqlAdapter.TableMappings.Add("Categories", "dbo.Categories"); _sqlAdapter.Fill(Program.Dataset); This should fill my data from dbo.Categories to Categories (in my local, typed dataset). but it doesn't. It creates a new table with the name "Table". It looks like it can't handle the existing schema. I can't figure it out. Where is the problem? btw. of course the database request I do isn't very useful that way. It's just a simplified version for testing...

  Read the article

• SQL Scenario of allocating ids to user

  - by Enjoy coding

  Hi, I have an sql scenario as follows which I have been trying to improve. There is a table 'Returns' which is having ids of the returned goods against a shop for an item. Its structure is as below. Returns ------------------------- Return ID | Shop | Item ------------------------- 1 Shop1 Item1 2 Shop1 Item1 3 Shop1 Item1 4 Shop1 Item1 5 Shop1 Item1 There is one more table Supplier with Shop, supplier and Item as shown below. Supplier --------------------------------- Supplier | Shop | Item | Volume --------------------------------- supp1 Shop1 Item1 20% supp2 Shop1 Item1 80% Now as you see supp1 is supplying 20 % of total item1 volume and supp2 is supplying 80% of Item1 to shop1. And there were 5 return of items against the same Item1 for same Shop1. Now I need to allocate any four return IDs to Supp1 and remaining one return Id to supp2. This allocation of numbers is based on the ratio of the supplied volume percentage of the supplier. This allocation varies depending on the ratio of volume of supplied items. Now I have tried a method of using RANKs as shown below by use of temp tables. temp table 1 will have Shop, Return Id, Item, Total count of return IDs and Rank of the return id. temp table 2 will have shop, Supplier, Item and his proportion and rank of proportion. Now I am facing the difficulty in allocating top return ids to top supplier as illustrated above. As SQL doesnt have loops how can I achieve this. I have been tying several ways of doing this. Please advice. My environment is Teradata (ANSI SQL is enough). Thanks in advance.

  Read the article

• How to run stored procedures and ad-hoc scripts asynchronously with "loosely" connected SQL Server 2

  - by sanga

  Is there a way to initiate a script against an instance of SQL server when it is not connected then have it run on the instance the next time it connects? This needs to happen without any intervention from me. Background situation if you are interested: We have about 120 machines each with their own instance of SQL Server 2000. Most of them are laptops. We have merge replication set up with each one. From time to time, there is a need to delete "rogue" guids from some tables in some instances that overwrite legitimate records on the main publisher as well as perform administrative tasks via stored procedure or adhoc sql statements. The problem is there is no telling when each machine is going to be connected to the network. Some folks turn their machines completely off at the end of the day. Others disconnect their machines and take them on business trips, home for the weekend etc. Did I mention that about 35 of these machines are in utility trucks and "attempt" to sync over a wireless connection. Thanks in advance for any assistance or suggestions. Sanga

  Read the article

• SQL - Query range between two dates (NON-VBA)

  - by Mohgeroth

  I see various topics on this around stack overflow but none that fit the contect of MS-Access... Given a starting date and an ending date, is there a way through SQL to return records for each given month within the time frame? EG: Between #1/1/2010# and #12/31/2010# results #1/4/2010# #1/11/2010# ..... #12/27/2010# Restrictions MS-Access 2003 :No Case/Loops inside the SQL (IIF statements are good) This is a view only, NO VBA will be used since the data will not be tampered with. Disconnected recordset is my last option. I would prefer to find out theres some way to call your customized functions in the SQL to help return these values... some class stored on a global scope while you iterate through this date range maybe... Is this possible? I see many no's, but if there was a way to pass a value into a function I could find a way to make this work. Sad that I don't have a way to simulate a stored procedure without using a d/c recordset, at least that I know of... any experts out there know a way?

  Read the article

• speed up sql INSERTs

  - by sean717

  I have the following method to insert millions of rows of data into a table (I use SQL 2008) and it seems slow, is there any way to speed up INSERTs? Here is the code snippet - I use MS enterprise library public void InsertHistoricData(List<DataRow> dataRowList) { string sql = string.Format( @"INSERT INTO [MyTable] ([Date],[Open],[High],[Low],[Close],[Volumn]) VALUES( @DateVal, @OpenVal, @High, @Low, @CloseVal, @Volumn )"); DbCommand dbCommand = VictoriaDB.GetSqlStringCommand( sql ); DB.AddInParameter(dbCommand, "DateVal", DbType.Date); DB.AddInParameter(dbCommand, "OpenVal", DbType.Currency); DB.AddInParameter(dbCommand, "High", DbType.Currency ); DB.AddInParameter(dbCommand, "Low", DbType.Currency); DB.AddInParameter(dbCommand, "CloseVal", DbType.Currency); DB.AddInParameter(dbCommand, "Volumn", DbType.Int32); foreach (NasdaqHistoricDataRow dataRow in dataRowList) { DB.SetParameterValue( dbCommand, "DateVal", dataRow.Date ); DB.SetParameterValue( dbCommand, "OpenVal", dataRow.Open ); DB.SetParameterValue( dbCommand, "High", dataRow.High ); DB.SetParameterValue( dbCommand, "Low", dataRow.Low ); DB.SetParameterValue( dbCommand, "CloseVal", dataRow.Close ); DB.SetParameterValue( dbCommand, "Volumn", dataRow.Volumn ); DB.ExecuteNonQuery( dbCommand ); } }

  Read the article

• LINQ to SQL Queries odd Materialization

  - by ptoinson

  I ran across an interesting Linq to SQL, uh, feature, the other day. Perhaps someone can give me a logical explanation for the reasoning behind the results. Take the code below as my example which utilizes the AdventureWorks database setup in a Linq to SQL DataContext. This is a clip from my unit test. The resulting customer returned from a call to both CustomerQuery_Test_01() and CustomerQuery_Test_02() is the same. However, the query executed on the SQLServer are different is a major way. The method CustomerQuery_Test_01 us causing the entire Customer table to be materialized, which the call to CustomerQuery_Test_02 is only causing the single customer to be materialized. The resulting SQL Queries are at the bottom of this post. Anyone have a good reason for this? To me, it was highly non-intuitive. protected virtual Customer GetByPrimaryKey(Func<Customer, bool> keySelection) { AdventureWorksDataContext context = new AdventureWorksDataContext(); return (from r in context.Customers select r).SingleOrDefault(keySelection); } [TestMethod] public void CustomerQuery_Test_01() { Customer customer = GetByPrimaryKey(c => c.CustomerID == 2); } [TestMethod] public void CustomerQuery_Test_02() { AdventureWorksDataContext context = new AdventureWorksDataContext(); Customer customer = (from r in context.Customers select r).SingleOrDefault(c => c.CustomerID == 2); } Query for CustomerQuery_Test_01 (notice the lack of a where clause) SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate] FROM [SalesLT].[Customer] AS [t0] Query for CustomerQuery_Test_02 (notice the where clause) SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate] FROM [SalesLT].[Customer] AS [t0] WHERE [t0].[CustomerID] = @p0

  Read the article

• How do I introspect on a SQL Server?

  - by MetaHyperBolic

  I have a server with a vendor application which is heavily database-reliant. I need to make some minor changes to the data in a few tables in the database in an automated fashion. Just INSERTs and UPDATEs, nothing fancy. Vendors being vendors, I can never be quite sure when they change the schema of a database during upgrade. To that end, how do I ask the SQL server, in some scriptable fashion, "Hey, does this table still exist? Yeah, cool, okay, but does it have this column? What's the data type and size on that? Is it nullable? Could you give me a list of tables? In this table, could you give me a list of columns? Any primary keys there?" I do not need to do this for the whole schema, only part of it, just a quick check of the database before I launch into things. We have Microsoft SQL Server 2005 on it currently, but it might easily move to Microsoft SQL Server 2008. I am probably not using the correct terminology when searching. I do know that ORM is not only too much overhead for this sort of thing, but also that I have no chance of pitching it to my coworkers.

  Read the article

• Is it possible to aggregate over differing where clauses?

  - by BenAlabaster

  Is it possible to calculate multiple aggregates based on differing where clauses? For instance: Let's say I have two tables, one for Invoice and one for InvoiceLineItems. The invoice table has a total field for the invoice total, and each of the invoice line item records in the InvoiceLineItems table contains a field that denotes whether the line item is discountable or not. I want three sum totals, one where Discountable = 0 and one where Discountable = 1 and one where Discountable is irrelevant. Such that my output would be: InvoiceNumber Total DiscountableTotal NonDiscountableTotal ------------- ----- ----------------- -------------------- 1 53.27 27.27 16.00 2 38.94 4.76 34.18 3... The only way I've found so far is by using something like: Select i.InvoiceNumber, i.Total, t0.Total As DiscountableTotal, t1.Total As NonDiscountableTotal From Invoices i Left Join ( Select InvoiceNumber, Sum(Amount), From InvoiceLineItems Where Discountable = 0 Group By InvoiceNumber ) As t0 On i.InvoiceNumber = t0.InvoiceNumber Left Join ( Select InvoiceNumber, Sum(Amount) From InvoiceLineItems Where Discountable = 1 Group By InvoiceNumber ) As t1 On i.InvoiceNumber = t1.InvoiceNumber This seems somewhat cumbersome, it would be nice if I could do something like: Select InvoiceNumber, Sum(Amount) Where Discountable = 1 As Discountable Sum(Amount) Where Discountable = 0 As NonDiscountable Group By InvoiceNumber I realize that SQL is completely invalid, but it logically portrays what I'm trying to do... TIA P.S. I need this to run on a SQL Server 2000 instance, but I am also interested (for future reference) if/how I would achieve this on SQL Server 2005/2008.

  Read the article

• When to use CTEs to encapsulate sub-results, and when to let the RDBMS worry about massive joins.

  - by IanC

  This is a SQL theory question. I can provide an example, but I don't think it's needed to make my point. Anyone experienced with SQL will immediately know what I'm talking about. Usually we use joins to minimize the number of records due to matching the left and right rows. However, under certain conditions, joining tables cause a multiplication of results where the result is all permutations of the left and right records. I have a database which has 3 or 4 such joins. This turns what would be a few records into a multitude. My concern is that the tables will be large in production, so the number of these joined rows will be immense. Further, heavy math is performed on each row, and the idea of performing math on duplicate rows is enough to make anyone shudder. I have two questions. The first is, is this something I should care about, or will SQL Server intelligently realize these rows are all duplicates and optimize all processing accordingly? The second is, is there any advantage to grouping each part of the query so as to get only the distinct values going into the next part of the query, using something like: WITH t1 AS ( SELECT DISTINCT... [or GROUP BY] ), t2 AS ( SELECT DISTINCT... ), t3 AS ( SELECT DISTINCT... ) SELECT... I have often seen the use of DISTINCT applied to subqueries. There is obviously a reason for doing this. However, I'm talking about something a little different and perhaps more subtle and tricky.

  Read the article

• Counting character count in Access database column ins SQL

  - by jzr

  Good Evening. My problem is possibly very easy, I just have spent some time researching now and probably have a brain lock and unable to solve this, help would be much appreciated. database structure: col1 col2 col3 col4 ==================== 1233+4566+ABCD+CDEF 1233+4566+ACD1+CDEF 1233+4566+D1AF+CDEF I need to count character count in col3, wanted result in from the previous table would be: char count =========== A 3 B 1 C 2 D 3 F 1 1 2 is this possible to achieve by using SQL only? at the moment I am thinking of passing a parameter in to SQL query and count the characters one by one and then sum, however I did not start the VBA part yet, and frankly wouldn't want to do that. this is my query at the moment: PARAMETERS X Long; SELECT First(Mid(TABLE.col3,X,1)) AS [col3 Field], Count(Mid(TABLE.col3,X,1)) AS Dcount FROM TEST GROUP BY Mid(TABLE.col3,X,1) HAVING (((Count(Mid([TABLE].[col3],[X],1)))>=1)); ideas and help are much appreciated, as being said this is probably very for some of your guys, I don't usually work with access and SQL. Thanks.

  Read the article

• In sync query calls, one query causing other query to run slower. Why?

  - by Irchi

  Sorry for the long question, but I think this is an interesting situation and I couldn't find any explanations for it: I was involved in optimization of an application that performed a large number of sequential SELECT and INSERT statements on a single dedicated SQL Server database. The process needs to INSERT a large number of records into a table, but for each of them there should be some value mappings, which performed using SELECT statements on another table in the same database. For a specific execution, it took 90 minutes to run. I used a profiler (JProfiler - the application is Java-based) to determine how much time does each part of the application take. It yields that 60% of the time was spent on INSERT method calls, and almost 20% on SELECT calls (the rest distributed in other parts). After some trials, I came to this situation: I commented out the INSERT query that took 60% of the time. I was expecting for the total run time to be around 35 minutes, as I have removed 60% of the 90 minutes. But the whole process took the same 90 minutes (doing only SELECTs and nothing else), but each SELECT took longer this time! Everything was running sync, there were no async calls. And there was only one single thread of execution. SELECT and INSERT queries are very simple, and don't have anything special, and they are on different tables, but on the same DB. I tested with both the DB on the application machine, and on a remote network machine. I can't think of any explanation for this, as the Profiler (Application profiler, not SQL Profiler) reported the changes in the method call times, and by removing INSERT statements SELECT statements took longer to run. Can anyone give me some kind of explanation of what could have happened? (there can't be cache / query optimization stuff, because the queries were run in sync, and in a single thread, and it was far from affecting the cache this much) I should note that the bottleneck of the speed was in SQL server, using most of the CPU time.

  Read the article

• Access to SQL Server 2005 from a non-domain machine using Windows authentication

  - by user304582

  Hi, I have a Windows domain within which a machine is running SQL Server 2005 and which is configured to support only Windows authentication. I would like to run a C# client application on a machine on the same network, but which is NOT on the domain, and access a database on the SQL Server 2005 instance. I thought that it would be a simple matter of doing something like this: string connectionString = "Data Source=server;Initial Catalog=database;User Id=domain\user;Password=password"; SqlConnection connection = new SqlConnection(connectionString); connection.Open(); However, this fails: the client-side error is: System.Data.SqlClient.SqlException: Login failed for user 'domain\user' and the server-side error is: Error 18456, Severity 14, State 5 I have tried various things including setting integrated security to true and false, and \ instead of \ in the User Id, but without success. In general, I know that it possible to connect to the SQL Server 2005 instance from a non-domain machine (for example, I am working with a Linux-based application which happily does this), but I don't seem to be able to work out how to do it from a Windows machine. Help would be appreciated! Thanks, Martin

  Read the article

• SQL Server 2008, Books Online, and old documentation...

  - by Chris J

  [I have no idea if stackoverflow really is right right place for this, but don't know how many devs on here run into msi issues with SQL Server; suggest SuperUser or ServerFault if folk think it's better on either of those] About a year ago, when we were looking at moving our codebase forward and migrating to SQL Server 2008, I pulled down a copy of Books Online from the MSDN. Reviewed, did background research, fed results upstream, grabbed Express and tinkered with that. Then we got the nod to move forward (hurrah!) this past couple of weeks. So armed with Developer Edition, and running through the install, I've since found out I've zapped the Books Online MSI, no-ones got a copy of it, and Microsoft only have a later version (Oct 2009) available, so damned if I can update my SQL Server fully and properly... {mutter grumble}. Does anyone know if old versions of Books Online are available for download anywhere? Poking around the Microsoft download centre can't find it, neither is my google-fu finding it. For reference, I'm looking for SQLServer2008_BOL_August2008_ENU.msi ... This may just be a case of good ol' manual delete the files and (try) and clean up the registry :-(

  Read the article

• Windows Azure Root CAs and SSL Client Certificates

  - by Your DisplayName here!

  I ran into some problems while trying to make SSL client certificates work for StarterSTS 1.5. In theory you have to do two things (via startup tasks): Unlock the SSL section in IIS Install all the root certificates for the client certs you want to accept I did that. But it still does not work. While inspecting the event log, I stumbled over an schannel error message that I’ve never seen before: “When asking for client authentication, this server sends a list of trusted certificate authorities to the client. The client uses this list to choose a client certificate that is trusted by the server. Currently, this server trusts so many certificate authorities that the list has grown too long. This list has thus been truncated. The administrator of this machine should review the certificate authorities trusted for client authentication and remove those that do not really need to be trusted.” WTF? And indeed standard Azure (web role) VMs trust 275 root CAs (see attached list). Including kinda obscure ones. I don’t really know why MS made this design decision. It seems just wrong (including breaking the SSL client cert functionality). Deleting like 60% of them made SSL client certs from my CA work. So I guess I now have to find an automated way to attach CTLs to my site…joy. Exported list of trusted CA (as of 30th Dec 2010) AC Raíz Certicámara S.A. (4/2/2030 9:42:02 PM) AC RAIZ FNMT-RCM (1/1/2030 12:00:00 AM) A-CERT ADVANCED (10/23/2011 2:14:14 PM) Actalis Authentication CA G1 (6/25/2022 2:06:00 PM) Agence Nationale de Certification Electronique (8/12/2037 9:03:17 AM) Agence Nationale de Certification Electronique (8/12/2037 9:58:14 AM) Agencia Catalana de Certificacio (NIF Q-0801176-I) (1/7/2031 10:59:59 PM) America Online Root Certification Authority 1 (11/19/2037 8:43:00 PM) America Online Root Certification Authority 2 (9/29/2037 2:08:00 PM) ANCERT Certificados CGN (2/11/2024 5:27:12 PM) ANCERT Certificados Notariales (2/11/2024 3:58:26 PM) ANCERT Corporaciones de Derecho Publico (2/11/2024 5:22:45 PM) A-Trust-nQual-01 (11/30/2014 11:00:00 PM) A-Trust-nQual-03 (8/17/2015 10:00:00 PM) A-Trust-Qual-01 (11/30/2014 11:00:00 PM) A-Trust-Qual-02 (12/2/2014 11:00:00 PM) A-Trust-Qual-03a (4/24/2018 10:00:00 PM) Austria Telekom-Control Kommission (9/24/2005 12:40:00 PM) Austrian Society for Data Protection (2/12/2009 11:30:30 AM) Austrian Society for Data Protection GLOBALTRUST Certification Service (9/18/2036 2:12:35 PM) Autoridad Certificadora Raiz de la Secretaria de Economia (5/9/2025 12:00:00 AM) Autoridad de Certificacion de la Abogacia (6/13/2030 10:00:00 PM) Autoridad de Certificacion Firmaprofesional CIF A62634068 (10/24/2013 10:00:00 PM) Autoridade Certificadora Raiz Brasileira (11/30/2011 11:59:00 PM) Baltimore CyberTrust Root (5/12/2025 11:59:00 PM) BIT AdminCA-CD-T01 (1/25/2016 12:36:19 PM) BIT Admin-Root-CA (11/10/2021 7:51:07 AM) Buypass Class 2 CA 1 (10/13/2016 10:25:09 AM) Buypass Class 3 CA 1 (5/9/2015 2:13:03 PM) CA Disig (3/22/2016 1:39:34 AM) CertEurope (3/27/2037 11:00:00 PM) CERTICAMARA S.A. (2/23/2015 5:10:37 PM) Certicámara S.A. (5/23/2011 10:00:00 PM) Certigna (6/29/2027 3:13:05 PM) Certipost E-Trust Primary Normalised CA (7/26/2020 10:00:00 AM) Certipost E-Trust Primary Qualified CA (7/26/2020 10:00:00 AM) Certipost E-Trust Primary TOP Root CA (7/26/2025 10:00:00 AM) Certisign Autoridade Certificadora AC1S (6/27/2018 12:00:00 AM) Certisign Autoridade Certificadora AC2 (6/27/2018 12:00:00 AM) Certisign Autoridade Certificadora AC3S (7/9/2018 8:56:32 PM) Certisign Autoridade Certificadora AC4 (6/27/2018 12:00:00 AM) CertPlus Class 1 Primary CA (7/6/2020 11:59:59 PM) CertPlus Class 2 Primary CA (7/6/2019 11:59:59 PM) CertPlus Class 3 Primary CA (7/6/2019 11:59:59 PM) CertPlus Class 3P Primary CA (7/6/2019 11:59:59 PM) CertPlus Class 3TS Primary CA (7/6/2019 11:59:59 PM) CertRSA01 (3/3/2010 2:59:59 PM) certSIGN Root CA (7/4/2031 5:20:04 PM) Certum (6/11/2027 10:46:39 AM) Certum Trusted Network CA (12/31/2029 12:07:37 PM) Chambers of Commerce Root - 2008 (7/31/2038 12:29:50 PM) Chambersign Chambers of Commerce Root (9/30/2037 4:13:44 PM) Chambersign Global Root (9/30/2037 4:14:18 PM) Chambersign Public Notary Root (9/30/2037 4:14:49 PM) Chunghwa Telecom Co. Ltd. (12/20/2034 2:31:27 AM) Cisco Systems (5/14/2029 8:25:42 PM) CNNIC Root (4/16/2027 7:09:14 AM) Common Policy (10/15/2027 4:08:00 PM) COMODO (12/31/2028 11:59:59 PM) COMODO (1/18/2038 11:59:59 PM) COMODO (12/31/2029 11:59:59 PM) ComSign Advanced Security CA (3/24/2029 9:55:55 PM) ComSign CA (3/19/2029 3:02:18 PM) ComSign Secured CA (3/16/2029 3:04:56 PM) Correo Uruguayo - Root CA (12/31/2030 2:59:59 AM) Cybertrust Global Root (12/15/2021 8:00:00 AM) DanID (2/11/2037 9:09:30 AM) DanID (4/5/2021 5:03:17 PM) Deutsche Telekom Root CA 2 (7/9/2019 11:59:00 PM) DigiCert (11/10/2031 12:00:00 AM) DigiCert (11/10/2031 12:00:00 AM) DigiCert (11/10/2031 12:00:00 AM) DigiNotar Root CA (3/31/2025 6:19:21 PM) DIRECCION GENERAL DE LA POLICIA (2/8/2036 10:59:59 PM) DST (ABA.ECOM) CA (7/9/2009 5:33:53 PM) DST (ANX Network) CA (12/9/2018 4:16:48 PM) DST (Baltimore EZ) CA (7/3/2009 7:56:53 PM) DST (National Retail Federation) RootCA (12/8/2008 4:14:16 PM) DST (United Parcel Service) RootCA (12/7/2008 12:25:46 AM) DST ACES CA X6 (11/20/2017 9:19:58 PM) DST Root CA X3 (9/30/2021 2:01:15 PM) DST RootCA X1 (11/28/2008 6:18:55 PM) DST RootCA X2 (11/27/2008 10:46:16 PM) DSTCA E1 (12/10/2018 6:40:23 PM) DSTCA E2 (12/9/2018 7:47:26 PM) DST-Entrust GTI CA (12/9/2018 12:32:24 AM) D-TRUST GmbH (5/16/2022 5:20:47 AM) D-TRUST GmbH (6/8/2012 11:47:46 AM) D-TRUST GmbH (5/16/2022 5:20:47 AM) EBG Elektronik Sertifika Hizmet Saglayicisi (8/14/2016 12:31:09 AM) E-Certchile (9/5/2028 7:39:41 PM) Echoworx Root CA2 (10/7/2030 10:49:13 AM) ECRaizEstado (6/23/2030 1:41:27 PM) EDICOM (4/13/2028 4:24:22 PM) E-GÜVEN Elektronik Sertifika Hizmet Saglayicisi (1/4/2017 11:32:48 AM) E-ME SSI (RCA) (5/19/2027 8:48:15 AM) Entrust (11/27/2026 8:53:42 PM) Entrust (5/25/2019 4:39:40 PM) Entrust.net (12/7/2030 5:55:54 PM) Equifax Secure eBusiness CA-1 (6/21/2020 4:00:00 AM) Equifax Secure eBusiness CA-2 (6/23/2019 12:14:45 PM) Equifax Secure Global eBusiness CA-1 (6/21/2020 4:00:00 AM) eSign Australia: eSign Imperito Primary Root CA (5/23/2012 11:59:59 PM) eSign Australia: Gatekeeper Root CA (5/23/2014 11:59:59 PM) eSign Australia: Primary Utility Root CA (5/23/2012 11:59:59 PM) Fabrica Nacional de Moneda y Timbre (3/18/2019 3:26:19 PM) GeoTrust (8/22/2018 4:41:51 PM) GeoTrust (7/16/2036 11:59:59 PM) GeoTrust Global CA (5/21/2022 4:00:00 AM) GeoTrust Global CA 2 (3/4/2019 5:00:00 AM) GeoTrust Primary Certification Authority - G2 (1/18/2038 11:59:59 PM) GeoTrust Primary Certification Authority - G3 (12/1/2037 11:59:59 PM) GeoTrust Universal CA (3/4/2029 5:00:00 AM) GeoTrust Universal CA 2 (3/4/2029 5:00:00 AM) Global Chambersign Root - 2008 (7/31/2038 12:31:40 PM) GlobalSign (1/28/2028 12:00:00 PM) GlobalSign (12/15/2021 8:00:00 AM) Go Daddy Class 2 Certification Authority (6/29/2034 5:06:20 PM) GTE CyberTrust Global Root (8/13/2018 11:59:00 PM) GTE CyberTrust Root (4/3/2004 11:59:00 PM) GTE CyberTrust Root (2/23/2006 11:59:00 PM) Halcom CA FO (6/5/2020 10:33:31 AM) Halcom CA PO 2 (2/7/2019 6:33:31 PM) Hongkong Post Root CA (1/16/2010 11:59:00 PM) Hongkong Post Root CA 1 (5/15/2023 4:52:29 AM) I.CA První certifikacní autorita a.s. (4/1/2018 12:00:00 AM) I.CA První certifikacní autorita a.s. (4/1/2018 12:00:00 AM) InfoNotary (3/6/2026 5:33:05 PM) IPS SERVIDORES (12/29/2009 11:21:07 PM) IZENPE S.A. (1/30/2018 11:00:00 PM) Izenpe.com (12/13/2037 8:27:25 AM) Japan Certification Services, Inc. SecureSign RootCA1 (9/15/2020 2:59:59 PM) Japan Certification Services, Inc. SecureSign RootCA11 (4/8/2029 4:56:47 AM) Japan Certification Services, Inc. SecureSign RootCA2 (9/15/2020 2:59:59 PM) Japan Certification Services, Inc. SecureSign RootCA3 (9/15/2020 2:59:59 PM) Japan Local Government PKI Application CA (3/31/2016 2:59:59 PM) Japanese Government ApplicationCA (12/12/2017 3:00:00 PM) Juur-SK AS Sertifitseerimiskeskus (8/26/2016 2:23:01 PM) KamuSM (8/21/2017 11:37:07 AM) KISA RootCA 1 (8/24/2025 8:05:46 AM) KISA RootCA 3 (11/19/2014 6:39:51 AM) Macao Post eSignTrust (1/29/2013 11:59:59 PM) MicroSec e-Szigno Root CA (4/6/2017 12:28:44 PM) Microsoft Authenticode(tm) Root (12/31/1999 11:59:59 PM) Microsoft Root Authority (12/31/2020 7:00:00 AM) Microsoft Root Certificate Authority (5/9/2021 11:28:13 PM) Microsoft Timestamp Root (12/30/1999 11:59:59 PM) MOGAHA Govt of Korea (4/21/2012 9:07:23 AM) MOGAHA Govt of Korea GPKI (3/15/2017 6:00:04 AM) NetLock Arany (Class Gold) Fotanúsítvány (12/6/2028 3:08:21 PM) NetLock Expressz (Class C) Tanusitvanykiado (2/20/2019 2:08:11 PM) NetLock Kozjegyzoi (Class A) Tanusitvanykiado (2/19/2019 11:14:47 PM) NetLock Minositett Kozjegyzoi (Class QA) Tanusitvanykiado (12/15/2022 1:47:11 AM) NetLock Platina (Class Platinum) Fotanúsítvány (12/6/2028 3:12:44 PM) NetLock Uzleti (Class B) Tanusitvanykiado (2/20/2019 2:10:22 PM) Netrust CA1 (3/30/2021 2:57:45 AM) Network Solutions (12/31/2029 11:59:59 PM) NLB Nova Ljubljanska Banka d.d. Ljubljana (5/15/2023 12:22:45 PM) OISTE WISeKey Global Root GA CA (12/11/2037 4:09:51 PM) Post.Trust Root CA (7/5/2022 9:12:33 AM) Post.Trust Root CA (8/20/2010 1:56:21 PM) Posta CA Root (10/20/2028 12:52:08 PM) POSTarCA (2/7/2023 11:06:58 AM) QuoVadis Root CA 2 (11/24/2031 6:23:33 PM) QuoVadis Root CA 3 (11/24/2031 7:06:44 PM) QuoVadis Root Certification Authority (3/17/2021 6:33:33 PM) Root CA Generalitat Valenciana (7/1/2021 3:22:47 PM) RSA Security 2048 V3 (2/22/2026 8:39:23 PM) SECOM Trust Systems CO LTD (6/6/2037 2:12:32 AM) SECOM Trust Systems CO LTD (6/25/2019 10:23:48 PM) SECOM Trust Systems CO LTD (9/30/2023 4:20:49 AM) Secretaria de Economia Mexico (5/8/2025 12:00:00 AM) Secrétariat Général de la Défense Nationale (10/17/2020 2:29:22 PM) SecureNet CA Class B (10/16/2009 9:59:00 AM) Serasa Certificate Authority I (11/21/2024 2:12:45 PM) Serasa Certificate Authority II (11/21/2024 12:44:48 PM) Serasa Certificate Authority III (11/21/2024 1:24:14 PM) SERVICIOS DE CERTIFICACION - A.N.C. (3/9/2009 9:08:07 PM) Sigen-CA (6/29/2021 9:57:46 PM) Sigov-CA (1/10/2021 2:22:52 PM) Skaitmeninio sertifikavimo centras (12/28/2026 12:05:04 PM) Skaitmeninio sertifikavimo centras (12/25/2026 12:08:26 PM) Skaitmeninio sertifikavimo centras (12/22/2026 12:11:30 PM) Sonera Class1 CA (4/6/2021 10:49:13 AM) Sonera Class2 CA (4/6/2021 7:29:40 AM) Spanish Property & Commerce Registry CA (4/27/2012 9:39:50 AM) Staat der Nederlanden Root CA (12/16/2015 9:15:38 AM) Staat der Nederlanden Root CA - G2 (3/25/2020 11:03:10 AM) Starfield Class 2 Certification Authority (6/29/2034 5:39:16 PM) Starfield Technologies (6/26/2019 12:19:54 AM) Starfield Technologies Inc. (12/31/2029 11:59:59 PM) StartCom Certification Authority (9/17/2036 7:46:36 PM) S-TRUST Authentication and Encryption Root CA 2005:PN (6/21/2030 11:59:59 PM) Swisscom Root CA 1 (8/18/2025 10:06:20 PM) SwissSign (10/25/2036 8:30:35 AM) SwissSign Platinum G2 Root CA (10/25/2036 8:36:00 AM) SwissSign Silver G2 Root CA (10/25/2036 8:32:46 AM) TC TrustCenter Class 1 CA (1/1/2011 11:59:59 AM) TC TrustCenter Class 2 CA (1/1/2011 11:59:59 AM) TC TrustCenter Class 2 CA II (12/31/2025 10:59:59 PM) TC TrustCenter Class 3 CA (1/1/2011 11:59:59 AM) TC TrustCenter Class 3 CA II (12/31/2025 10:59:59 PM) TC TrustCenter Class 4 CA (1/1/2011 11:59:59 AM) TC TrustCenter Class 4 CA II (12/31/2025 10:59:59 PM) TC TrustCenter Time Stamping CA (1/1/2011 11:59:59 AM) TC TrustCenter Universal CA I (12/31/2025 10:59:59 PM) TC TrustCenter Universal CA II (12/31/2030 10:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte (7/16/2036 11:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte (12/31/2020 11:59:59 PM) thawte Primary Root CA - G2 (1/18/2038 11:59:59 PM) thawte Primary Root CA - G3 (12/1/2037 11:59:59 PM) Thawte Timestamping CA (12/31/2020 11:59:59 PM) Trustis EVS Root CA (1/9/2027 11:56:00 AM) Trustis FPS Root CA (1/21/2024 11:36:54 AM) Trustwave (1/1/2035 5:37:19 AM) Trustwave (12/31/2029 7:40:55 PM) Trustwave (12/31/2029 7:52:06 PM) TURKTRUST Elektronik Islem Hizmetleri (9/16/2015 12:13:05 PM) TURKTRUST Elektronik Islem Hizmetleri (3/22/2015 10:04:51 AM) TURKTRUST Elektronik Sertifika Hizmet Saglayicisi (9/16/2015 10:07:57 AM) TURKTRUST Elektronik Sertifika Hizmet Saglayicisi (3/22/2015 10:27:17 AM) TÜRKTRUST Elektronik Sertifika Hizmet Saglayicisi (12/22/2017 6:37:19 PM) TW Government Root Certification Authority (12/5/2032 1:23:33 PM) TWCA Root Certification Authority 1 (12/31/2030 3:59:59 PM) TWCA Root Certification Authority 2 (12/31/2030 3:59:59 PM) U.S. Government FBCA (10/6/2010 6:53:56 PM) UCA Global Root (12/31/2037 12:00:00 AM) UCA Root (12/31/2029 12:00:00 AM) USERTrust (7/9/2019 6:40:36 PM) USERTrust (7/9/2019 5:36:58 PM) USERTrust (6/24/2019 7:06:30 PM) USERTrust (7/9/2019 6:19:22 PM) USERTrust (5/30/2020 10:48:38 AM) UTN - USERFirst-Network Applications (7/9/2019 6:57:49 PM) ValiCert Class 3 Policy Validation Authority (6/26/2019 12:22:33 AM) VAS Latvijas Pasts SSI(RCA) (9/13/2024 9:27:57 AM) VeriSign (5/18/2018 11:59:59 PM) VeriSign (7/16/2036 11:59:59 PM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (12/31/1999 9:37:48 AM) VeriSign (1/7/2004 11:59:59 PM) VeriSign (5/18/2018 11:59:59 PM) VeriSign (1/7/2004 11:59:59 PM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (1/7/2020 11:59:59 PM) VeriSign (12/31/1999 9:35:58 AM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (7/16/2036 11:59:59 PM) VeriSign (1/7/2004 11:59:59 PM) VeriSign (7/16/2036 11:59:59 PM) VeriSign (1/7/2010 11:59:59 PM) VeriSign (5/18/2018 11:59:59 PM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (1/7/2004 11:59:59 PM) VeriSign (7/16/2036 11:59:59 PM) VeriSign (7/16/2036 11:59:59 PM) VeriSign (8/1/2028 11:59:59 PM) VeriSign (5/18/2018 11:59:59 PM) VeriSign Class 3 Public Primary CA (8/1/2028 11:59:59 PM) VeriSign Class 3 Public Primary Certification Authority - G4 (1/18/2038 11:59:59 PM) VeriSign Time Stamping CA (1/7/2004 11:59:59 PM) VeriSign Universal Root Certification Authority (12/1/2037 11:59:59 PM) Visa eCommerce Root (6/24/2022 12:16:12 AM) Visa Information Delivery Root CA (6/29/2025 5:42:42 PM) VRK Gov. Root CA (12/18/2023 1:51:08 PM) Wells Fargo Root Certificate Authority (1/14/2021 4:41:28 PM) WellsSecure Public Certificate Authority (12/14/2022 12:07:54 AM) Xcert EZ by DST (7/11/2009 4:14:18 PM)

  Read the article

• Performance considerations for common SQL queries

  - by Jim Giercyk

  Originally posted on: http://geekswithblogs.net/NibblesAndBits/archive/2013/10/16/performance-considerations-for-common-sql-queries.aspxSQL offers many different methods to produce the same results.  There is a never-ending debate between SQL developers as to the “best way” or the “most efficient way” to render a result set.  Sometimes these disputes even come to blows….well, I am a lover, not a fighter, so I decided to collect some data that will prove which way is the best and most efficient.  For the queries below, I downloaded the test database from SQLSkills:  http://www.sqlskills.com/sql-server-resources/sql-server-demos/.  There isn’t a lot of data, but enough to prove my point: dbo.member has 10,000 records, and dbo.payment has 15,554.  Our result set contains 6,706 records. The following queries produce an identical result set; the result set contains aggregate payment information for each member who has made more than 1 payment from the dbo.payment table and the first and last name of the member from the dbo.member table.   /*************/ /* Sub Query  */ /*************/ SELECT  a.[Member Number] ,         m.lastname ,         m.firstname ,         a.[Number Of Payments] ,         a.[Average Payment] ,         a.[Total Paid] FROM    ( SELECT    member_no 'Member Number' ,                     AVG(payment_amt) 'Average Payment' ,                     SUM(payment_amt) 'Total Paid' ,                     COUNT(Payment_No) 'Number Of Payments'           FROM      dbo.payment           GROUP BY  member_no           HAVING    COUNT(Payment_No) > 1         ) a         JOIN dbo.member m ON a.[Member Number] = m.member_no         /***************/ /* Cross Apply  */ /***************/ SELECT  ca.[Member Number] ,         m.lastname ,         m.firstname ,         ca.[Number Of Payments] ,         ca.[Average Payment] ,         ca.[Total Paid] FROM    dbo.member m         CROSS APPLY ( SELECT    member_no 'Member Number' ,                                 AVG(payment_amt) 'Average Payment' ,                                 SUM(payment_amt) 'Total Paid' ,                                 COUNT(Payment_No) 'Number Of Payments'                       FROM      dbo.payment                       WHERE     member_no = m.member_no                       GROUP BY  member_no                       HAVING    COUNT(Payment_No) > 1                     ) ca /********/                    /* CTEs  */ /********/ ; WITH    Payments           AS ( SELECT   member_no 'Member Number' ,                         AVG(payment_amt) 'Average Payment' ,                         SUM(payment_amt) 'Total Paid' ,                         COUNT(Payment_No) 'Number Of Payments'                FROM     dbo.payment                GROUP BY member_no                HAVING   COUNT(Payment_No) > 1              ),         MemberInfo           AS ( SELECT   p.[Member Number] ,                         m.lastname ,                         m.firstname ,                         p.[Number Of Payments] ,                         p.[Average Payment] ,                         p.[Total Paid]                FROM     dbo.member m                         JOIN Payments p ON m.member_no = p.[Member Number]              )     SELECT  *     FROM    MemberInfo /************************/ /* SELECT with Grouping   */ /************************/ SELECT  p.member_no 'Member Number' ,         m.lastname ,         m.firstname ,         COUNT(Payment_No) 'Number Of Payments' ,         AVG(payment_amt) 'Average Payment' ,         SUM(payment_amt) 'Total Paid' FROM    dbo.payment p         JOIN dbo.member m ON m.member_no = p.member_no GROUP BY p.member_no ,         m.lastname ,         m.firstname HAVING  COUNT(Payment_No) > 1   We can see what is going on in SQL’s brain by looking at the execution plan.  The Execution Plan will demonstrate which steps and in what order SQL executes those steps, and what percentage of batch time each query takes.  SO….if I execute all 4 of these queries in a single batch, I will get an idea of the relative time SQL takes to execute them, and how it renders the Execution Plan.  We can settle this once and for all.  Here is what SQL did with these queries:   Not only did the queries take the same amount of time to execute, SQL generated the same Execution Plan for each of them.  Everybody is right…..I guess we can all finally go to lunch together!  But wait a second, I may not be a fighter, but I AM an instigator.     Let’s see how a table variable stacks up.  Here is the code I executed: /********************/ /*  Table Variable  */ /********************/ DECLARE @AggregateTable TABLE     (       member_no INT ,       AveragePayment MONEY ,       TotalPaid MONEY ,       NumberOfPayments MONEY     ) INSERT  @AggregateTable         SELECT  member_no 'Member Number' ,                 AVG(payment_amt) 'Average Payment' ,                 SUM(payment_amt) 'Total Paid' ,                 COUNT(Payment_No) 'Number Of Payments'         FROM    dbo.payment         GROUP BY member_no         HAVING  COUNT(Payment_No) > 1   SELECT  at.member_no 'Member Number' ,         m.lastname ,         m.firstname ,         at.NumberOfPayments 'Number Of Payments' ,         at.AveragePayment 'Average Payment' ,         at.TotalPaid 'Total Paid' FROM    @AggregateTable at         JOIN dbo.member m ON m.member_no = at.member_no In the interest of keeping things in groupings of 4, I removed the last query from the previous batch and added the table variable query.  Here’s what I got:     Since we first insert into the table variable, then we read from it, the Execution Plan renders 2 steps.  BUT, the combination of the 2 steps is only 22% of the batch.  It is actually faster than the other methods even though it is treated as 2 separate queries in the Execution Plan.  The argument I often hear against Table Variables is that SQL only estimates 1 row for the table size in the Execution Plan.  While this is true, the estimate does not come in to play until you read from the table variable.  In this case, the table variable had 6,706 rows, but it still outperformed the other queries.  People argue that table variables should only be used for hash or lookup tables.  The fact is, you have control of what you put IN to the variable, so as long as you keep it within reason, these results suggest that a table variable is a viable alternative to sub-queries. If anyone does volume testing on this theory, I would be interested in the results.  My suspicion is that there is a breaking point where efficiency goes down the tubes immediately, and it would be interesting to see where the threshold is. Coding SQL is a matter of style.  If you’ve been around since they introduced DB2, you were probably taught a little differently than a recent computer science graduate.  If you have a company standard, I strongly recommend you follow it.    If you do not have a standard, generally speaking, there is no right or wrong answer when talking about the efficiency of these types of queries, and certainly no hard-and-fast rule.  Volume and infrastructure will dictate a lot when it comes to performance, so your results may vary in your environment.  Download the database and try it!

  Read the article

• Upgrading SSIS Custom Components for SQL Server 2012

  Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file. The high level steps are listed below, followed by some more details. Create a 2012 copy of the project file Upgrade project, just open the new project file is VS2010 Change target framework to .NET 4.0 Set conditional compilation symbol for DENALI Change any conditional code, including assembly version and UI type name Edit project file to change referenced assemblies for 2012 Change target framework to .NET 4.0 Open the project properties. On the Applications page, change the Target framework to .NET Framework 4. Set conditional compilation symbol for DENALI Re-open the project properties. On the Build tab, first change the Configuration to All Configurations, then set a Conditional compilation symbol of DENALI. Change any conditional code, including assembly version and UI type name The value doesn’t have to be DENALI, it can actually be anything you like, that is just what I use. It is how I control sections of code that vary between versions. There were several API changes between 2005 and 2008, as well as interface name changes. Whilst we don’t have the same issues between 2008 and 2012, I still have some sections of code that do change such as the assembly attributes. #if DENALI [assembly: AssemblyDescription("Data Generator Source for SQL Server Integration Services 2012")] [assembly: AssemblyCopyright("Copyright © 2012 Konesans Ltd")] [assembly: AssemblyVersion("3.0.0.0")] #else [assembly: AssemblyDescription("Data Generator Source for SQL Server Integration Services 2008")] [assembly: AssemblyCopyright("Copyright © 2008 Konesans Ltd")] [assembly: AssemblyVersion("2.0.0.0")] #endif The Visual Studio editor automatically formats the code based on the current compilation symbols, hence in this case the 2008 code is grey to indicate it is disabled. As you can see in the previous example I have distinct assembly version attributes, ensuring I can run both 2008 and 2012 versions of my component side by side. For custom components with a user interface, be sure to update the UITypeName property of the DtsTask or DtsPipelineComponent attributes. As above I use the conditional compilation symbol to control the code. #if DENALI [DtsTask ( DisplayName = "File Watcher Task", Description = "File Watcher Task", IconResource = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask.ico", UITypeName = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTaskUI,Konesans.Dts.Tasks.FileWatcherTask,Version=3.0.0.0,Culture=Neutral,PublicKeyToken=b2ab4a111192992b", TaskContact = "File Watcher Task; Konesans Ltd; Copyright © 2012 Konesans Ltd; http://www.konesans.com" )] #else [DtsTask ( DisplayName = "File Watcher Task", Description = "File Watcher Task", IconResource = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask.ico", UITypeName = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTaskUI,Konesans.Dts.Tasks.FileWatcherTask,Version=2.0.0.0,Culture=Neutral,PublicKeyToken=b2ab4a111192992b", TaskContact = "File Watcher Task; Konesans Ltd; Copyright © 2004-2008 Konesans Ltd; http://www.konesans.com" )] #endif public sealed class FileWatcherTask: Task, IDTSComponentPersist, IDTSBreakpointSite, IDTSSuspend { // .. code goes on... } Shown below is another example I found that needed changing. I borrow one of the MS editors, and use it against a custom property, but need to ensure I reference the correct version of the MS controls assembly. This section of code is actually shared between the 2005, 2008 and 2012 versions of my component hence it has test for both DENALI and KATMAI symbols. #if DENALI const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=11.0.00.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; #elif KATMAI const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; #else const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; #endif // Create Match Expression parameter IDTSCustomPropertyCollection100 propertyCollection = outputColumn.CustomPropertyCollection; IDTSCustomProperty100 property = propertyCollection.New(); property = propertyCollection.New(); property.Name = MatchParams.Name; property.Description = MatchParams.Description; property.TypeConverter = typeof(MultilineStringConverter).AssemblyQualifiedName; property.UITypeEditor = multiLineUI; property.Value = MatchParams.DefaultValue; Edit project file to change referenced assemblies for 2012 We now need to edit the project file itself. Open the MyComponente2012.cproj  in you favourite text editor, and then perform a couple of find and replaces as listed below: Find Replace Comment Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Change the assembly references version from SQL Server 2008 to SQL Server 2012. Microsoft SQL Server\100\ Microsoft SQL Server\110\ Change any assembly reference hint path locations from from SQL Server 2008 to SQL Server 2012. If you use any Build Events during development, such as copying the component assembly to the DTS folder, or calling GACUTIL to install it into the GAC, you can also change these now. An example of my new post-build event for a pipeline component is shown below, which uses the .NET 4.0 path for GACUTIL. It also uses the 110 folder location, instead of 100 for SQL Server 2008, but that was covered the the previous find and replace. "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe" /if "$(TargetPath)" copy "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\110\DTS\PipelineComponents" /Y

  Read the article

• SQL -- How to combine three SELECT statements with very tricky requirements

  - by Frederick

  I have a SQL query with three SELECT statements. A picture of the data tables generated by these three select statements is located at www.britestudent.com/pub/1.png. Each of the three data tables have identical columns. I want to combine these three tables into one table such that: (1) All rows in top table (Table1) are always included. (2) Rows in the middle table (Table2) are included only when the values in column1 (UserName) and column4 (CourseName) do not match with any row from Table1. Both columns need to match for the row in Table2 to not be included. (3) Rows in the bottom table (Table3) are included only when the value in column4 (CourseName) is not already in any row of the results from combining Table1 and Table2. I have had success in implementing (1) and (2) with an SQL query like this: SELECT DISTINCT UserName AS UserName, MAX(AmountUsed) AS AmountUsed, MAX(AnsweredCorrectly) AS AnsweredCorrectly, CourseName, MAX(course_code) AS course_code, MAX(NoOfQuestionsInCourse) AS NoOfQuestionsInCourse, MAX(NoOfQuestionSetsInCourse) AS NoOfQuestionSetsInCourse FROM ( "SELECT statement 1" UNION "SELECT statement 2" ) dt_derivedTable_1 GROUP BY CourseName, UserName Where "SELECT statement 1" is the query that generates Table1 and "SELECT statement 2" is the query that generates Table2. A picture of the data table generated by this query is located at www.britestudent.com/pub/2.png. I can get away with using the MAX() function because values in the AmountUsed and AnsweredCorrectly columns in Table1 will always be larger than those in Table2 (and they are identical in the last three columns of both tables). What I fail at is implementing (3). Any suggestions on how to do this will be appreciated. It is tricky because the UserName values in Table3 are null, and because the CourseName values in the combined Table1 and Table2 results are not unique (but they are unique in Table3). After implementing (3), the final table should look like the table in picture 2.png with the addition of the last row from Table3 (the row with the CourseName value starting with "4. Klasse..." I have tried to implement (3) using another derived table using SELECT, MAX() and UNION, but I could not get it to work. Below is my full SQL query with the lines from this failed attempt to implement (3) commented out. Cheers, Frederick PS--I am new to this forum (and new to SQL as well), but I have had more of my previous problems answered by reading other people's posts on this forum than from reading any other forum or Web site. This forum is a great resources. -- SELECT DISTINCT MAX(UserName), MAX(AmountUsed) AS AmountUsed, MAX(AnsweredCorrectly) AS AnsweredCorrectly, CourseName, MAX(course_code) AS course_code, MAX(NoOfQuestionsInCourse) AS NoOfQuestionsInCourse, MAX(NoOfQuestionSetsInCourse) AS NoOfQuestionSetsInCourse -- FROM ( SELECT DISTINCT UserName AS UserName, MAX(AmountUsed) AS AmountUsed, MAX(AnsweredCorrectly) AS AnsweredCorrectly, CourseName, MAX(course_code) AS course_code, MAX(NoOfQuestionsInCourse) AS NoOfQuestionsInCourse, MAX(NoOfQuestionSetsInCourse) AS NoOfQuestionSetsInCourse FROM ( -- Table 1 - All UserAccount/Course combinations that have had quizzez. SELECT DISTINCT dbo.win_user.user_name AS UserName, cast(dbo.GetAmountUsed(dbo.session_header.win_user_id, dbo.course.course_id, dbo.course.no_of_questionsets_in_course) as nvarchar(10)) AS AmountUsed, Isnull(cast(dbo.GetAnswerCorrectly(dbo.session_header.win_user_id, dbo.course.course_id, dbo.question_set.no_of_questions) as nvarchar(10)),0) AS AnsweredCorrectly, dbo.course.course_name AS CourseName, dbo.course.course_code, dbo.course.no_of_questions_in_course AS NoOfQuestionsInCourse, dbo.course.no_of_questionsets_in_course AS NoOfQuestionSetsInCourse FROM dbo.session_detail INNER JOIN dbo.session_header ON dbo.session_detail.session_header_id = dbo.session_header.session_header_id INNER JOIN dbo.win_user ON dbo.session_header.win_user_id = dbo.win_user.win_user_id INNER JOIN dbo.win_user_course ON dbo.win_user_course.win_user_id = dbo.win_user.win_user_id INNER JOIN dbo.question_set ON dbo.session_header.question_set_id = dbo.question_set.question_set_id RIGHT OUTER JOIN dbo.course ON dbo.win_user_course.course_id = dbo.course.course_id WHERE (dbo.session_detail.no_of_attempts = 1 OR dbo.session_detail.no_of_attempts IS NULL) AND (dbo.session_detail.is_correct = 1 OR dbo.session_detail.is_correct IS NULL) AND (dbo.win_user_course.is_active = 'True') GROUP BY dbo.win_user.user_name, dbo.course.course_name, dbo.question_set.no_of_questions, dbo.course.no_of_questions_in_course, dbo.course.no_of_questionsets_in_course, dbo.session_header.win_user_id, dbo.course.course_id, dbo.course.course_code UNION ALL -- Table 2 - All UserAccount/Course combinations that do or do not have quizzes but where the Course is selected for quizzes for that User Account. SELECT dbo.win_user.user_name AS UserName, -1 AS AmountUsed, -1 AS AnsweredCorrectly, dbo.course.course_name AS CourseName, dbo.course.course_code, dbo.course.no_of_questions_in_course AS NoOfQuestionsInCourse, dbo.course.no_of_questionsets_in_course AS NoOfQuestionSetsInCourse FROM dbo.win_user_course INNER JOIN dbo.win_user ON dbo.win_user_course.win_user_id = dbo.win_user.win_user_id RIGHT OUTER JOIN dbo.course ON dbo.win_user_course.course_id = dbo.course.course_id WHERE (dbo.win_user_course.is_active = 'True') GROUP BY dbo.win_user.user_name, dbo.course.course_name, dbo.course.no_of_questions_in_course, dbo.course.no_of_questionsets_in_course, dbo.course.course_id, dbo.course.course_code ) dt_derivedTable_1 GROUP BY CourseName, UserName -- UNION ALL -- Table 3 - All Courses. -- SELECT DISTINCT null AS UserName, -- -2 AS AmountUsed, -- -2 AS AnsweredCorrectly, -- dbo.course.course_name AS CourseName, -- dbo.course.course_code, -- dbo.course.no_of_questions_in_course AS NoOfQuestionsInCourse, -- dbo.course.no_of_questionsets_in_course AS NoOfQuestionSetsInCourse -- FROM dbo.course -- WHERE is_active = 'True' -- ) dt_derivedTable_2 -- GROUP BY CourseName -- ORDER BY CourseName

  Read the article

• sql perfomance on new server

  - by Rapunzo

  My database is running on a pc (AMD Phenom x6, intel ssd disk, 8GB DDR3 RAM and windows 7 OS + sql server 2008 R2 sp3 ) and it started working hard, timeout problems and up to 30 seconds long queries after 200 mb of database And I also have an old server pc (IBM x-series 266: 72*3 15k rpm scsi discs with raid5, 4 gb ram and windows server 2003 + sql server 2008 R2 sp3 ) and same query start to give results in 100 seconds.. I tried query analyser tool for tuning my indexed. but not so much improvements. its a big dissapointment for me. because I thought even its an old server pc it should be more powerfull with 15k rpm discs with raid5. what should I do. do I need $10.000 new server to get a good performance for my sql server? cant I use that IBM server? Extra information: there is 50 sql users and its an ERP program. There is my query ALTER FUNCTION [dbo].[fnDispoTerbiye] ( ) RETURNS TABLE AS RETURN ( SELECT MD.dispoNo, SV.sevkNo, M1.musteriAdi AS musteri, SD.tipTurId, TT.tipTur, SD.tipNo, SD.desenNo, SD.varyantNo, SUM(T.topMetre) AS toplamSevkMetre, MD.dispoMetresi, DT.gelisMetresi, ISNULL(DT.fire, 0) AS fire, SV.sevkTarihi, DT.gelisTarihi, SP.mamulTermin, SD.miktar AS siparisMiktari, M.musteriAdi AS boyahane, MD.akisNotu AS islemler, --dbo.fnAkisIslemleri(MD.dispoNo) DT.partiNo, DT.iplikBoyaId, B.tanimAd AS BoyaTuru, MAX(HD.hamEn) AS hamEn, MAX(HD.hamGramaj) AS hamGramaj, TS.mamulEn, TS.mamulGramaj, DT.atkiCekmesi, DT.cozguCekmesi, DT.fiyat, DV.dovizCins, DT.dovizId, (SELECT CASE WHEN DT.dovizId = 2 THEN CAST(round(SUM(T .topMetre) * DT.fiyat * (SELECT TOP 1 satis FROM tblKur WHERE dovizId = 2 ORDER BY tarih DESC), 2) AS numeric(18, 2)) WHEN DT.dovizId = 3 THEN CAST(round(SUM(T .topMetre) * DT.fiyat * (SELECT TOP 1 satis FROM tblKur WHERE dovizId = 3 ORDER BY tarih DESC), 2) AS numeric(18, 2)) WHEN DT.dovizId = 1 THEN CAST(round(SUM(T .topMetre) * DT.fiyat * (SELECT TOP 1 satis FROM tblKur WHERE dovizId = 1 ORDER BY tarih DESC), 2) AS numeric(18, 2)) END AS Expr1) AS ToplamTLfiyat, DT.aciklama, MD.dispoNotu, SD.siparisId, SD.siparisDetayId, DT.sqlUserName, DT.kayitTarihi, O.orguAd, 'Çözgü=(' + (SELECT dbo.fnTipIplikler(SD.tipTurId, SD.tipNo, SD.desenNo, SD.varyantNo, 1) AS Expr1) + ')' + ' Atki=(' + (SELECT dbo.fnTipIplikler(SD.tipTurId, SD.tipNo, SD.desenNo, SD.varyantNo, 2) AS Expr1) + ')' AS iplikAciklama, DT.prosesOk, dbo.[fnYikamaTalimat](SP.siparisId) yikamaTalimati FROM tblDoviz AS DV WITH(NOLOCK) INNER JOIN tblDispoTerbiye AS DT WITH(NOLOCK) INNER JOIN tblTanimlar AS B WITH(NOLOCK) ON DT.iplikBoyaId = B.tanimId AND B.tanimTurId = 2 ON DV.id = DT.dovizId RIGHT OUTER JOIN tblMusteri AS M1 WITH(NOLOCK) INNER JOIN tblSiparisDetay AS SD WITH(NOLOCK) INNER JOIN tblDispo AS MD WITH(NOLOCK) ON SD.siparisDetayId = MD.siparisDetayId INNER JOIN tblTipTur AS TT WITH(NOLOCK) ON SD.tipTurId = TT.tipTurId INNER JOIN tblSiparis AS SP WITH(NOLOCK) ON SD.siparisId = SP.siparisId ON M1.musteriNo = SP.musteriNo INNER JOIN tblTip AS TP WITH(NOLOCK) ON SD.tipTurId = TP.tipTurId AND SD.tipNo = TP.tipNo AND SD.desenNo = TP.desen AND SD.varyantNo = TP.varyant INNER JOIN tblOrgu AS O WITH(NOLOCK) ON TP.orguId = O.orguId INNER JOIN tblMusteri AS M WITH(NOLOCK) INNER JOIN tblSevkiyat AS SV WITH(NOLOCK) ON M.musteriNo = SV.musteriNo INNER JOIN tblSevkDetay AS SVD WITH(NOLOCK) ON SV.sevkNo = SVD.sevkNo ON MD.mamulDispoHamSevkno = SV.sevkNo LEFT OUTER JOIN tblTop AS T WITH(NOLOCK) INNER JOIN tblDispo AS HD WITH(NOLOCK) ON T.dispoNo = HD.dispoNo AND T.dispoTuruId = HD.dispoTuruId ON SVD.dispoTuruId = T.dispoTuruId AND SVD.dispoNo = T.dispoNo AND SVD.topNo = T.topNo AND MD.siparisDetayId = HD.siparisDetayId ON DT.dispoTuruId = MD.dispoTuruId AND DT.dispoNo = MD.dispoNo LEFT OUTER JOIN tblDispoTerbiyeTest AS TS WITH(NOLOCK) ON DT.dispoTuruId = TS.dispoTuruId AND DT.dispoNo = TS.dispoNo --WHERE DT.gelisTarihi IS NULL -- OR DT.gelisTarihi > GETDATE()-30 GROUP BY MD.dispoNo, DT.partiNo, DT.iplikBoyaId, TS.mamulEn, TS.mamulGramaj, DT.gelisMetresi, DT.gelisTarihi, DT.atkiCekmesi, DT.cozguCekmesi, DT.fire, DT.fiyat, DT.aciklama, DT.sqlUserName, DT.kayitTarihi, SD.tipTurId, TT.tipTur, SD.tipNo, SD.desenNo, SD.varyantNo, SD.siparisId, SD.siparisDetayId, B.tanimAd, M.musteriAdi, M.musteriAdi, M1.musteriAdi, O.orguAd, TP.iplikAciklama, SD.miktar, MD.dispoNotu, SP.mamulTermin, DT.dovizId, DV.dovizCins, MD.dispoMetresi, MD.akisNotu, SV.sevkNo, SV.sevkTarihi, DT.prosesOk,SP.siparisId )

  Read the article

• Why Do I See the "In Recovery" Msg, and How Can I Prevent it?

  - by John Hansen

  The project I'm working on creates a local copy of the SQL Server database for each SVN branch you work on. We're running SQL Server 2008 Express with Advanced Services on our local machine to host it. When we create a new branch, the build script will create a new database with the ID of that branch, creates the schema objects, and copies over a selection of data from the production shadow server. After the database is created, it, or other databases on the local machine, will often go into "In Recovery" mode for several minutes. After several refreshes it comes up and is happy, but will occasionally go back into "In Recovery" mode. The database is created in simple recovery mode. The file names aren't specified, so it uses default paths for files. The size of the database after loading data is ~400 megs. It is running in SQL Server 2005 compatibility mode. The command that creates the database is: sqlcmd -S $(DBServer) -Q "IF NOT EXISTS (SELECT [name] FROM sysdatabases WHERE [name] = '$(DBName)') BEGIN CREATE DATABASE [$(DBName)]; print 'Created $(DBName)'; END" ...where $(DBName) and $(DBServer) are MSBuild parameters. I got a nice clean log file this morning. When I turned on my computer it starts all five databases. However, two of them show transactions being rolled forward and backwards. The it just keeps trying to start up all five of the databases. 2010-06-10 08:24:59.74 spid52 Starting up database 'ASPState'. 2010-06-10 08:24:59.82 spid52 Starting up database 'CommunityLibrary'. 2010-06-10 08:25:03.97 spid52 Starting up database 'DLG-R8441'. 2010-06-10 08:25:05.07 spid52 2 transactions rolled forward in database 'DLG-R8441' (6). This is an informational message only. No user action is required. 2010-06-10 08:25:05.14 spid52 0 transactions rolled back in database 'DLG-R8441' (6). This is an informational message only. No user action is required. 2010-06-10 08:25:05.14 spid52 Recovery is writing a checkpoint in database 'DLG-R8441' (6). This is an informational message only. No user action is required. 2010-06-10 08:25:11.23 spid52 Starting up database 'DLG-R8979'. 2010-06-10 08:25:12.31 spid36s Starting up database 'DLG-R8441'. 2010-06-10 08:25:13.17 spid52 2 transactions rolled forward in database 'DLG-R8979' (9). This is an informational message only. No user action is required. 2010-06-10 08:25:13.22 spid52 0 transactions rolled back in database 'DLG-R8979' (9). This is an informational message only. No user action is required. 2010-06-10 08:25:13.22 spid52 Recovery is writing a checkpoint in database 'DLG-R8979' (9). This is an informational message only. No user action is required. 2010-06-10 08:25:18.43 spid52 Starting up database 'Rls QA'. 2010-06-10 08:25:19.13 spid46s Starting up database 'DLG-R8979'. 2010-06-10 08:25:23.29 spid36s Starting up database 'DLG-R8441'. 2010-06-10 08:25:27.91 spid52 Starting up database 'ASPState'. 2010-06-10 08:25:29.80 spid41s Starting up database 'DLG-R8979'. 2010-06-10 08:25:31.22 spid52 Starting up database 'Rls QA'. In this case it kept trying to start the databases continuously until I shut down SQL Server at 08:48:19.72, 23 minutes later. Meanwhile, I actually am able to use the databases much of the time.

  Read the article

• Nginx and client certificates from hierarchical OpenSSL-based certification authorities

  - by Fmy Oen

  I'm trying to set up root certification authority, subordinate certification authority and to generate the client certificates signed by any of this CA that nginx 0.7.67 on Debian Squeeze will accept. My problem is that root CA signed client certificate works fine while subordinate CA signed one results in "400 Bad Request. The SSL certificate error". Step 1: nginx virtual host configuration: server { server_name test.local; access_log /var/log/nginx/test.access.log; listen 443 default ssl; keepalive_timeout 70; ssl_protocols SSLv3 TLSv1; ssl_ciphers AES128-SHA:AES256-SHA:RC4-SHA:DES-CBC3-SHA:RC4-MD5; ssl_certificate /etc/nginx/ssl/server.crt; ssl_certificate_key /etc/nginx/ssl/server.key; ssl_client_certificate /etc/nginx/ssl/client.pem; ssl_verify_client on; ssl_session_cache shared:SSL:10m; ssl_session_timeout 5m; location / { proxy_pass http://testsite.local/; } } Step 2: PKI infrastructure organization for both root and subordinate CA (based on this article): # mkdir ~/pki && cd ~/pki # mkdir rootCA subCA # cp -v /etc/ssl/openssl.cnf rootCA/ # cd rootCA/ # mkdir certs private crl newcerts; touch serial; echo 01 > serial; touch index.txt; touch crlnumber; echo 01 > crlnumber # cp -Rvp * ../subCA/ Almost no changes was made to rootCA/openssl.cnf: [ CA_default ] dir = . # Where everything is kept ... certificate = $dir/certs/rootca.crt # The CA certificate ... private_key = $dir/private/rootca.key # The private key and to subCA/openssl.cnf: [ CA_default ] dir = . # Where everything is kept ... certificate = $dir/certs/subca.crt # The CA certificate ... private_key = $dir/private/subca.key # The private key Step 3: Self-signed root CA certificate generation: # openssl genrsa -out ./private/rootca.key -des3 2048 # openssl req -x509 -new -key ./private/rootca.key -out certs/rootca.crt -config openssl.cnf Enter pass phrase for ./private/rootca.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, YOUR name) []:rootca Email Address []: Step 4: Subordinate CA certificate generation: # cd ../subCA # openssl genrsa -out ./private/subca.key -des3 2048 # openssl req -new -key ./private/subca.key -out subca.csr -config openssl.cnf Enter pass phrase for ./private/subca.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, YOUR name) []:subca Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: Step 5: Subordinate CA certificate signing by root CA certificate: # cd ../rootCA/ # openssl ca -in ../subCA/subca.csr -extensions v3_ca -config openssl.cnf Using configuration from openssl.cnf Enter pass phrase for ./private/rootca.key: Check that the request matches the signature Signature ok Certificate Details: Serial Number: 1 (0x1) Validity Not Before: Feb 4 10:49:43 2013 GMT Not After : Feb 4 10:49:43 2014 GMT Subject: countryName = AU stateOrProvinceName = Some-State organizationName = Internet Widgits Pty Ltd commonName = subca X509v3 extensions: X509v3 Subject Key Identifier: C9:E2:AC:31:53:81:86:3F:CD:F8:3D:47:10:FC:E5:8E:C2:DA:A9:20 X509v3 Authority Key Identifier: keyid:E9:50:E6:BF:57:03:EA:6E:8F:21:23:86:BB:44:3D:9F:8F:4A:8B:F2 DirName:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca serial:9F:FB:56:66:8D:D3:8F:11 X509v3 Basic Constraints: CA:TRUE Certificate is to be certified until Feb 4 10:49:43 2014 GMT (365 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y ... # cd ../subCA/ # cp -v ../rootCA/newcerts/01.pem certs/subca.crt Step 6: Server certificate generation and signing by root CA (for nginx virtual host): # cd ../rootCA # openssl genrsa -out ./private/server.key -des3 2048 # openssl req -new -key ./private/server.key -out server.csr -config openssl.cnf Enter pass phrase for ./private/server.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, YOUR name) []:test.local Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: # openssl ca -in server.csr -out certs/server.crt -config openssl.cnf Step 7: Client #1 certificate generation and signing by root CA: # openssl genrsa -out ./private/client1.key -des3 2048 # openssl req -new -key ./private/client1.key -out client1.csr -config openssl.cnf Enter pass phrase for ./private/client1.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, YOUR name) []:Client #1 Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: # openssl ca -in client1.csr -out certs/client1.crt -config openssl.cnf Step 8: Client #1 certificate converting to PKCS12 format: # openssl pkcs12 -export -out certs/client1.p12 -inkey private/client1.key -in certs/client1.crt -certfile certs/rootca.crt Step 9: Client #2 certificate generation and signing by subordinate CA: # cd ../subCA/ # openssl genrsa -out ./private/client2.key -des3 2048 # openssl req -new -key ./private/client2.key -out client2.csr -config openssl.cnf Enter pass phrase for ./private/client2.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, YOUR name) []:Client #2 Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: # openssl ca -in client2.csr -out certs/client2.crt -config openssl.cnf Step 10: Client #2 certificate converting to PKCS12 format: # openssl pkcs12 -export -out certs/client2.p12 -inkey private/client2.key -in certs/client2.crt -certfile certs/subca.crt Step 11: Passing server certificate and private key to nginx (performed with OS superuser privileges): # cd ../rootCA/ # cp -v certs/server.crt /etc/nginx/ssl/ # cp -v private/server.key /etc/nginx/ssl/ Step 12: Passing root and subordinate CA certificates to nginx (performed with OS superuser privileges): # cat certs/rootca.crt > /etc/nginx/ssl/client.pem # cat ../subCA/certs/subca.crt >> /etc/nginx/ssl/client.pem client.pem file look like this: # cat /etc/nginx/ssl/client.pem -----BEGIN CERTIFICATE----- MIID6TCCAtGgAwIBAgIJAJ/7VmaN048RMA0GCSqGSIb3DQEBBQUAMFYxCzAJBgNV BAYTAkFVMRMwEQYDVQQIEwpTb21lLVN0YXRlMSEwHwYDVQQKExhJbnRlcm5ldCBX aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMTBnJvb3RjYTAeFw0xMzAyMDQxMDM1NTda ... -----END CERTIFICATE----- Certificate: Data: Version: 3 (0x2) Serial Number: 1 (0x1) ... -----BEGIN CERTIFICATE----- MIID4DCCAsigAwIBAgIBATANBgkqhkiG9w0BAQUFADBWMQswCQYDVQQGEwJBVTET MBEGA1UECBMKU29tZS1TdGF0ZTEhMB8GA1UEChMYSW50ZXJuZXQgV2lkZ2l0cyBQ dHkgTHRkMQ8wDQYDVQQDEwZyb290Y2EwHhcNMTMwMjA0MTA0OTQzWhcNMTQwMjA0 ... -----END CERTIFICATE----- It looks like everything is working fine: # service nginx reload # Reloading nginx configuration: Enter PEM pass phrase: # nginx. # Step 13: Installing *.p12 certificates in browser (Firefox in my case) gives the problem I've mentioned above. Client #1 = 200 OK, Client #2 = 400 Bad request/The SSL certificate error. Any ideas what should I do? Update 1: Results of SSL connection test attempts: # openssl s_client -connect test.local:443 -CAfile ~/pki/rootCA/certs/rootca.crt -cert ~/pki/rootCA/certs/client1.crt -key ~/pki/rootCA/private/client1.key -showcerts Enter pass phrase for tmp/testcert/client1.key: CONNECTED(00000003) depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = rootca verify return:1 depth=0 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = test.local verify return:1 --- Certificate chain 0 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=test.local i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca -----BEGIN CERTIFICATE----- MIIDpjCCAo6gAwIBAgIBAjANBgkqhkiG9w0BAQUFADBWMQswCQYDVQQGEwJBVTET MBEGA1UECBMKU29tZS1TdGF0ZTEhMB8GA1UEChMYSW50ZXJuZXQgV2lkZ2l0cyBQ dHkgTHRkMQ8wDQYDVQQDEwZyb290Y2EwHhcNMTMwMjA0MTEwNjAzWhcNMTQwMjA0 ... -----END CERTIFICATE----- 1 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca -----BEGIN CERTIFICATE----- MIID6TCCAtGgAwIBAgIJAJ/7VmaN048RMA0GCSqGSIb3DQEBBQUAMFYxCzAJBgNV BAYTAkFVMRMwEQYDVQQIEwpTb21lLVN0YXRlMSEwHwYDVQQKExhJbnRlcm5ldCBX aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMTBnJvb3RjYTAeFw0xMzAyMDQxMDM1NTda ... -----END CERTIFICATE----- --- Server certificate subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=test.local issuer=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca --- Acceptable client certificate CA names /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=subca --- SSL handshake has read 3395 bytes and written 2779 bytes --- New, TLSv1/SSLv3, Cipher is AES256-SHA Server public key is 2048 bit Secure Renegotiation IS supported Compression: zlib compression Expansion: zlib compression SSL-Session: Protocol : TLSv1 Cipher : AES256-SHA Session-ID: 15BFC2029691262542FAE95A48078305E76EEE7D586400F8C4F7C516B0F9D967 Session-ID-ctx: Master-Key: 23246CF166E8F3900793F0A2561879E5DB07291F32E99591BA1CF53E6229491FEAE6858BFC9AACAF271D9C3706F139C7 Key-Arg : None PSK identity: None PSK identity hint: None SRP username: None TLS session ticket: 0000 - c2 5e 1d d2 b5 6d 40 23-b2 40 89 e4 35 75 70 07 .^...m@#[email protected]. 0010 - 1b bb 2b e6 e0 b5 ab 10-10 bf 46 6e aa 67 7f 58 ..+.......Fn.g.X 0020 - cf 0e 65 a4 67 5a 15 ba-aa 93 4e dd 3d 6e 73 4c ..e.gZ....N.=nsL 0030 - c5 56 f6 06 24 0f 48 e6-38 36 de f1 b5 31 c5 86 .V..$.H.86...1.. ... 0440 - 4c 53 39 e3 92 84 d2 d0-e5 e2 f5 8a 6a a8 86 b1 LS9.........j... Compression: 1 (zlib compression) Start Time: 1359989684 Timeout : 300 (sec) Verify return code: 0 (ok) --- Everything seems fine with Client #2 and root CA certificate but request returns 400 Bad Request error: # openssl s_client -connect test.local:443 -CAfile ~/pki/rootCA/certs/rootca.crt -cert ~/pki/subCA/certs/client2.crt -key ~/pki/subCA/private/client2.key -showcerts Enter pass phrase for tmp/testcert/client2.key: CONNECTED(00000003) depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = rootca verify return:1 depth=0 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = test.local verify return:1 ... Compression: 1 (zlib compression) Start Time: 1359989989 Timeout : 300 (sec) Verify return code: 0 (ok) --- GET / HTTP/1.0 HTTP/1.1 400 Bad Request Server: nginx/0.7.67 Date: Mon, 04 Feb 2013 15:00:43 GMT Content-Type: text/html Content-Length: 231 Connection: close <html> <head><title>400 The SSL certificate error</title></head> <body bgcolor="white"> <center><h1>400 Bad Request</h1></center> <center>The SSL certificate error</center> <hr><center>nginx/0.7.67</center> </body> </html> closed Verification fails with Client #2 certificate and subordinate CA certificate: # openssl s_client -connect test.local:443 -CAfile ~/pki/subCA/certs/subca.crt -cert ~/pki/subCA/certs/client2.crt -key ~/pki/subCA/private/client2.key -showcerts Enter pass phrase for tmp/testcert/client2.key: CONNECTED(00000003) depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = rootca verify error:num=19:self signed certificate in certificate chain verify return:0 ... Compression: 1 (zlib compression) Start Time: 1359990354 Timeout : 300 (sec) Verify return code: 19 (self signed certificate in certificate chain) --- GET / HTTP/1.0 HTTP/1.1 400 Bad Request ... Still getting 400 Bad Request error with concatenated CA certificates and Client #2 (but still everything ok with Client #1): # cat certs/rootca.crt ../subCA/certs/subca.crt > certs/concatenatedca.crt # openssl s_client -connect test.local:443 -CAfile ~/pki/rootCA/certs/concatenatedca.crt -cert ~/pki/subCA/certs/client2.crt -key ~/pki/subCA/private/client2.key -showcerts Enter pass phrase for tmp/testcert/client2.key: CONNECTED(00000003) depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = rootca verify return:1 depth=0 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = test.local verify return:1 --- ... Compression: 1 (zlib compression) Start Time: 1359990772 Timeout : 300 (sec) Verify return code: 0 (ok) --- GET / HTTP/1.0 HTTP/1.1 400 Bad Request ... Update 2: I've managed to recompile nginx with enabled debug. Here is the part of successfull conection by Client #1 track: 2013/02/05 14:08:23 [debug] 38701#0: *119 accept: <MY IP ADDRESS> fd:3 2013/02/05 14:08:23 [debug] 38701#0: *119 event timer add: 3: 60000:2856497512 2013/02/05 14:08:23 [debug] 38701#0: *119 kevent set event: 3: ft:-1 fl:0025 2013/02/05 14:08:23 [debug] 38701#0: *119 malloc: 28805200:660 2013/02/05 14:08:23 [debug] 38701#0: *119 malloc: 28834400:1024 2013/02/05 14:08:23 [debug] 38701#0: *119 posix_memalign: 28860000:4096 @16 2013/02/05 14:08:23 [debug] 38701#0: *119 http check ssl handshake 2013/02/05 14:08:23 [debug] 38701#0: *119 https ssl handshake: 0x16 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL server name: "test.local" 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_do_handshake: -1 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_get_error: 2 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL handshake handler: 0 2013/02/05 14:08:23 [debug] 38701#0: *119 verify:1, error:0, depth:1, subject:"/C=AU /ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca",issuer: "/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca" 2013/02/05 14:08:23 [debug] 38701#0: *119 verify:1, error:0, depth:0, subject:"/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=Client #1",issuer: "/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca" 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_do_handshake: 1 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL: TLSv1, cipher: "AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1" 2013/02/05 14:08:23 [debug] 38701#0: *119 http process request line 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_read: -1 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_get_error: 2 2013/02/05 14:08:23 [debug] 38701#0: *119 http process request line 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_read: 1 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_read: 524 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_read: -1 2013/02/05 14:08:23 [debug] 38701#0: *119 SSL_get_error: 2 2013/02/05 14:08:23 [debug] 38701#0: *119 http request line: "GET / HTTP/1.1" And here is the part of unsuccessfull conection by Client #2 track: 2013/02/05 13:51:34 [debug] 38701#0: *112 accept: <MY_IP_ADDRESS> fd:3 2013/02/05 13:51:34 [debug] 38701#0: *112 event timer add: 3: 60000:2855488975 2013/02/05 13:51:34 [debug] 38701#0: *112 kevent set event: 3: ft:-1 fl:0025 2013/02/05 13:51:34 [debug] 38701#0: *112 malloc: 28805200:660 2013/02/05 13:51:34 [debug] 38701#0: *112 malloc: 28834400:1024 2013/02/05 13:51:34 [debug] 38701#0: *112 posix_memalign: 28860000:4096 @16 2013/02/05 13:51:34 [debug] 38701#0: *112 http check ssl handshake 2013/02/05 13:51:34 [debug] 38701#0: *112 https ssl handshake: 0x16 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL server name: "test.local" 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_do_handshake: -1 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_get_error: 2 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL handshake handler: 0 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_do_handshake: -1 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_get_error: 2 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL handshake handler: 0 2013/02/05 13:51:34 [debug] 38701#0: *112 verify:0, error:20, depth:1, subject:"/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=subca",issuer: "/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca" 2013/02/05 13:51:34 [debug] 38701#0: *112 verify:0, error:27, depth:1, subject:"/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=subca",issuer: "/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=rootca" 2013/02/05 13:51:34 [debug] 38701#0: *112 verify:1, error:27, depth:0, subject:"/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=Client #2",issuer: "/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=subca" 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_do_handshake: 1 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL: TLSv1, cipher: "AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1" 2013/02/05 13:51:34 [debug] 38701#0: *112 http process request line 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_read: 1 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_read: 524 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_read: -1 2013/02/05 13:51:34 [debug] 38701#0: *112 SSL_get_error: 2 2013/02/05 13:51:34 [debug] 38701#0: *112 http request line: "GET / HTTP/1.1" So I'm getting OpenSSL error #20 and then #27. According to verify documentation: 20 X509_V_ERR_UNABLE_TO_GET_ISSUER_CERT_LOCALLY: unable to get local issuer certificate the issuer certificate could not be found: this occurs if the issuer certificate of an untrusted certificate cannot be found. 27 X509_V_ERR_CERT_UNTRUSTED: certificate not trusted the root CA is not marked as trusted for the specified purpose.

  Read the article

• vb.net : is it possible to connect to sql server 2008 via odbc but not through vb.net code?

  - by phill

  I'm supporting an old vb.net program whose database it connected to was moved from SQL Server 2005 to SQL Server 2008. Is there a setting on SQL Server 2008 which will allow ODBC connections to access the database but not allow VB.NET to connect to it programmatically? the error i keep receiving in the app is: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) however I can connect to it when I create a system dsn to the sql server instance and through VS2005's Tools Connect to Database. Here is the code I'm using to connect: dim strC as string strC = "data source=bob; database=subscribers; user id=bobuser; password=passme" dim connection as New SqlClient.SqlConnection(strC) try connection.open() catch ex as Exception msgbox(ex.message) end try connection.Close()

  Read the article

• SQL Server 2005 to 2008 Bak file help please!

  - by Brandon

  I have a SQl Server 2005 database backup that I want to transfer to SQL Server 2008 on my server. I spent 3 days transferring the .bak file from my own machine to my server. I then tried to restore the bak file and I got an error. I then read online a completely different method for adding a SQL server 2005 Database to SQL server 2008 which was the detach and attach method which means I need to detach the database in SQL Server 2005 and then transfer the MDF file from it via ftp to my server and then attach it in SQL Server 2008. Well I already used a lot of bandwidth transferring the .bak file to my server. is there a way to convert my .bak file which is already on my server to an MDF file and attach it in SQL server 2008?

  Read the article

• SQLAuthority News SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

  SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL [...]...Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

  Read the article

< Previous Page | 218 219 220 221 222 223 224 225 226 227 228 229  | Next Page >