Alternate to location.href

Hi all,
Actually the location.href functionality is some times not working in firefox . So we can use the following way is the alternative for "location.href."

top.window.opener.location.href

...S.VinothkumaR.

OPENDATASOURCE

It provides ad hoc connection information as part of four – part object name without using a linked server name.

Syntax:
OPENDATASOURCE( provider_name, init_string )

provider_name is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

init_string is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, that is, "keyword1=value; keyword2=value."

The following lists the most commonly used keywords in the init_string argument.

Keyword : Data Source
OLE DB property : DBPROP_INIT_DATASOURCE
Valid values and Description :
Name of the data source to connect to. Different providers interpret this in different ways. For SQL Server OLE DB provider, this indicates the name of the server. For Jet OLE DB provider, this indicates the full path of the .mdb file or .xls file.

Keyword : Location
OLE DB property : DBPROP_INIT_LOCATION
Valid values and Description : Location of the database to connect to.

Keyword : Extended Properties
OLE DB property : DBPROP_INIT_PROVIDERSTRING
Valid values and Description : The provider-specific connect-string.

Keyword : Connect timeout
OLE DB property : DBPROP_INIT_TIMEOUT
Valid values and Description : Time-out value after which the connection attempt fails.

Keyword : User ID
OLE DB property : DBPROP_AUTH_USERID
Valid values and Description : User ID to be used for the connection.

Keyword : Password
OLE DB property : DBPROP_AUTH_PASSWORD
Valid values and Description : Password to be used for the connection.

Keyword : Catalog
OLE DB property : DBPROP_INIT_CATALOG
Valid values and Description : The name of the initial or default catalog when connecting to the data source.

Examples
This example accesses data from a table on another instance of SQL Server.

SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=ServerName;User ID=MyUID;Password=MyPass').Northwind.dbo.Categories

This is an example of a query against an Excel spreadsheet through the OLE DB provider for Jet.

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

...S.VinothkumaR.

How to import data from Excel to SQL Server?

We can import data from Excel to SQL Server by using SQL Server distributed queries.

We can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples will help us to import data from excel customer’s worksheet into new SQL Server tables.

SELECT * INTO tblXLImport1 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO tblXLImport2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', [Customers$])

SELECT * INTO tblXLImport3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', 'SELECT * FROM [Customers$]')

//Here Customers$ means the name of worksheet.

Simple sample query:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\students.xls;Extended Properties=Excel 8.0')...[Students$]

...S.VinothkumaR.

Simple Cursor Programme

Hi all,
Here is a simple sample for cursor.

declare testCur CURSOR For //Cursor declaring for

select top 10 * from tblUsers

Open testCur //Open the cursor

Fetch next from testCur //Fetch the first record

while @@FETCH_STATUS = 0

Fetch next from testCur //Fetch the next record while status = 0

CLOSE testCur //Close cursor

deallocate testCur //Deallocate the cursor.

...S.VinothkumaR.

Grant permission to all stored procedures.

Hi all,

I am continuing my posts here for a long gape...

Here is a query for granting permission to all stored procedures in a database. Check it out...


create PROCEDURE stpGrantPermissionToAllSP @user sysname
AS
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)

-- 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P'
AND o.[Name] NOT LIKE 'dt_%'

-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN

-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID

-- 7 - Build the string
SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

-- 8 - Execute the string
-- SELECT @CMD1

-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
exec stpGrantPermissionToAllSP 'username'

...S.VinothkumaR.

DataTable,DataRow,DataView sorting.

Hi all,

Just check the data sorting by using dataview in below.


SqlConnection sql=new SqlConnection("Connection String");
SqlCommand cmd=new SqlCommand("SampleProcedure",sql);
cmd.CommandType=CommandType.StoredProcedure;
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
if(ds.Tables[0].Rows.Count>0)
{
DataTable dt=new DataTable();
dt.Columns.Add("Score",typeof(double));
dt.Columns.Add("Name",typeof(string));
DataRow dr;
foreach(DataRow drow in ds.Tables[0].Rows)
{
dr = dt.NewRow();
dr["Score"] = drow ["intScore"];
dr["Name"]=drow ["strGameName"];
dt.Rows.Add(dr);
}
DataView dv=dt.DefaultView;
dv.Sort="Score desc";
DataGrid drg=new DataGrid();
drg.DataSource=dv; //drg is DataGrid
drg.DataBind();
this.Controls.Add(drg);
}

...S.VinothkumaR

Double Formatting.

Here is sample for double formatting by using C#.

Double dblScore = 2.0;
string str = dblScore.ToString("#,##0.0;(#,##0.0);00.0");

...S.Vinothkumar.