dePoPo.net

UPDATE a field from a different table

This snippet retrieves the value from table2.field2, and places it in table1.field1.

update table1 set table1.field1 = table2.field2
from table1,table2
where table1.key = table2.key

UPDATE a field with a SUM from a different table

This snippet adds up multiple entry’s in the second table which have the correct key, and places the sum in the first table. This technique is usefull for totaling seperate lines in table2 into a different table.

update table1 set field1 = ( select sum(table2.field2) from table2 where table1.key = table2.key)

When writing long query’s or stored procedures using temporary tables, you may run into an error like:

Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

The cause of this error is that the tables you are querying against have a different collation then the server default. This causes the temporary tables in TempDb.. to have a different collation too.

To specify the collation your database has during the creation of your temptable, implemenet it like this:

-- Clear out temp. table if it already exists
begin try
	drop table #sometable
end try
begin catch
end catch

-- Create with enforced collation
create table #sometable (
field1 nvarchar(50) collate Latin1_General_CI_AS,
field2 nvarchar(50) collate Latin1_General_CI_AS,
.....
)

-- These examples display the use of the JOIN command to select matching
-- or non matching records
-- Written using the AdventureWorks database for SQL Server 2005,
-- The syntax is generic enough to be used on basicaly any version of MS SQL Server
-- or MySQL

-- Display all sales orders 

select sh.SalesOrderID, sh.OrderDate, sh.CustomerID from Sales.SalesOrderHeader as sh

-- Display the list of customers that have one or more sales attached
-- Each other that we find for the customer is listed on a seperate line.
-- the LEFT JOIN selects only records from the tables that have a match in BOTH
-- tables, so a Customer with no order attached will NOT be listed. 

select cu.* from Sales.Customer as cu
left join Sales.SalesOrderHeader as sh on cu.CustomerID = sh.CustomerID

-- Display the number of sales orders each customer has, sorted by the number of orders.
-- We COUNT the number of records in the SalesOrderHeader table for each unique
-- CustomerID.
-- The group by clause makes sure we list each CustomerID only once

select cu.CustomerID, count(sh.CustomerID) as NumOfSales from Sales.Customer as cu
left join Sales.SalesOrderHeader as sh on cu.CustomerID = sh.CustomerID
group by cu.CustomerID
order by NumOfSales DESC

-- Display the customers that have no sales orders attached
-- the LEFT OUTER JOIN forces the SQL server to include the rows in Sales.Customer regardless of a match
-- in the SalesOrderHeader table. Therefore, we can now select on the CustomerID field being NULL in
-- the SalesOrderHeader tabel.

select cu.* from Sales.Customer as cu
left outer join Sales.SalesOrderHeader as sh on cu.CustomerID = sh.CustomerID
where sh.CustomerID is null

Run the following on your master database

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

source: http://www.sqlservercurry.com/2008/03/how-to-see-active-connections-for-each.html

When publishing a web application which includes an integrated SQL database you receive the following error:

Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

Cause:
The user identity your application is trying to create an instance of the SQL server with, does not have a profile on the computer. You need to change this identity to a valid user.

Resolution:

a) If you are already impersonating a non system account, log in to the local system using that username. This will create the missing profile.

b) If you are currently using a system account for the connection, change it to a valid user account on the machine. To do that: In IIS Manager, change the Apllication Pool identioty to a valid local user with a profile

This example shows how to create a stored procedure in an SQL Server database which can select data from various tables, combine the results as a jon query, and make that data available as a table object in memory for further processing. Additionaly a paramter is passed to the query from the code to influence the results.

The example uses the AdventureworksLT database.

The first code block is the stored procedure that query’s some data and builds a result set.

create procedure sp_demoquery(@ignorecust int)
as

-- Discard temp tables if they still exist in this session
IF Object_ID('tempdb..#selection1') IS NOT NULL DROP TABLE #selection1
IF Object_ID('tempdb..#selection2') IS NOT NULL DROP TABLE #selection2

-- Query 1 deliverig the first half of our data
create table #selection1 (salesorderid int, orderdate datetime, salesordernumber  nvarchar(25))
insert into #selection1 (salesorderid, orderdate, salesordernumber)
select SalesOrderID, OrderDate,SalesOrderNumber from SalesLT.SalesOrderHeader
where CustomerID <> @ignorecust

-- Query 2 delivering the second half of our data
create table #selection2 (salesid int, billid int, shipid int)
insert into #selection2 (salesid, billid, shipid)
select SalesOrderID as salesid, BillToAddressID as billid, ShipToAddressID as shipid from SalesLT.SalesOrderHeader
where CustomerID <> @ignorecust

-- The resulting join dataset to be returned from the stored procedure
select * from #selection1 as s1
join #selection2 as s2 on s1.salesorderid = s2.salesid
order by salesorderid

The result of the procedure as shown in the sql server manager:

Finaly, the code to execute the stored procedure in the database, and construct the table object holding the resturned data. In it’s simples form we could construct and type a table manualy and then use a datareader to loop the resultsets and fill the table up. This would result in this code:

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' --- Create a table to hold the result set
        Dim oTable As New DataTable
        oTable.Columns.Add("salesordid", Type.GetType("System.Int32"))
        oTable.Columns.Add("orderdate", Type.GetType("System.Date"))
        oTable.Columns.Add("salesordernumber", Type.GetType("System.String"))
        oTable.Columns.Add("salesid", Type.GetType("System.Int32"))
        oTable.Columns.Add("billid", Type.GetType("System.Int32"))
        oTable.Columns.Add("shipid", Type.GetType("System.Int32"))
        ' //

        ' --- Run the stored procedure and retrieve the data
        Using oCon As New SqlConnection(My.Settings.ConString)
            oCon.Open()
            Using oCmd As New SqlCommand("sp_demoquery")
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.Parameters.AddWithValue("@ignorecust", 31100)
                Using oReader As SqlDataReader = oCmd.ExecuteReader
                    If oReader.HasRows = True Then
                        While oReader.Read

                            ' --- Create a new row for our table and retrieve data from the query
                            Dim oRow As DataRow = oTable.NewRow
                            oRow("salesordid") = oReader("salesorderid").ToString
                            oRow("orderdate") = oReader("orderdate").ToString
                            oRow("salesordernumber") = oReader("salesordernumber").ToString
                            oRow("salesid") = oReader("salesid").ToString
                            oRow("billid") = oReader("billid").ToString
                            oRow("shipid") = oReader("shipid").ToString
                            oTable.Rows.Add(oRow)

                        End While
                    End If
                End Using
            End Using
        End Using

        ' --- oTable now holds the results of the stored procedure

        ' Perform your processing here

        ' //

        oTable.Dispose()

    End Sub

End Class

Continuing along the same line it is also possible to retrieve the results as a generic datatable object which you can use for further processing. We can also pass parameters to the stored procedure if they are required. To retrieve the output of a strored procedure taking two parameters and return the results as a datatable the code would look as follows:

        Using oCon As New SqlConnection(My.Settings.ConString)
            oCon.Open()
            Using oDa As New SqlDataAdapter
                oDa.SelectCommand = New SqlCommand("sp_myprocedure", oCon)
                oDa.SelectCommand.CommandType = CommandType.StoredProcedure
                oDa.SelectCommand.Parameters.AddWithValue("@parm1", nSomeVal)
                oDa.SelectCommand.Parameters.AddWithValue("@parm2", nSomeVal)
                Using oDs As New DataSet
                    oDa.Fill(oDs, "results")
                    '  --- Processing here
                    ' MicroTools.ExcelWriter.Write(oDs)
                End Using
            End Using
        End Using

Solution #1;

Start regedit,

locate: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

rename the key: PendingFileRenameOperations to PendingFileRenameOperations2
Re-run the SQL Server 2008 installation without rebooting.
Rename the key back
Solution #2;

Additionaly, the following keys can contain values with pending actions for a reboot:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunOnce
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunOnceEx
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunServicesOnce
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\RunOnce
Solution #3;

(This seems to be the most successfull one on Vista64 systems)

Start regedit, and use the search funtion (F3) to locate ALL instances of PendingFileRenameOperations
Clear or rename the keys
Rerun the setup WITHOUT prior reboot

The ASP.NET gridview is bound to a standard sql selection query, so to select a daterange you can configure the bound SQLDataSource and do something like this:

' --- Select item from the last 7 days
SELECT        id, item_header, item_date, item_cat
FROM           items
WHERE        (item_date >= DATEADD(day, - 7, GETDATE()))
ORDER BY item_date DESC, id DESC

' --- Or a range
SELECT        id, item_header, item_date, item_cat
FROM           items
WHERE        (item_date BETWEEN DATEADD(day, - 14, GETDATE()) AND DATEADD(day, - 7, GETDATE()))
ORDER BY item_date DESC, id DESC