sql-server

NLog - DDL for Log Table and Setting the ConnectionString Programmatically

1: Is there a create statement for the Database Target Log Table for all the possible fields documented somewhere? I created one by guessing and I could check the source, but it would be handy if the generic SQL was available. I searched StackOverflow and the NLog site, but the SQL I found was dated and contained incorrect field types....

(Common Table Expressions) CTE as part of WHERE clause... possible?

Is it possible to use CTE in a WHERE clause e.g. SELECT * FROM Table1 WHERE Table1.PK IN ( WITH Cte AS ( -- root selection (dynamic, generated in code) SELECT Bla FROM Table2 -- recursive part UNION ALL SELECT …..) SELECT Bla FROM Cte) The reason I’m asking is that I need to use a recursive query and the ...

How do I find what code is consumming my SQL Server connection pool?

I have rewritten the below based on the answers. I have a website that causes HIGH CPU issues on the database server to the point where the server becomes unavailable. Recycling the app pool fixes the issue. According to the server administrator http://www.microsoft.com/downloads/details.aspx?FamilyID=28bd5941-c458-46f1-b24d-f60151d875a...

Can you configure SQL Server 2008 Auditing to keep the audit data on a database other than the one being audited?

We are planning on using the new auditing feature in SQL Server 2008. Is there a way to configure the auditing component to insert audit data to a separate database? ...

SQL Server - Update a column if row is the first record in group

How do i identify the first row that has value for each Brand and Category, then update Column "FirstValue" as 1, else 0? e.g of expected table Date | Brands | Category | Value | FirstValue Jan 08 | A | 1 | 0 |0 Jan 08 | A | 2 | 0 |0 Jan 08 | A | 3 | 0 |0 Jan 08 | ...

Select distinct values from many tables from a SQL Server query using group by and order by?

Hi I'm trying to select distinct top 10 url, it's count(url) and size from log_table joining other tables too. I tried the following SQL Server query: select distinct top 10 url, count(url) as hits, size as data from log_table where log_table.IP in (select IPAddress from IP where IP.IPId in ...

Storing time values in a database and getting the difference between two time fields

I am working on an application the stores time values in a database e.g Expected time of Arrival and actual time of arrival. What is the best way of storing these fields in a database? What SQL query can I use to obtain the time difference between the two fields? ...

LASt 6 records of the output of the stored procedure

The following is the stored procedure and "I want to fetch the LATEST SIX INVOICES FOR EACH CUSTOMER" THERE COULD BE MORE INVOICES FOR EACH CUSTOMER BUT I HAVE TO FETCH ONLY WHICH ARE LATEST 6 INVOICES. ALTER PROCEDURE [dbo].[SCA_M_CUSTSOINV_REFRESH] @COMP_CD NVARCHAR(20)='', @USER_CD NVARCHAR(20)='', @USER_TYPE NVARCHAR(1)='' ...

help with getting statistics from db in SQL

I'm using SQL-Server 2005. I have two tables Users and Payments which has a foreign key to Users. Both Users and Payments have a date column (Users set their value during registration and Payments gets a value during payment). Users has a column called isPaymentsRecurring as bit which tells me to renew the user or not. The value is 1 on...

Which is faster: JOIN with GROUP BY or a Subquery?

Let's say we have two tables: 'Car' and 'Part', with a joining table in 'Car_Part'. Say I want to see all cars that have a part 123 in them. I could do this: SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id WHERE Car_Part.Part_Id = @part_to_look_for GROUP BY Car.Col1, Car.Col2, Car.Col3 ...

sql server replication: transfer rate, latency, amount of data

Hi! I'm working on a project that requires wireless mssql replication. Does mssql have tools for measurement of transfer rate, latency, amount of data transferred, average and peak values, etc. Or how such measurements are usually performed? ...

converting a string in a time data type

I am making an application which requires entering time into the system. I made the time field in the database as nvarchar(5). When I input data into this field using the form in the application it is entered as a string is there a way in which I can convert it into a string into time format? ...

How to create a basic SQL Server job with SQL Server Express?

Hi, I need to monitor SQL Server jobs and for that I basically have to use sp_help_job. Anyway, the problem is that I don't have any sql job running on my local machine so I can't do any test. I need a simple method to create sql jobs just for this purpose (testing) but I only have SQL Server Management Express Edition which doesn't pro...

SQL Server trace - translating PAGE information to actual resource

I am researching deadlocks that are happening in our application. I turned trace on for 1204, 1205 and 3605. I got the deadlock trace alright. But I am unable to figure out the resource it is deadlocking on. I have read many forums and they all say that the trace should contain something called a KEY/RID which would point to the resource...

Which is best temp. linking method of records which are to be sent to a server for insertion in DB

Hi, [SCENARIO] The scenario is, there is a requirement to send multiple records through XML file to the server, for insertion in the database. These records consists of multiple master and detail tables data linked together through primary and foreign keys. Now the client cannot fill the Primary key and foreign key columns/data in thos...

Running stored procedures

Hi, What's the best way to know which stored procedure are currently running in a database. I have a stored procedure which basically calls other 3 and passes them some parameters and these 3 stored procedures take a long time to complete... I would like to know which one is running... Thanks ...

"Error converting data type varchar to numeric." - What column?

I have a huge INSERT-statement with 200 columns and suddendly I get the dreaded Error converting data type varchar to numeric. Is there somewhere I can see the actual column that contains the "varchar" value? I know I can remove one of the columns at a time until the error disappears, but it's very tedious. ...

asp.net sqlcommand not doing as it should - debugging help req.

Hi, This is a really odd situation that I can't seem to work out where the problem lies. I have a simple ASP textbox and button, on clicking the button I have a simple sqlconnection/command routine perform a simple update to a database based on the text value of the textbox. Code: Using myConnection As SqlConnection = New sqlConnect...

SQL Server timestamp column not supported in Mono?

I am trying to run a .NET command line application in Linux using Mono. It accesses SQL Server 2005 Express database and, when querying some data it throws the following exception: System.NotSupportedException: Unknown Type : timestamp at Mono.Data.Tds.TdsMetaParameter.GetMetaType () [0x00000] at Mono.Data.Tds.Protocol.Tds70.Write...

How can we give multiple alias for a column in SQL?

I have a function call in a query, and that result has to be returned with two names. Example: SELECT myFunction(column1) as Name1, column2 as Name2 FROM myTable I want the result of myFunction to be returned in two different columns. Is this possible without making another function call in the column list? ...