TSQL script to loop round all databases

Hi Guys

Quick script to loop round all databases and perform an action - in this case its setting all databases to simple recovery. The keyword here is sp_msforeachdb - pure genius :)

use master
GO
sp_msforeachdb '
declare @sql VARCHAR(1000)
IF DATABASEPROPERTYEX(''?'', ''Rjavascript:void(0)ecovery'') <> ''SIMPLE''
begin
set @sql = ''ALTER DATABASE ? SET RECOVERY SIMPLE''
PRINT @sql

end'

Uploading Spread into ASp.Net and inserting Data in a spread sheet into Data BAse

Uploading a file using FileUploader


String Name = FileUpload1.FileName.ToString();
if (FileUpload1.PostedFile != null)
{
fn = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
SaveLocation = Server.MapPath(fn);
FileUpload1.PostedFile.SaveAs(SaveLocation);
}

Getting connection to Spread Sheet and SQL to get the data from spread sheet and insert into SQl TABLE(Table NAme: Photo Finding)


String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(fn) + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(sConnectionString);
SqlConnection con1 = new SqlConnection("data source=DCSQL2;initial catalog=gGilesDW;integrated security=SSPI;persist security info=False;Trusted_Connection=Yes");
OleDbDataReader oledbrdr = null;
try
{
con.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", con);
oledbrdr = cmd.ExecuteReader();
con1.Open();
while (oledbrdr.Read())
{
String Status = Convert.ToString(oledbrdr["Status"]);
String id = Convert.ToString(oledbrdr["ID"]);
query = "INSERT INTO PhotoFinding" + "(Status,[ID]) " + " VALUES ('" + Status + "','" + id + "')";
SqlCommand sqlcmd = new SqlCommand(query, con1);
i = sqlcmd.ExecuteNonQuery();
}
if (i == 1)
{
Label2.Text = "Hai Data Base is Updated Sucessfully";
}
else
{
Label2.Text = "The Process of Updating Failed";
}
}

How to access UDF across Servers

How to access UDF across servers!

It seems that this is not possible by using the standard server linkage: ServerName.db.dbo.

This is because you cant qualify the server in a UDF. However to get round this the UDF can be called from the remote server using in sp_executesql.

It is also possible to create a SP on the remote server to handle the call.

SQL Server 2008 Interesting features

SQL Server 2008

Reporting Services

Memory management in SQL Server 2008 Reporting Service is improved. So running large reports will not consume all available memory. In addition, report rendering has more consistency than before.

Encryption

Transparent Data Encryption: the SQL engine encrypts the entire database e.g. all data, log files, Indexes and Tables. Changes to programming applications are not required.

Backup Encryption: Enables encryption of backups to prevent data disclosure or tampering. It can place limitations on the users who are able to restore backups.

External Key Management: Used for involvement with credit card processing or payment card industry compliance. It supports third party Hardware Security Modules used to store keys in a location separate from the data they protect.

Auditing

In addition to the standard auditing of logon/logoffs and permission changes SQL 2008 allows for monitoring of data changes or access. It is configured in TSQL Statements - AUDIT UPDATE(Salary) ON Employee TO MyAuditFolder WHERE Salary>200000

Data Compression

Usually, data compression is associated with general hard disk savings, and with smaller physical files, backup times are reduced. While this holds true for SQL Server Data Compression, the main goal is Fact Table size reduction. The stated advantages for Data Compression include the following:

* Improves query performance by reducing I/O and increasing buffer-hit rates
* Provides compression ratios of 2X to 7X for real DW fact data
* Is available for both data and indexes

According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved because of less IO.

Resource Governor

New in SQL Server 2008 is the Resource Governor. The Governor is used to restrict users or groups of users from consuming high levels of resources. Items that can be monitored include CPU bandwidth, timeout waits, execution times, blocking times, and idle times. If a Resource Governor Threshold level is reached, the system can trigger an event or stop the process.

Performance Data

New in SQL Server 2008 is the Performance Studio. The Studio is a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several colleting methods including TSQL queries, SQL Trace, and Performance Counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options. Microsoft lists these six client side features of the Performance Studio:

* SQL Server dashboard
* Performance monitoring
* Current and historical data analysis
* Data collection sets-based reports

Entity Data Services

SQL Server 2008 and ADO.NET now allow for high level business objects to be created, such as Customers or Parts. These entities can be used rather than the standard method of returning individual rows and tables. If you’re using E-R (entity relationship) modeling, your objects in SQL will now match your modeling. There are several new ADO.NET frameworks that can access these entities such as the Line-of-Business (LOB) framework and the Entity Query Language (eSQL).

Data Synchronizing Features

The combination of SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating synchronizing or frequently disconnected applications, making it easier to create client applications that synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.

Large UDT

Previously, in SQL 2005, User Defined Types (UDT) could not be larger than 8,000 bytes. In SQL 2008 there is no longer any size restriction, allowing storage of very large UDTs.

Dates and Times

There are new Date and Time data types in SQL 2008.

* Date. This is a data type with a date only, no time.
* Time. A Time data type without a date component. Precision can be up to 100 nanoseconds.
* Date Time Offset. This data type will store a Universal Coordinated Time (UTC) time-zone aware value.

File Stream

The new data type VarBinary(Max) FileStream allows for a way to manipulate binary data using TSQL Select, Insert, Update, and Delete statements. In the past, to store binary data a BLOB, accessed by a Dot.Net application was typically used. Now, SQL functions such as triggers, Full Text Search, and backup restore can be applied to binary data.

Spatial Data

The new Spatial Data type allows Latitude, Longitude, and GPS-based data entries to be natively stored inside SQL Server. The data type conforms to several industry standards such as Open Geospatial Consortium (OGC) Simple Features for SQL and ISO 19125 Simple Feature Access.

Table Value Parameters

In previous versions of SQL Server, there wasn’t a native way to pass a table to a stored procedure. The usual workaround was to pass a large varchar or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are available.

Migration Options

SQL Server 2008 provides migration tools to move databases created in previous versions of SQL Server into the new platform. This will allow us to easily upgrade our current databases. It also supports a side-by-side installation with older versions, allowing us to keep legacy databases running without the need to upgrade. However, this will not be an issue for us as our server architecture will include two new standalone SQL Server 2008 machines.

Format uppercase strings in SQL

Hi,

A useful SQL function here. It was written for SQL Server 2000, but I'm sure it can be adapted for any other SQL technologies.

It takes a string and changes it to be uppercase for the first char of every word and lower for the rest of it. E.g. "SQL IS GREAT FUN" becomes "Sql Is Great Fun".

Not much else to say... here it is!

CREATE FUNCTION dbo.ReturnFormattedString(@IN_String varchar(1000))
RETURNS varchar(1000) AS
BEGIN

DECLARE
@NewValue VarChar(1000)
, @X int
, @NewWord bit


SET @x = 1

SET @NewWord = 1

SET @NewValue = ''
WHILE (@x <= Len(@IN_String))
BEGIN

If (@NewWord = 1) and (
(ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
OR (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
)
begin

if (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
SET @NewValue = @NewValue + Char(ASCII(SUBSTRING(@IN_String, @x, 1))-32)
else
SET @NewValue = @NewValue + SUBSTRING(@IN_String, @x, 1)

SET @NewWord = 0
end
else
begin
if (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
SET @NewValue = @NewValue + Char(ASCII(SUBSTRING(@IN_String, @x, 1))+32)
else
SET @NewValue = @NewValue + SUBSTRING(@IN_String, @x, 1)
end



SET @x = @x + 1


if Not
(
(ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
OR (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
)
SET @NewWord = 1
END

RETURN(@NewValue)

END

SQL code to Enable/Disable a Trigger

Useful code to disable and enable a trigger:



ALTER TABLE YourTable DISABLE TRIGGER ALL
and
ALTER TABLE YourTable ENABLE TRIGGER ALL