Feeds:
Posts
Comments

Archive for the ‘SQL Server’ Category

If your are getting following exception

“Procedure or function ‘*’ expects parameter ‘*’, which was not supplied. “

while it’s already supplied.

then check if you forgot to set “CommandType” Property to “StoredProcedure”.

e.g.

cmdUser.CommandType = CommandType.StoredProcedure;    /*This is missing. */

Read Full Post »

With OrderedData as
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY logDateTime) AS RowNum
FROM tblDetailPushableData Where isNull(parkId,”) = ‘GUC-244-AAT-4’
)
Update OrderedData set synchronizedStatus = 0 Where RowNum >= 100  and RowNum <= 1000

Read Full Post »

Declare @Capacity int

Declare @strStart NVarchar(500)
Declare @strWhere NVarchar(2500)
Declare @strSQL     NVarchar(3000)
Declare @Param NVarchar(400)

Set @strStart = N‘SELECT @Capacity = COUNT(*) from tblParkSitedetails SD
INNER JOIN GetSiteDetailsDescription AV on AV.ParkSiteDetailId=SD.ParkSiteDetailId ‘
Set @strWhere = N‘WHERE ParkSiteTypeId=”’ + @ParkSiteTypeID + ”’ and ‘ + @ParmeterValue + ”
Set @strSQL = @strStart + @strWhere
Set @Param = N’@Capacity int OutPut’

EXECUTE sp_executesql @strSQL, @Param, @Capacity = @Capacity Output;

Print @Capacity

For more details :

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Read Full Post »

With AnyTable As
(
select *,row_number() over(partition by ParkSiteDetailId, SiteTypeAdditionalAttributeId order by ParkSiteDetailId, SiteTypeAdditionalAttributeId)as RepeateCount from tblParkSiteTypeDetailsAdditionalAttribute
)Delete     from AnyTable Where RepeateCount > 1

Read Full Post »

Select T1.ParkSiteTypeId, T2.ParkSiteTypeId,  T1.ParkSiteTypeName, T2.ParkSiteTypeName,
T1.SiteTypeId, T2.SiteTypeId
from tblParkSiteType T1 inner join tblParkSiteType T2 on Upper(Ltrim(Rtrim(T1.ParkSiteTypeName))) = Upper(Ltrim(Rtrim(T2.ParkSiteTypeName)))

Read Full Post »

Create Procedure skr_SelectAllFromTable
(
@TableName varchar(128)
)
As
Begin
Declare @SelectString Varchar(max)
Set @SelectString = ‘Select * from ‘ +  @TableName
exec(@SelectString)
End

Go to :: Tools > Options > Keybord > Infront of Ctrl + 0 put “skr_SelectAllFromTable” without quote.

Open new Query Editor > Type just name of Table > Select Table Name & Press Ctrl + 0

Read Full Post »

Created a user defined function in SQL Server 2005 :
CREATE function udf_GetMasterSiteTypeName
(
@ParkSiteTypeName Varchar(128),
@ParkCampid Varchar(128) = null
)
returns varchar(128)
AS BEGIN
declare @id varchar(128)
Select @id=Upper(Ltrim(Rtrim([Name]))) from tblSitetypeMaster M
INNER JOIN tblparksitetype T ON M.SiteTypeId=T.SiteTypeId
Where T.ParkCampid = isNull(@ParkCampid, ‘%’)
and Upper(Ltrim(Rtrim(T.ParkSiteTypeName))) = Upper(Ltrim(Rtrim(@ParkSiteTypeName)))

return @id
END

Called above UDF in C#.Net :

public string GetGlobalSiteTypeName(string SiteTypeName, string CampID)
{
string retValue = “”;
try
{
SqlCommand cmdGetGlobalSiteType = new SqlCommand(“udf_GetMasterSiteTypeName”, Connection.GetConnection());
cmdGetGlobalSiteType.CommandType = CommandType.StoredProcedure;
cmdGetGlobalSiteType.Parameters.Add(“@ParkSiteTypeName”, SqlDbType.VarChar).Value = SiteTypeName;
cmdGetGlobalSiteType.Parameters.Add(“@ParkCampid”, SqlDbType.VarChar).Value = CampID;
SqlParameter prmSiteTypeName = new SqlParameter(“@GlobalSiteType”, SqlDbType.VarChar);
prmSiteTypeName.Direction = ParameterDirection.ReturnValue;
cmdGetGlobalSiteType.Parameters.Add(prmSiteTypeName);
cmdGetGlobalSiteType.ExecuteNonQuery();
if (Convert.ToString(prmSiteTypeName.Value) != “”)
retValue = Convert.ToString(prmSiteTypeName.Value);

}
catch (Exception)
{

throw;
}
return retValue;
}

Read Full Post »

Older Posts »