Search Results

Search found 101927 results on 4078 pages for 'ms sql server'.

Page 186/4078 | < Previous Page | 182 183 184 185 186 187 188 189 190 191 192 193  | Next Page >

  • Select a record with highest amount by joining two tables

    - by user2516394
    I've 2 tables Sales & Purchase, Sales table with fields SaleId, Rate, Quantity, Date, CompanyId, UserID. Purchase table with fields PurchaseId, Rate, Quantity, Date, CompanyId, UserID. I want to select a record from either table that have highest Rate*Quantity. SELECT SalesId Or PurchaseId FROM Sales,Purchase where Sales.UserId=Purchase.UserId and Sales.CompanyId=Purchase.CompanyId AND Sales.Date=Current date AND Purchase.Date=Current date AND Sales.UserId=1 AND Purchase.UserId=1 AND Sales.CompanyId=1 AND Purchase.ComoanyId=1

    Read the article

  • how do i insert into two table all at once in a stored procedure?

    - by user996502
    Doing a project for school so any help would be great thank you! I have two tables how do i insert into two tables? so both tables are linked. First table called Customer with primary key called CID that auto increments CREATE TABLE [dbo].[Customer]( [CID] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](255) NOT NULL, [FirstName] [varchar](255) NOT NULL, [MiddleName] [varchar](255) NULL, [EmailAddress] [varchar](255) NOT NULL, [PhoneNumber] [varchar](12) NOT NULL CONSTRAINT [PK__CInforma__C1F8DC5968DD69DC] PRIMARY KEY CLUSTERED ( And a second table called Employment that has a foreign key linked to the parent table CREATE TABLE [dbo].[Employment]( [EID] [int] IDENTITY(1,1) NOT NULL, [CID] [int] NOT NULL, [Employer] [varchar](255) NOT NULL, [Occupation] [varchar](255) NOT NULL, [Income] [varchar](25) NOT NULL, [WPhone] [varchar](12) NOT NULL, CONSTRAINT [PK__Employme__C190170BC7827524] PRIMARY KEY CLUSTERED (

    Read the article

  • Field to display Previous 30 Day Total

    - by whytheq
    I've got this table: CREATE TABLE #Data1 ( [Market] VARCHAR(100) NOT NULL, [Operator] VARCHAR(100) NOT NULL, [Date] DATETIME NOT NULL, [Measure] VARCHAR(100) NOT NULL, [Amount] NUMERIC(36,10) NOT NULL, --new calculated fields [DailyAvg_30days] NUMERIC(38,6) NULL DEFAULT 0 ) I've populated all the fields apart from DailyAvg_30days. This field needs to show the total for the preceding 30 days e.g. 1. if Date for a particular record is 2nd Dec then it will be the total for the period 3rd Nov - 2nd Dec inclusive. 2. if Date for a particular record is 1st Dec then it will be the total for the period 2nd Nov - 1st Dec inclusive. My attempt to try to find these totals before updating the table is as follows: SELECT a.[Market], a.[Operator], a.[Date], a.[Measure], a.[Amount], [DailyAvg_30days] = SUM(b.[Amount]) FROM #Data1 a INNER JOIN #Data1 b ON a.[Market] = b.[Market] AND a.[Operator] = b.[Operator] AND a.[Measure] = b.[Measure] AND a.[Date] >= b.[Date]-30 AND a.[Date] <= b.[Date] GROUP BY a.[Market], a.[Operator], a.[Date], a.[Measure], a.[Amount] ORDER BY 1,2,4,3 Is this a valid approach or do I need to approach this from a different angle?

    Read the article

  • Synchronizing non-DB SQL Server objects

    - by DigDoug
    There are a number of tools available for synchronizing Tables, Indexes, Views, Stored Procedures and objects within a database. (We love RedGate here, and throw a lot of money their way). However, I'm having a very difficult time finding tools that will help with Jobs, Logins and Linked Servers. Do these things exist? Am I missing something obvious?

    Read the article

  • strange behavior of <> to filter null values

    - by Kerezo
    Hi experts: I have a table Called tblAlarm and it has some records like this: I have another table for determine what user see what message: Now I want to write a query to show Messages that user has not seen if message didinot expired.(for example it's year between BeginYear and EndYear and so on ...). I write this query: SELECT * FROM tblAlarms LEFT OUTER JOIN tblUsersAlarms tua ON tblAlarms.Id=tua.MessageID WHERE @CurrentYear BETWEEN tblAlarms.BeginYear AND tblAlarms.EndYear AND @CurrentMonth BETWEEN tblAlarms.BeginMonth AND tblAlarms.EndMonth AND @CurrentDay BETWEEN tblAlarms.BeginDay AND tblAlarms.EndDay AND (@CurrentHour * 60 + @CurrentMinute) BETWEEN tblAlarms.BeginHour*60 + tblAlarms.BeginMinute AND tblAlarms.EndHour*60 + tblAlarms.EndMinute --AND (tua.UserID <> 128 AND tua.UserID IS NULL) and it returns : but if I unComment last line it does not return any record.How I can determine what messages that users has not been seen? thanks

    Read the article

  • SQL Server 05, which is optimal, LIKE %<term>% or CONTAINS() for searching large column

    - by Spud1
    I've got a function written by another developer which I am trying to modify for a slightly different use. It is used by a SP to check if a certain phrase exists in a text document stored in the DB, and returns 1 if the value is found or 0 if its not. This is the query: SELECT @mres=1 from documents where id=@DocumentID and contains(text, @search_term) The document contains mostly XML, and the search_term is a GUID formatted as an nvarchar(40). This seems to run quite slowly to me (taking 5-6 seconds to execute this part of the process), but in the same script file there is also this version of the above, commented out. SELECT @mres=1 from documents where id=@DocumentID and textlike '%' + @search_term + '%' This version runs MUCH quicker, taking 4ms compared to 15ms for the first example. So, my question is why use the first over the second? I assume this developer (who is no longer working with me) had a good reason, but at the moment I am struggling to find it.. Is it possibly something to do with the full text indexing? (this is a dev DB I am working with, so the production version may have better indexing..) I am not that clued up on FTI really so not quite sure at the moment. Thoughts/ideas?

    Read the article

  • SQL Server 2008 Running trigger after Insert, Update locks original table

    - by Polity
    Hi Folks, I have a serious performance problem. I have a database with (related to this problem), 2 tables. 1 Table contains strings with some global information. The second table contains the string stripped down to each individual word. So the string is like indexed in the second table, word by word. The validity of the data in the second table is of less important then the validity of the data in the first table. Since the first table can grow like towards 1*10^6 records and the second table having an average of like 10 words for 1 string can grow like 1*10^7 records, i use a nolock in order to read the second this leaves me free for inserting new records without locking it (Expect many reads on both tables). I have a script which keeps on adding and updating rows to the first table in a MERGE statement. On average, the data beeing merged are like 20 strings a time and the scripts runs like ones every 5 seconds. On the first table, i have a trigger which is beeing invoked on a Insert or Update, which takes the newly inserted or updated data and calls a stored procedure on it which makes sure the data is indexed in the second table. (This takes some significant time). The problem is that when having the trigger disbaled, Reading the first table happens in a few ms. However, when enabling the trigger and your in bad luck of trying to read the first table while this is beeing updated, Our webserver gives you a timeout after 10 seconds (which is way to long anyways). I can quess from this part that when running the trigger, the first table is kept (partially) in a lock untill the trigger is completed. What do you think, if i'm right, is there a easy way around this? Thanks in advance! Cheers, Koen

    Read the article

  • i want to search in sql server with must have parameter in one colunm

    - by sherif4csharp
    hello i am usning c# and ms SQL server 2008 i have table like this id | propertyTypeId | FinishingQualityId | title | Description | features 1 1 2 prop1 propDEsc1 1,3,5,7 2 2 3 prop2 propDEsc2 1,3 3 6 5 prop3 propDEsc3 1 4 5 4 prop4 propDEsc4 3,5 5 4 6 prop5 propDEsc5 5,7 6 4 6 prop6 propDEsc6 and here is my stored code (search in the same table) create stored procdures propertySearch as @Id int = null, @pageSize float , @pageIndex int, @totalpageCount int output, @title nvarchar(150) =null , @propertyTypeid int = null , @finishingqualityid int = null , @features nvarchar(max) = null , -- this parameter send like 1,3 ( for example) begin select row_number () as TempId over( order by id) , id,title,description,propertyTypeId,propertyType.name,finishingQualityId,finishingQuality.Name,freatures into #TempTable from property join propertyType on propertyType.id= property.propertyTypeId join finishingQuality on finishingQuality.id = property.finishingQualityId where property.id = isnull(@id,property.id ) and proprty.PropertyTypeId= isnull(@propertyTypeid,property.propertyTypeId) select totalpageconunt = ((select count(*) from #TempTable )/@pageSize ) select * from #TempTable where tempid between (@pageindex-1)*@pagesize +1 and (@pageindex*@pageSize) end go i can't here filter the table by feature i sent. this table has to many rows i want to add to this stored code to filter data for example when i send 1,3 in features parameter i want to return row number one and two in the example table i write in this post (want to get the data from table must have the feature I send) many thanks for every one helped me and will help

    Read the article

  • MSSQL 2000 installation error: Setup failed to configure the server. Refer to the server error logs.

    - by kaneuniversal
    I'm trying to install MSSQL 2000 on a virtual Windows 2003 instance. However, every time I run the install program, it fails to start the service. This is the error log: 21:46:50 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I MSSQLSERVER -V 1 -M 0 -Q "SQL_Latin1_General_CP1_CI_AS" -H 131408 -U sa -P ############################################################################### Starting Service ... SQL_Latin1_General_CP1_CI_AS -m -Q -T4022 -T3659 Connecting to Server ... driver={sql server};server=xxxxxxxxxx;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver]Timeout expired driver={sql server};server=xxxxxxxxxx;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver]Timeout expired driver={sql server};server=xxxxxxxxxx;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver]Timeout expired SQL Server configuration failed. ############################################################################### 21:49:34 Process Exit Code: (-1) 22:19:04 Setup failed to configure the server. Refer to the server error logs and C:\WINDOWS\sqlstp.log for more information. 22:19:04 Action CleanUpInstall: 22:19:04 C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent 1 -Action 4 -Service SQLSERVERAGENT 22:19:05 Process Exit Code: (1060) The specified service does not exist as an installed service. 22:19:05 C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent 1 -Action 4 -Service MSSQLSERVER 22:19:05 Process Exit Code: (0) 22:19:05 StatsGenerate returned: 2 22:19:05 StatsGenerate (0x0,0x1,0xf00000,0x200,1033,303,0x0,0x1,0,0,0 22:19:05 StatsGenerate -1,Administrator) 22:19:05 Installation Failed. Has anyone had this problem? Any ideas about how to fix it? Thanks very much, Michael

    Read the article

  • Exchange 2003 SP2 and Windows Server 2008 R2 Domain Controllers

    - by Brian
    I'm looking at adding two Windows Server 2008 R2 Domain Controllers into our Windows Server 2003 domain to support our Exchange 2003 SP2 server and replace a retiring Windows Server 2003 Server. Our Domain and Forest functional levels are currently Windows Server 2003, which supports domain controller operating systems (Windows Server 2008 R2, Windows Server 2008 and Windows Server 2003) according to the "Appendix of Functional Level Features" on Technet . So there should not be an issue other than running adprep /forestprep and adprep /domain.... right!? But, according to the Exchange Server Supportability Matrix, Windows Server 2008 R2 Active Directory Servers are not supported as global catalog servers or domain controllers in a Exchange 2003 SP2 environment!!!??? This was a shock to me... How can Windows Server 2008 R2 be a DC for a Windows Server 2003 domain and forest, but not communicate with an Exchange 2003 SP2 server? Hopefully, I'm not the first to see this issue (or maybe I am), but I know a lot of Exchange 2003 admins will not be happy if there is not a work around... or is Microsoft trying to push everyone automatically to Exchange 2010...

    Read the article

  • How to run VisualSvn Server on port 443 running IIS on same server?

    - by Metro Smurf
    Server 2008 R2 SP1 VisualSvn Server 2.1.6 The IIS server has about 10 sites. One of them uses https over port 443 with the following bindings: http x.x.x.39:80 site.com http x.x.x.39:80 www.site.com https x.x.x.39:443 VisualSvn Server Properties server name: svn.SomeSite.com server port: 443 Server Binding: x.x.x.40 No sites on IIS are listening to x.x.x.40. When starting up VisualSvn server, the following errors are thrown: make_sock: could not bind to address x.x.x.40:443 (OS 10013) An attempt was made to access a socket in a way forbidden by its access permissions. no listening sockets available, shutting down When I stop Site.com on IIS, then VisualSvn Server starts up without a problem. When I bind VisualSvn server to port 8443 and start Site.com, then VisualSvn Server starts without a problem. My goal is to be able to access the VisualSvn Server with a normal url, i.e., one that does't use a port number in the address: https://svn.site.com vs https://svn.site.com:8443 What needs to be configured to allow VisualSvn Server to run on port 443 with IIS running on the same server?

    Read the article

  • Moving Microsoft Exchange server to the private network.

    - by Alexey Shatygin
    In one of the offices, we have a 50-computers network, which had only one server machine: Windows 2003 Server Microsoft ISA Server Microsoft Exchange 2003 This server worked as a gateway (proxy server), mail server, file server, firewall and domain controller. It had two network interfaces, one for WAN (let's say 222.222.222.222) and one for LAN (192.168.1.1). I set up a Linux box to be the gateway (without a proxy), so the Linux box now has the following interfaces: 222.222.222.222 (our external IP, we removed it from the Windows machine) and 192.168.1.100 (internal IP), but we need to keep the old Windows server as a mail server and a proxy for some of our users, until we prepare another Linux machine for that, so I need the mail server on that machine to be available from the Internet. I set up iptables rules to redirect all the incoming connections on the 25th and 110th ports of our external IP to 192.168.1.1:25 and 192.168.1.1:110 and when I try to telnet our SMTP service telnet 222.222.222.222 25 I get the greetings from our windows server's (192.168.1.1) SMTP service, and that's works fine. But when I telnet POP3 service telnet 222.222.222.222 110 I only get the blank black screen and the connection seem to disappear if I press any button. I've checked the ISA rules - everything seems to be the same for 110th and 25th ports. When I telnet on 110th ports of our Windows server from our new gateway machine like this: telnet 192.168.1.1 110 I get the acces to it's POP3 service: +OK Microsoft Exchange Server 2003 POP3 server version 6.5.7638.1 (...) ready. What sould I do, to make the POP3 service available through our new gateway?

    Read the article

  • User (MS-Office) generated content - how?

    - by Avi
    How can I allow users to share Microsoft Office generated content on an ASP.Net site? For example usage, imagine a site similar to StackOverflow. George, writing a question, uses Word, Excel or OneNote to create content, and then inserts the content into the question area (probably copying it into the clipboard and then using some "paste from office" widget). Harry, who doesn't have MS-Office on his computer, can still see in his browser the content George has generate. If Harry wants to add content, he can use the built in editor, same like in Stackoverflow, and have to be satisfied with lesser functionality. Sue, who has MS-Office installed, can of course see the content in the browser just like Harry. In addition, she can "export" this content and process it in the application George used to generate it. So, how do I do it? Would Save/Export to HTML feature work? Any tools? Samples? Articles? Office 2007 or later is OK.

    Read the article

  • SQL Server Import table keeping default values

    - by Chrissi
    I am importing a table from one database to another in SQL Server 2008 by right-clicking the target database and choosing Tasks Import Data... When I import the table I get the column names and types and all the data fine, but I lose the primary key, identity specifications and all the default values that were set in the source table. So now I have to set all the default values for each column again manually. Is there any way to get the default values with the import, or even after with a Query? I am VERY new to this and flailing in the dark, so forgive me if this is a really stupid question...

    Read the article

  • MS Access Bulk Insert exits app

    - by Brij
    In the web service, I have to bulk insert data in MS Access database. First, There was single complex Insert-Select query,There was no error but app exited after inserting some records. I have divided the query to make it simple and using linq to remove complexity of query. now I am inserting records using for loop. there are approx 10000 records. Again, same problem. I put a breakpoint after loop, but No hit. I have used try catch, but no error found. For Each item In lstSelDel Try qry = String.Format("insert into Table(Id,link,file1,file2,pID) values ({0},""{1}"",""{2}"",""{3}"",{4})", item.WebInfoID, item.Links, item.Name, item.pName, pDateID) DAL.ExecN(qry) Catch ex As Exception Throw ex End Try Next Public Shared Function ExecN(ByVal SQL As String) As Integer Dim ret As Integer = -1 Dim nowConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "DataBase\\mydatabase.mdb;" Dim nowCon As System.Data.OleDb.OleDbConnection nowCon = New System.Data.OleDb.OleDbConnection(nowConString) Dim cmd As New OleDb.OleDbCommand(SQL, nowCon) nowCon.Open() ret = cmd.ExecuteNonQuery() nowCon.Close() nowCon.Dispose() cmd.Dispose() Return ret End Function After exiting app, I see w3wp.exe uses more than 50% of Memory. Any idea, What is going wrong? Is there any limitation of MS Access?

    Read the article

  • SQL Server Error 26 (Error Locating Server/Instance Specified) Only When Debugging

    - by Phsion
    I have a simple asp .net web project I'm working on, and while normally everything is going fine, if I try to debug the site, I get "SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified". I'm not sure if its a weird code issue or a weird SQL Server Issue, but here are the system specs anyway. My dev machine is Windows 7 32-bit, and a Windows Server 2008 VM (on the same Windows 7 Machine) runs both IIS and SQL Server 2008 Express. I haven't had any other problems with the connection string I'm using, and I'm using the simple SA account. Anyhow, thanks for the help.

    Read the article

  • COM+ Applications returns an error when when I try to add a new application

    - by Baright
    Error message returned 'An error occurred while processing the last operation. Error code 800401154 - Class not registered The event log may contain additional troubleshooting information.' The other thing is, the is a red arrow icon displayed over My Computer (Component Servers - Computers - My Computer). I have searched everywhere for this, but I couldnt find a solution that resolves my specific problem. Im using VISTA and this error started after I reinstalled my SQL server 2008. I have 2 dll that I need to add but I cannot, because of this error.

    Read the article

  • sql server db deployment script ignoring constraints etc until commit

    - by Daniel
    Hi all, I am planning on doing a database deployment script for sql server 2005. Currently we have a tool that will run all of the tables, foreign keys, indexes and then data, each of which is located in a separate file with a certain extension (eg. tab, .kci, .fky) and the tool just runs *.tab, *.kci, *.fky into the db etc. Could I possibly combine all of thse into one file and have them run ignoring referential integrity until they are all complete, I would turn it on before we started inserting test data. It is just unmanageable having to maintain 4 or 5 different types of scripts for one table. Are there any issues I should be aware of? Cheers

    Read the article

  • Emulating a computer running MS-DOS

    - by Richard
    Writing emulators has always fascinated me. Now I want to write an emulator for an IBM PC and run MS-DOS on it (I've got the floppy image files). I have good experience in C++ and C and basic knowledge of assembler and the architecture of a CPU. I also know that there are thousands of emulators out there doing exactly what I want to do, but I'd be doing this for pure joy only. How much work do I have to expect? (If my goal is to boot DOS and create a text file with it, all emulated) What CPU should I emulate ? Where can I find documentation on how the machine code is organized and which opcodes mean what, so I can unpack and execute them correctly with my emulator? Does MS-DOS still run on the newest generations of processors? Would it theoretically be able to natively run on a 64-bit AMD Phenom 2 processor w/ a modern mainboard, HDD, RAM, etc.? What else, besides emulating the CPU, could be an important factor (in terms of difficulty)? I would only aim for outputting / inputting text to the system via the host system's console, no sound or other more advanced IO etc. Have you written an emulator yet? What was your first one for? How hard was it? Do you have any special tips for me? Thanks in advance

    Read the article

  • SQL Server 2005: Rename DB Server Instance Name?

    - by Code Sherpa
    Hi, Can somebody tell me how to rename the DB server instance name and a DB name in SQL Server 2005? Right Now I Have SERVER/OLDNAME -- oldnameDB I want to change the server instance and also change the db name. I have tried: EXEC sp_renamedb 'oldName', 'newName' and that has changed the dbname as it appers in the tree directory. But, when I do "select @@servername" it is the old name. Also, the MDF and LDF files are still the old name. How do change instance and db names as a clean sweep across the server? Thanks.

    Read the article

  • Migrating from SQL Trace to Extended Events

    - by extended_events
    In SQL Server codenamed “Denali” we are moving our diagnostic tracing capabilities forward by building a system on top of Extended Events. With every new system you face the specter of migration which is always a bit of a hassle. I’m obviously motivated to see everyone move their diagnostic tracing systems over to the new extended events based system, so I wanted to make sure we lowered the bar for the migration process to help ease your trials. In my initial post on Denali CTP 1 I described a couple tables that we created that will help map the existing SQL Trace Event Classes to the equivalent Extended Events events. In this post I’ll describe the tables in a bit more details, explain the relationship between the SQL Trace objects (Event Class & Column) and Extended Event objects (Events & Actions) and at the end provide some sample code for a managed stored procedure that will take an existing SQL Trace session (eg. a trace that you can see in sys.Traces) and converts it into event session DDL. Can you relate? In some ways, SQL Trace and Extended Events is kind of like the Standard and Metric measuring systems in the United States. If you spend too much time trying to figure out how to convert between the two it will probably make your head hurt. It’s often better to just use the new system without trying to translate between the two. That said, people like to relate new things to the things they’re comfortable with, so, with some trepidation, I will now explain how these two systems are related to each other. First, some terms… SQL Trace is made up of Event Classes and Columns. The Event Class occurs as the result of some activity in the database engine, for example, SQL:Batch Completed fires when a batch has completed executing on the server. Each Event Class can have any number of Columns associated with it and those Columns contain the data that is interesting about the Event Class, such as the duration or database name. In Extended Events we have objects named Events, EventData field and Actions. The Event (some people call this an xEvent but I’ll stick with Event) is equivalent to the Event Class in SQL Trace since it is the thing that occurs as the result of some activity taking place in the server. An  EventData field (from now on I’ll just refer to these as fields) is a piece of information that is highly correlated with the event and is always included as part of the schema of an Event. An Action is something that can be associated with any Event and it will cause some additional “action” to occur when ever the parent Event occurs. Actions can do a number of different things for example, there are Actions that collect additional data and, take memory dumps. When mapping SQL Trace onto Extended Events, Columns are covered by a combination of both fields and Actions. Knowing exactly where a Column is covered by a field and where it is covered by an Action is a bit of an art, so we created the mapping tables to make you an Artist without the years of practice. Let me draw you a map. Event Mapping The table dbo.trace_xe_event_map exists in the master database with the following structure: Column_name Type trace_event_id smallint package_name nvarchar xe_event_name nvarchar By joining this table sys.trace_events using trace_event_id and to the sys.dm_xe_objects using xe_event_name you can get a fair amount of information about how Event Classes are related to Events. The most basic query this lends itself to is to match an Event Class with the corresponding Event. SELECT     t.trace_event_id,     t.name [event_class],     e.package_name,     e.xe_event_name FROM sys.trace_events t INNER JOIN dbo.trace_xe_event_map e     ON t.trace_event_id = e.trace_event_id There are a couple things you’ll notice as you peruse the output of this query: For the most part, the names of Events are fairly close to the original Event Class; eg. SP:CacheMiss == sp_cache_miss, and so on. We’ve mostly stuck to a one to one mapping between Event Classes and Events, but there are a few cases where we have combined when it made sense. For example, Data File Auto Grow, Log File Auto Grow, Data File Auto Shrink & Log File Auto Shrink are now all covered by a single event named database_file_size_change. This just seemed like a “smarter” implementation for this type of event, you can get all the same information from this single event (grow/shrink, Data/Log, Auto/Manual growth) without having multiple different events. You can use Predicates if you want to limit the output to just one of the original Event Class measures. There are some Event Classes that did not make the cut and were not migrated. These fall into two categories; there were a few Event Classes that had been deprecated, or that just did not make sense, so we didn’t migrate them. (You won’t find an Event related to mounting a tape – sorry.) The second class is bigger; with rare exception, we did not migrate any of the Event Classes that were related to Security Auditing using SQL Trace. We introduced the SQL Audit feature in SQL Server 2008 and that will be the compliance and auditing feature going forward. Doing this is a very deliberate decision to support separation of duties for DBAs. There are separate permissions required for SQL Audit and Extended Events tracing so you can assign these tasks to different people if you choose. (If you’re wondering, the permission for Extended Events is ALTER ANY EVENT SESSION, which is covered by CONTROL SERVER.) Action Mapping The table dbo.trace_xe_action_map exists in the master database with the following structure: Column_name Type trace_column_id smallint package_name nvarchar xe_action_name nvarchar You can find more details by joining this to sys.trace_columns on the trace_column_id field. SELECT     c.trace_column_id,     c.name [column_name],     a.package_name,     a.xe_action_name FROM sys.trace_columns c INNER JOIN    dbo.trace_xe_action_map a     ON c.trace_column_id = a.trace_column_id If you examine this list, you’ll notice that there are relatively few Actions that map to SQL Trace Columns given the number of Columns that exist. This is not because we forgot to migrate all the Columns, but because much of the data for individual Event Classes is included as part of the EventData fields of the equivalent Events so there is no need to specify them as Actions. Putting it all together If you’ve spent a bunch of time figuring out the inner workings of SQL Trace, and who hasn’t, then you probably know that the typically set of Columns you find associated with any given Event Class in SQL Profiler is not fix, but is determine by the contents of the table sys.trace_event_bindings. We’ve used this table along with the mapping tables to produce a list of Event + Action combinations that duplicate the SQL Profiler Event Class definitions using the following query, which you can also find in the Books Online topic How To: View the Extended Events Equivalents to SQL Trace Event Classes. USE MASTER; GO SELECT DISTINCT    tb.trace_event_id,    te.name AS 'Event Class',    em.package_name AS 'Package',    em.xe_event_name AS 'XEvent Name',    tb.trace_column_id,    tc.name AS 'SQL Trace Column',    am.xe_action_name as 'Extended Events action' FROM (sys.trace_events te LEFT OUTER JOIN dbo.trace_xe_event_map em    ON te.trace_event_id = em.trace_event_id) LEFT OUTER JOIN sys.trace_event_bindings tb    ON em.trace_event_id = tb.trace_event_id LEFT OUTER JOIN sys.trace_columns tc    ON tb.trace_column_id = tc.trace_column_id LEFT OUTER JOIN dbo.trace_xe_action_map am    ON tc.trace_column_id = am.trace_column_id ORDER BY te.name, tc.name As you might imagine, it’s also possible to map an existing trace definition to the equivalent event session by judicious use of fn_trace_geteventinfo joined with the two mapping tables. This query extracts the list of Events and Actions equivalent to the trace with ID = 1, which is most likely the Default Trace. You can find this query, along with a set of other queries and steps required to migrate your existing traces over to Extended Events in the Books Online topic How to: Convert an Existing SQL Trace Script to an Extended Events Session. USE MASTER; GO DECLARE @trace_id int SET @trace_id = 1 SELECT DISTINCT el.eventid, em.package_name, em.xe_event_name AS 'event'    , el.columnid, ec.xe_action_name AS 'action' FROM (sys.fn_trace_geteventinfo(@trace_id) AS el    LEFT OUTER JOIN dbo.trace_xe_event_map AS em       ON el.eventid = em.trace_event_id) LEFT OUTER JOIN dbo.trace_xe_action_map AS ec    ON el.columnid = ec.trace_column_id WHERE em.xe_event_name IS NOT NULL AND ec.xe_action_name IS NOT NULL You’ll notice in the output that the list doesn’t include any of the security audit Event Classes, as I wrote earlier, those were not migrated. But wait…there’s more! If this were an infomercial there’d by some obnoxious guy next to me blogging “Well Mike…that’s pretty neat, but I’m sure you can do more. Can’t you make it even easier to migrate from SQL Trace?”  Needless to say, I’d blog back, in an overly excited way, “You bet I can' obnoxious blogger side-kick!” What I’ve got for you here is a Extended Events Team Blog only special – this tool will not be sold in any store; it’s a special offer for those of you reading the blog. I’ve wrapped all the logic of pulling the configuration information out of an existing trace and and building the Extended Events DDL statement into a handy, dandy CLR stored procedure. Once you load the assembly and register the procedure you just supply the trace id (from sys.traces) and provide a name for the event session. Run the procedure and out pops the DDL required to create an equivalent session. Any aspects of the trace that could not be duplicated are included in comments within the DDL output. This procedure does not actually create the event session – you need to copy the DDL out of the message tab and put it into a new query window to do that. It also requires an existing trace (but it doesn’t have to be running) to evaluate; there is no functionality to parse t-sql scripts. I’m not going to spend a bunch of time explaining the code here – the code is pretty well commented and hopefully easy to follow. If not, you can always post comments or hit the feedback button to send us some mail. Sample code: TraceToExtendedEventDDL   Installing the procedure Just in case you’re not familiar with installing CLR procedures…once you’ve compile the assembly you can load it using a script like this: -- Context to master USE master GO -- Create the assembly from a shared location. CREATE ASSEMBLY TraceToXESessionConverter FROM 'C:\Temp\TraceToXEventSessionConverter.dll' WITH PERMISSION_SET = SAFE GO -- Create a stored procedure from the assembly. CREATE PROCEDURE CreateEventSessionFromTrace @trace_id int, @session_name nvarchar(max) AS EXTERNAL NAME TraceToXESessionConverter.StoredProcedures.ConvertTraceToExtendedEvent GO Enjoy! -Mike

    Read the article

  • Latency between IIS and SQL on same physical, two VMs

    - by Jerad Rose
    I have a single server (2x4 core CPUs, 32GB ram), that is a Windows Server 2012 Hyper V host, and it hosts two guest VMs (also Windows Server 2012 instances). One of them is a web server, the other is a SQL server. When hitting a page that loops over 50 records, there is noticeable latency. I capture/report the timings of each iteration on the loop, and each iteration is about 20-30 milliseconds. Of course, this amounts to over a second of latency for the whole loop. I thought maybe SQL needed to be tuned, but running profiler on it, the queries are showing almost 0 duration, so it seems the bottleneck is in transit between the two VMs. I have both VMs configured to use the actual NIC (vs. using a VNIC), so maybe that's part of my problem. Also, this is a classic ASP site, so it's using the SQL OLE DB provider, and I'm wondering if that is part of the problem. This is a new server setup, from an existing Windows 2003/IIS6 server setup where both web and DB run on the same server instance (no virtualization). On that setup, there is no such latency when looping over the cursor like this. But there are so many variables, I'm not sure where to start ruling things out.

    Read the article

< Previous Page | 182 183 184 185 186 187 188 189 190 191 192 193  | Next Page >