SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure
December 10, 2006 by pinaldave
Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Reference : Pinal Dave (http://www.SQLAuthority.com)

hi kali, this is the code to load report at runtime da

//code to load report with runtime data source

//dsReportSource -- we need to fill this dataset with our filtered data

CrystalDecisions.CrystalReports.Engine.ReportDocument rptdoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rptdoc.Load(Application.StartupPath + "\\Reports\\ReportFile.rpt");
rptdoc.SetDataSource(dsReportSource);
rptdoc.PrintOptions.PrinterName = PrinterName;
rptdoc.PrintToPrinter(1, false, 0, 0);
rptdoc.Close();
rptdoc.Dispose();
rptdoc = null;
GC.Collect();
GC.WaitForPendingFinalizers();








//strImage0 -- field name having file path
//Image0 -- field name which save image to datatable

//function to load image file in to data table
private void LoadImage(DataTable dtImage)
{
int i = 0;
try
{
for (int nRec = 0; nRec < dtImage.Rows.Count; nRec++)
{

try
{
String strFile = dtImage.Rows[nRec]["strImage" + i.ToString()].ToString();

dtImage.Rows[nRec]["strImage" + i.ToString()] = strFile ;
if (strFilePath != "")
{
dtImage.Rows[nRec]["Image" + i.ToString()] = GetByteArray(strFilePath);
}
}
catch (Exception ex)
{

}

}
}
catch (Exception exception)
{

}

}
public static byte[] GetByteArray(String strFileName)
{
System.IO.FileStream fs = new System.IO.FileStream(strFileName, System.IO.FileMode.Open);
// initialise the binary reader from file streamobject
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
// define the byte array of filelength

byte[] imgbyte = new byte[fs.Length + 1];
// read the bytes from the binary reader

imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));
// add the image in bytearray

br.Close();
// close the binary reader

fs.Close();
// close the file stream
return imgbyte;
}

Read and Write XML

http://mohammedfauzi.blogspot.com/2008/10/sample-to-read-write-xml-file-throught.html
http://www.dotnetspider.com/resources/22457-c-code-for-read-write-xml-file-simple-way.aspx

Sample for table valued function , STRINGSPLITTER

Sample for table valued function

Eg :
SELECT * FROM dbo.stringsplitter('12,14,14,15,as155,1gf1,df', ',')

CREATE FUNCTION [dbo].[stringsplitter]

(

@ParamaterList VARCHAR(MAX),

@Delimiter CHAR(1)

)

RETURNS @ReturnList TABLE

(

FieldValue VARCHAR(MAX)

)

AS BEGIN

DECLARE @ArrayList TABLE

(

FieldValue VARCHAR(MAX)

)

DECLARE @Value VARCHAR(MAX)

DECLARE @CurrentPosition INT

SET @ParamaterList = LTRIM(RTRIM(@ParamaterList))

+ CASE WHEN RIGHT(@ParamaterList, 1) = @Delimiter THEN ''

ELSE @Delimiter

END

SET @CurrentPosition = ISNULL(CHARINDEX(@Delimiter, @ParamaterList, 1), 0)

IF @CurrentPosition = 0

INSERT INTO @ArrayList ( FieldValue )

SELECT @ParamaterList

ELSE

BEGIN

WHILE @CurrentPosition > 0

BEGIN

SET @Value = LTRIM(RTRIM(LEFT(@ParamaterList,

@CurrentPosition - 1))) --make sure a value exists between the delimiters

IF LEN(@ParamaterList) > 0

AND @CurrentPosition <= LEN(@ParamaterList)

BEGIN

INSERT INTO @ArrayList ( FieldValue )

SELECT @Value

END

SET @ParamaterList = SUBSTRING(@ParamaterList,

@CurrentPosition

+ LEN(@Delimiter),

LEN(@ParamaterList))

SET @CurrentPosition = CHARINDEX(@Delimiter,

@ParamaterList, 1)

END

END

INSERT @ReturnList ( FieldValue )

SELECT FieldValue

FROM @ArrayList

RETURN

END

function to build string Ex : Input 1a2b3c Output abbccc

private void btnTest_Click(object sender, EventArgs e)
{
MessageBox.Show(Build(textBox1.Text));
}


private String Build(String strInput)
{
String strOutput = "";
String tChar = "";
int nTimes = 0;
int nLen = 0 ,nPointer=0;
// strInput = "4a";
Char[] strChar = strInput.ToCharArray();
nLen = strChar.Length;
while(nPointer<nLen)
{
nTimes = 0;
bool bInt = true;
while (bInt && nPointer < nLen)
{
int res = 0;
bInt = Int32.TryParse(strChar[nPointer].ToString(),out res);
if(bInt)
{
nTimes = (nTimes * 10) + res;
nPointer++;
}
}
bInt = false;
tChar = "";
while (!bInt && nPointer < nLen)
{
int res = 0;
bInt = Int32.TryParse(strChar[nPointer].ToString(), out res);
if (!bInt)
{
tChar = tChar + strChar[nPointer];
nPointer++;
}
}
for (int n = 0; n < nTimes;n++ )
{
strOutput = strOutput + tChar;
}

}
return strOutput;
}

Loading Excel File C# & VB

HI
this is the code for Loading excel file data to DataSet

String strConn = "";
String str =""; //should assign the name of the excel file
Dataset myDataSet = new DataSet();
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + str + ";" +
"Extended Properties=Excel 8.0;";
myDataSet = new DataSet();
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
myCommand.Fill(myDataSet, "ExcelInfo");


VB Code
Dim strConn As String = ""
Dim str As String = ""

Dim myDataSet As Dataset = New DataSet()
strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + str & ";" & "Extended Properties=Excel 8.0;"
dt = New DataTable()
myDataSet = New DataSet()
Dim myCommand As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
myCommand.Fill(myDataSet, "ExcelInfo")

Msn Lifestyle -Relationship

Keeping your marriage alive
By Meenakshi Shankar

Marriage is something that most of us end up taking for granted. Little of course, realizing the institution needs a lot of work. I still remember before I had kids, we actually went to bed at the same time every night. We spoke about our respective jobs - trivial details which made up our evenings so special. Those little touches, the warm fire beneath those feathery touches made up for a romantic evening.

But all that changed after the baby came along. We just moved into the Phase II of our relationship. There were many nights one of us would take the night shift while the other one slept. As the kids got older, I would sometimes use the after-bedtime hours as my office hours. Wise men/women said "Romance can be challenging when you have children". And I couldn't help but agree.

I recently read an article in which a woman of young children said that sex no longer existed for her and her husband. The truth of the matter is, if a husband and wife don't try to keep the fires burning in their marriage, the stress and demands of having children can sap every scrap of energy until there's nothing left for romance. While being a mother is one of the most important things you'll ever do, having a solid and thrilling marriage is just as important. Kids who see their parents madly in love are more likely to grow up and have strong marriages themselves.

Small things go a long into making your marriage click:

• Before going to bed, make sure that you chat with your partner about his/her work or how was his/her day.
• Reminisce the times you had and try to re-live it.
• Go out on a date once awhile without your kids.
• Try to remember or do the things you used to do for your partner.

• Be more open to your partners needs and feelings.

• Live each day as if it is your last, in that way you would make an effort of being sweeter to your partner.

• Spend less time watching the TV on your own, instead spend time with each other.

• Give something to your partner once in awhile like a certain gift that he/she would appreciate.

• Surprise your partner.

• Take care of yourself and make sure that you look pleasant to his/her eyes.

And if you are in a mood for seduction:

• Set the stage for seduction - Just before he goes to work, give him a long, passionate kiss which holds the promise to suprise and probably forget all about his sexy young thing back at the office.

• Send him a kinky email - Know the time that he's tuned into a meeting and send him something really short and sexy. Don't write a soap opera, but be a tease

• Drop off our baby to your mother's place and have a splurge day - Buy sexy lingerie, do your hair and order in food. Plan a perfect evening.

• Work your magic with your fingers - Who says that being a mom takes you off the genie pedestal. You still are a sexy little genie and work your magic with your fingers. Give him a good rub with essential oils. Treat him to something soothing and relaxing.

And sit back and enjoy your evening whilst it takes rainbow like hues.

Source: India Syndicate