Search Through Stored Procedure

How to find if particular table is being used in the stored procedure?
How to search in stored procedures?


SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Docs%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%FileLoc%'
go

Using sp_send_dbmail from a SQL-Agent job gives error EXECUTE permission denied.

Executed as user: domain\user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

To solve this issue, execute the below line
ALTER DATABASE [SampleDB] SET TRUSTWORTHY ON

Ref: http://technet.microsoft.com/en-us/library/ms187861.aspx
http://blog.arjanfraaij.com/2011/06/using-spsenddbmail-from-sql-agent-job.html

Active Directory With C#

very good link to AD programming using C#

http://www.ianatkinson.net/computing/adcsharp.htm

Cannot use the special principal 'sa'.

ERROR: Cannot use the special principal 'sa'.

SOLUTION: run this command in the query editor window for that database

exec sp_changedbowner 'sa','true'

find out which column has the userId = 11 in a database

exec sp_msforeachtable 'Select ''[?]'' as Table_Name, * from ? where userId in (''11'')'

Convert column data from Binary to String with SQL Server

declare @stringPassowrd varchar(max)

select @stringPassowrd = sys.fn_sqlvarbasetostr(UserPassword) from Users where UserID = 2

print @stringPassowrd

where UserPassword is the binary column you would like to convert.

Export DataTable to Excel

private void btnExportToExcel_Click(object sender, EventArgs e)

{

//Export to Excel code

try

{

string attachment = "attachment; filename=Contact.xls";

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition", attachment);

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string sTab = "";

foreach (DataColumn dc in resultTable.Columns)

{

HttpContext.Current.Response.Write(sTab + dc.ColumnName);

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

int i;

foreach (DataRow dr in resultTable.Rows)

{

sTab = "";

for (i = 0; i < resultTable.Columns.Count; i++)

{

HttpContext.Current.Response.Write(sTab + dr[i].ToString());

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

}

HttpContext.Current.Response.End();

}

catch (Exception ex)

{

ErrorMessage = ex.Message;

ErrorLabel = new Label();

ErrorLabel.Text = ErrorMessage;

this.Controls.Add(ErrorLabel);

}

}

SSRS 2008 R2 - Unable to Connect to Remote Server Error

I had installed SQL Server 2008 R2 on windows 7, but could not able to connect to the report server at
http://pc-0260:13000/Reports

Try doing the following things
Go to Reporting Services Conf. Manager.

1. Change the server account to local account.
2. Remove the SSL if configured.
3. Go to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
4. Change the  SecureConnectionLevel value to 0 in rsreportserver.config

Try refreshing the page, it should work now!

Spliting String in SQL Server Stored Procedure

declare @par varchar(255)
set@par = 'string1, string2,string3'

declare @val as varchar(255)

create table #temp (col1 varchar(255))

while (charindex(',',@par)>0)

begin

insert into #temp

select Cast(replace(substring(@par,1,charindex(',',@par)),',','') as varchar)

set @par = substring(@par,charindex(',',@par)+1,len(@par))

end

insert into #temp Select @par

Select * from #temp

drop table #temp

SQL Job Monitoring Queries

Failed Job

SELECT name,last_outcome_message
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

Disabled Job

SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

Running Job

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

Last Backup Date

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

SQL Logs

EXEC xp_readerrorlog

Custom attributes in AD does not show in Property Mapping for Synchronization

If you have a problem with the Profile Synchronization in SharePoint 2010.

I had made a connection to our Active Directory and the Synchronization of profiles is working like a charm but when I try to map User Properties to attributes in our AD I can't see the custom attributes we made in the AD. The attributes are added to the AD after the initial connection and synchronization was made.

Solution : recreate Active Directory Connection.

How To Enter Binary String Literals?

-- Size matches
DECLARE @x BINARY(8);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x2605260626402642

-- Truncated
DECLARE @x BINARY(4);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x26052606

-- Padded
DECLARE @x BINARY(12);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x260526062640264200000000

-- No padding on variable length data type
DECLARE @x VARBINARY(8);
SET @x = 0x; -- Empty binary string
PRINT @x;
GO
0x

-- Padding on fixed length data type
DECLARE @x BINARY(8);
SET @x = 0x; -- Empty binary strings
PRINT @x;
GO
0x0000000000000000

Find Tables With Foreign Key Constraint in Database

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

OR


select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

OR


select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id

Converting Varchar to Date format

declare @dt date
if(isdate('Thursday, April 01, 2010') = 0)
begin
set @dt = convert(date, substring('Thursday, April 01, 2010',CHARINDEX(',', 'Thursday, April 01, 2010') + 1,len('Thursday, April 01, 2010')), 103)
end
print @dt

How to Set the Day/Month/Year Date Format in SQL Server

Set the Language on the SQL Server

To set the language on the server you must add a language by using sp_addlanguage. The example below sets the language for British English and gives the dates in DD/MM/YY format. The example can also be applied to other countries, but you may need to modify the parameters for sp_addlanguage.

exec sp_addlanguage 'British', 'English',
'January,February,March,April,May,June,July,August,September,October,
November,December',
'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',
dmy,1
sp_configure 'default language', 1
reconfigure with override

To set the default language back to U.S. English after having installed another language, use the following SQL statements:

sp_configure 'default language', 0
reconfigure with override


To check what default language a server has installed, use the following SQL command:

sp_configure 'default language'

If the resulting value is 0, the default language U.S. English. If the result is not 0, run the following SQL command to find the installed default language setting and date format used:


select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')

Disabling All Constraints on a Table

When there are several constraints on a table you can disable them all with one statement. To do so, use the ALTER TABLE command, providing the name of the table and the clause, NOCHECK CONSTRAINT ALL. For example, the following disables all of the constraints for the CustomerAddresses table:

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT ALL

To re-enable the constraints, issue use the same command, substituting NOCHECK with CHECK, as follows:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT ALL

Disabling Individual Constraints

Sometimes you will wish to disable one constraint only. You can use the same ALTER TABLE command but instead of specifying ALL, provide the name of the constraint that you wish to remove. The following disables the FK_CustomerAddresses_Customers constraint on the CustomerAddresses table.

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT FK_CustomerAddresses_Customers

To re-enable the constraint change NOCHECK to CHECK:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT FK_CustomerAddresses_Customers