Please run these two commands in visual studio command prompt one by one.
Wsdl /language:CS /username:sa /password:XXXXX http://192.168.1.X/serwebservice/Service.asmx /n:XXXSmsService /o:XXXSmsService.CS
After generating result run this query.
csc /t:library /out:XXXSmsService.dll XXXSmsService.CS
This will generate a class file and a dll on below path.
Go To “C:\Program Files\Microsoft Visual Studio 9.0\VC” there you will find XXXSmsService.CS and XXXSmsService.dll.
Enjoy :)
Hi
Many times you come across a situation where you have to convert values stored in multiple rows in a comma separated string e.g. Part consumed in a job.
Using Coalesce you can achieve the same but the drawback is that it would hit the performance significantly if you need to call this function for each row in a recordset. Actually using any function when it needs to be applied to each row will hit query performance unless the rows returned are not too many !
As an alternate to Coalesce, you can use For XML construct which will give better performance if your recordset is large.
Sample :
SELECT t1.ID ,
SUBSTRING(( SELECT ( ‘ ,’ + t2.Part )
FROM dbo.PartConsumed t2
WHERE t1.ID = t2.ID
FOR
XML PATH(”)
), 3, 500) AS PartUsed
FROM dbo.PartConsumed t1
GROUP BY t1.ID
–Pass two dates and it how many sundays occures
CREATE FUNCTION [dbo].[fnc_SundayCount](@StartDate datetime,@EndDate datetime)
RETURNS SMALLINT
as
Begin
SET @StartDate=CONVERT(datetime,CONVERT(VARCHAR, @StartDate,101))
SET @EndDate=CONVERT(datetime,CONVERT(VARCHAR, @EndDate,101))
DECLARE @DayDiff SMALLINT
DECLARE @tempVar SMALLINT
DECLARE @TotalSunday SMALLINT
SET @TotalSunday=0
SET @DayDiff=DATEDIFF(DAY,@StartDate,@EndDate)
SET @tempVar=0
WHILE (@tempVar < @DayDiff)
BEGIN
IF(DATEPART(weekday,@StartDate)=1)
BEGIN
SET @TotalSunday =@TotalSunday+1
END
SET @StartDate=@StartDate+1
SET @tempvar = @tempvar+1
END
Return @TotalSunday
END
Implicit Conversion can hit performance of your queries
If there is data type mismatch in the type of table field and the variable you use in where clause, the query will result in table or index scan instead of index seek even when there is a proper index on the field used in the where clause.
This is because SQL server will do implicit conversion by using the function on the field. Eg. if your table field is varchar and you define parameter as nvarchar, if you write something like below
declare @var1 nvarchar(20)=’test’
select * from table1 where field1=@var1
it will actually work like
select * from table1 where convert(nvarchar(20),field1) = @var1.
Thus any index on field1 will be ignored and a scan operation will run.
Be aware of this and use proper data types for your variables.
Also note the implicit conversion issue is not limited to field vs. variable..it will happen as well if you compare two fields with different data types..thus if same you have field in multiple tables, came the data type as same.
declare @NewLine char(2)
set @NewLine=char(13)+char(10)
update StockSerialMaster
set StockSerialNo =Replace(StockSerialNo , @NewLine,'')
WHERE StockSerialNo like '%' +@NewLine +'%'
<script language="javascript" type="text/javascript">
function SelectAllCheckboxes(spanChk) {
var collection = document.getElementById(‘GridData’).getElementsByTagName(‘INPUT’);
for (var x = 0; x < collection.length; x++) {
if (collection[x].type.toUpperCase() == ‘CHECKBOX’)
collection[x].checked = spanChk.checked;
}
}
</script>
<input id="chkAll" runat="server" onclick="javascript:SelectAllCheckboxes(this);"
type="checkbox" />
<b>Select All </b>
<div class="innerrightarea" id="GridData">
<input type="checkbox">
<input type="checkbox">
<input type="checkbox">
<input type="checkbox">
<input type="checkbox">
</div>
--EXEC SearchAndReplace 'hin','Pankaj' Create PROC SearchAndReplace ( @SearchStr nvarchar(100), @ReplaceStr nvarchar(100) ) AS BEGIN /* — Copyright © 2010 Pankaj Mittal. All rights reserved. — Purpose: To search all columns of all tables for a given search string and replace it with another string — Site: http://pankajmittal.wordpress.com/ — Tested on: SQL Server 7.0 and SQL Server 2000/2005 — Date modified: 29 July 2010 */ SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int SET @TableName = '' SET @SearchStr2 = '%' + @SearchStr + '%' SET @RCTR = 0 WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ) SELECT @TableName,@ColumnName WHILE ( @TableName IS NOT NULL ) AND ( @ColumnName IS NOT NULL ) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ( 'char ', 'varchar ', 'nchar ', 'nvarchar ' ) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE( ' + @ColumnName + ', ''' --+ QUOTENAME(@SearchStr, '') + ''', ''' --+ QUOTENAME(@ReplaceStr, '') + @SearchStr + ''', ''' + @ReplaceStr + ''') WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 +'''' PRINT @SQL EXEC ( @SQL ) SET @RCTR = @RCTR + @@ROWCOUNT END END END SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s) ' AS 'Outcome ' END GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
--eXEC SearchAllTables 'hindware' --GO create PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN /* — Copyright © 2010 Pankaj Mittal. All rights reserved. — Purpose: To search all columns of all tables for a given search string — Written by: Pankaj Mittal — Site: http://pankajmittal.wordpress.com/ — Tested on: SQL Server 7.0 and SQL Server 2000/2005 — Date modified: 29th July 2010 */ CREATE TABLE #Results ( ColumnName nvarchar(370), ColumnValue nvarchar(3630) ) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.'
+ QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ) WHILE ( @TableName IS NOT NULL ) AND ( @ColumnName IS NOT NULL ) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar' ) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results DROP TABLE #Results END
MERGE INTO <Table name in which you want to insert/Update/Delete> USING <Table name from where data will be pick for process> ON <Join condition> WHEN MATCHED THEN UPDATE SET <update columns>, WHEN NOT MATCHED THEN INSERT ( <column name> ) VALUES (<column name> ) ;
If you want to delete then just enter delete instead of Then UPDATE OR THEN INSERT.
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
MERGE INTO JobSheetBatteryQCTestReading RT
USING
( SELECT JobSheetBatteryQCTestReading.*
FROM JobSheetBatteryQCTestReading
INNER JOIN @BatteryQCTestReading_TVP AS a
ON JobSheetBatteryQCTestReading.BatteryQCTestMasterID = a.BatteryQcTestMasterID ) temp ON RT.JobSheetBatteryQCTestID = @PKId WHEN MATCHED THEN UPDATE SET Cell1Status = temp.Cell1Status , Cell1Status = temp.Cell1Status , Cell2Status = temp.Cell2Status , Cell3Status = temp.Cell3Status , Cell4Status = temp.Cell4Status , Cell5Status = temp.Cell5Status , Cell6Status = temp.Cell6Status , MakeInformation = temp.MakeInformation , Remarks = temp.Remarks WHEN NOT MATCHED THEN INSERT ( JobSheetBatteryQCTestID , BatteryQCTestMasterID , Cell1Status , Cell2Status , Cell3Status , Cell4Status , Cell5Status , Cell6Status , MakeInformation , Remarks ) VALUES ( @PKId , -- JobSheetBatteryQCTestID - bigint temp.BatteryQCTestMasterID , -- BatteryQCTestMasterID - smallint temp.Cell1Status , -- Cell1Status - tinyint temp.Cell2Status , -- Cell2Status - tinyint temp.Cell3Status , -- Cell3Status - tinyint temp.Cell4Status , -- Cell4Status - tinyint temp.Cell5Status , -- Cell5Status - tinyint temp.Cell6Status , -- Cell6Status - tinyint temp.MakeInformation , -- MakeInformation - nvarchar(500) temp.Remarks -- Remarks - nvarchar(500) ) ; MERGE INTO JobSheetBatteryQCTest LT USING JobSheetBatteryQCTest MT ON Mt.JobsheetID = @JobsheetID WHEN MATCHED THEN UPDATE SET LT.TestedByID = @TestedByID , LT.AssemblySerialNo = @AssemblySerialNo , LT.QCLocation = @QCLocation WHEN NOT MATCHED THEN INSERT ( JobsheetID , TestedByID , AssemblySerialNo , QCLocation , QcDate ) VALUES ( @JobsheetID , @TestedByID , @AssemblySerialNo , @QCLocation , GETDATE() ) ;
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
/* with Delete statement */ CREATE TABLE dbo.table1 ( id INT, employee VARCHAR(32) ) go INSERT INTO dbo.table1 VALUES (1, 'Fred') ,(2, 'Tom') ,(3, 'Sally') ,(4, 'Alice'); GO DECLARE @MyTableVar TABLE ( id INT, employee VARCHAR(32) ); PRINT 'table1, before delete' SELECT * FROM dbo.table1; DELETE FROM dbo.table1 OUTPUT DELETED.* INTO @MyTableVar WHERE id = 4 OR id = 2; PRINT 'table1, after delete' SELECT * FROM dbo.table1; PRINT '@MyTableVar, after delete' SELECT * FROM @MyTableVar; DROP TABLE dbo.table1; --Results --table1, before delete --id employee ------------- ------------------------------ --1 Fred --2 Tom --3 Sally --4 Alice -- --table1, after delete --id employee ------------- ------------------------------ --1 Fred --3 Sally --@MyTableVar, after delete --id employee ------------- ------------------------------ --2 Tom --4 Alice
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
/*with update statement */ USE AdventureWorks2008R2; GO DECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime); UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25, ModifiedDate = GETDATE() OUTPUT inserted.BusinessEntityID, deleted.VacationHours, inserted.VacationHours, inserted.ModifiedDate INTO @MyTableVar; --Display the result set of the table variable. SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate FROM @MyTableVar; GO --Display the result set of the table. SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate FROM HumanResources.Employee; GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
CREATE TABLE dbo.table1
(
id INT,
employee VARCHAR(32)
)
go
DECLARE @MyTableVar TABLE
(
id INT,
employee VARCHAR(32)
);
INSERT INTO dbo.table1
OUTPUT inserted.id,inserted.employee INTO @MyTableVar
VALUES
(1, 'Fred')
,(2, 'Tom')
,(3, 'Sally')
,(4, 'Alice')
SELECT * FROM @MyTableVar
;
GO
DROP TABLE dbo.table1;
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Theme: Rubric. Blog at WordPress.com.
