Saturday, August 11, 2012
Sql server how to find currently running query's
We will face an issue like a sql server memory will keep on increase, in that situation we can easily find the issue using following query.
Following query will give how many time query executed and total elapsed time .
SELECT
qs.execution_count,
DatabaseName = DB_NAME(qp.dbid),
ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),
StatementDefinition =
SUBSTRING (
st.text,
(
qs.statement_start_offset / 2
) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
),
query_plan,
st.text, total_elapsed_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE
st.encrypted = 0
ORDER BY qs.execution_count DESC
Tuesday, February 14, 2012
sql rows to columns comma separated values
We can convert rows to column using following
querys.
in case we have more rows then we can use
following query.( only 500000000 characters)
--Sql rows to
column
--only 500000000
characters--If you want more rows,then you can use the following query
select SUBSTRING((SELECT(', ' + CAST(Columname AS varchar(1000)) ) FROM
tablename FOR XML PATH('')),3,500000000)
in case we have less rows then we can use
following query.( only 8000 characters)
--If you want less rows , then you can use the following query--only8000
characters
DECLARE @retstr varchar(MAX) SELECT @retstr
=
COALESCE(@retstr + ',','') + [Columname] FROM tablename
select @retstr
Download remote file using asp.net and C#.net
Download remote file using C#.net in windows application.
Following code will help for downloading remote file.
private
static int DownloadFile(String remoteFilename, String localFilename)
{
int
bytesProcessed = 0;
Stream
remoteStream = null;
Stream
localStream = null;
WebResponse
response = null;
try
{
WebRequest
request = WebRequest.Create(remoteFilename);
if
(request != null)
{
response
= request.GetResponse();
if
(response != null)
{
remoteStream
= response.GetResponseStream();
localStream
= File.Create(localFilename);
byte[]
buffer = new byte[1024];
int
bytesRead;
do
{
bytesRead
= remoteStream.Read(buffer, 0, buffer.Length);
localStream.Write(buffer,
0, bytesRead);
bytesProcessed
+= bytesRead;
}
while (bytesRead > 0);
}
}
}
catch
(Exception ex)
{
string
strMsg = ex.Message;
}
finally
{
if
(response != null) response.Close();
if
(remoteStream != null) remoteStream.Close();
if
(localStream != null) localStream.Close();
}
return
bytesProcessed;
}
}
For web application following
method code will help
private
Void DownloadFile()
{
string
remoteImageUrl = "http://www.toptechreviews.net/wp-content/uploads/2011/06/Microsoft.png";
string
strRealname = Path.GetFileName(remoteImageUrl);
string
exts = Path.GetExtension(remoteImageUrl);
WebClient
webClient = new WebClient();
webClient.DownloadFile(remoteImageUrl,
Server.MapPath("~/im/")
+ strRealname + exts);
}
in web.config we need set
following configuration
<defaultProxy enabled="true"
useDefaultCredentials="true"></defaultProxy>
</system.net>