Programming Discussions

Python - list vs pandas.series

noreply@blogger.com (Unknown) — Tue, 18 Feb 2025 15:35:00 +0000

pandas.Series and Python's list both allow you to store a collection of data, but they have significant differences in terms of functionality, performance, and ease of use. Here's a comparison of the two:

1. Indexing and Labels

pandas.Series: Each element in a Series has an associated index (which can be labeled), making it easier to access data using meaningful labels rather than just integer-based indices.
- Example:
```
import pandas as pd
series = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
print(series['a'])  # Output: 10
```
list: A list is indexed by integers, starting from 0. There are no custom labels, only numerical indices.
- Example:
```
my_list = [10, 20, 30]
print(my_list[0])  # Output: 10
```

2. Data Types

pandas.Series: A Series can hold data of any type (integers, floats, strings, etc.), but it’s optimized for heterogeneous types and can handle missing data (e.g., NaN).
- Series can be numeric, boolean, string, and more.
list: A list in Python can also hold any type of data, but lists are not optimized for handling missing values or complex data operations like Series.
- Lists do not naturally handle NaN or missing values.

3. Vectorized Operations

pandas.Series: Supports vectorized operations (i.e., operations applied to every element without the need for explicit loops), which allows you to perform arithmetic operations and transformations on the entire series at once.
- Example:
```
series = pd.Series([1, 2, 3])
print(series * 2)  # Output: [2, 4, 6]
```
list: Does not support vectorized operations. You would have to use a loop or list comprehension to perform element-wise operations.
- Example:
```
my_list = [1, 2, 3]
result = [x * 2 for x in my_list]  # Output: [2, 4, 6]
```

4. Performance

pandas.Series: Optimized for large-scale data manipulation and performance. Series are implemented using NumPy arrays under the hood, allowing for efficient operations.
list: Slower when working with large datasets, especially for operations that require iteration or element-wise manipulation.

5. Missing Data Handling

pandas.Series: Supports missing data using NaN (Not a Number), and provides methods to handle missing data (e.g., isnull(), fillna()).
- Example:
```
series = pd.Series([1, None, 3])
print(series.isnull())  # Output: [False, True, False]
```
list: Does not have built-in support for missing values. You would have to use None or other custom indicators and manually handle missing data.

6. Aggregations and Functions

pandas.Series: Provides built-in methods for aggregation and statistical functions like sum(), mean(), std(), min(), max(), etc.
- Example:
```
series = pd.Series([1, 2, 3])
print(series.mean())  # Output: 2.0
```
list: Does not have direct support for aggregation functions. You would have to use external libraries (like sum(), min(), max()) or implement your own functions.
- Example:
```
my_list = [1, 2, 3]
print(sum(my_list))  # Output: 6
```

7. Alignment and Handling of Different Lengths

pandas.Series: Supports automatic alignment when performing operations on two Series, even if they have different indices. Missing values are filled with NaN.
- Example:
```
s1 = pd.Series([1, 2, 3], index=['a', 'b', 'c'])
s2 = pd.Series([4, 5], index=['b', 'c'])
print(s1 + s2)  # Output: NaN for 'a' and sum for 'b' and 'c'
```
list: Does not have automatic alignment; you would need to manually ensure that lists are of equal length when performing element-wise operations.

8. Integration with DataFrames

pandas.Series: Commonly used as a column in a pandas.DataFrame. A DataFrame is essentially a collection of Series.
- Example:
```
df = pd.DataFrame({'col1': [1, 2, 3]})
print(df['col1'])  # Output: Series with index 0, 1, 2
```
list: Lists are more generic and do not integrate with DataFrames, though they can be converted to a DataFrame if needed.

9. Operations with Numpy

pandas.Series: Because it is built on top of NumPy, Series can directly interact with NumPy functions and arrays. You can use NumPy operations on Series for efficient computations.
list: Lists are not directly compatible with NumPy functions. You would need to first convert a list into a NumPy array before performing NumPy operations.

Summary Table

Feature	`pandas.Series`	`list`
Indexing	Labeled indices (customizable)	Integer-based indices
Data Types	Handles mixed data types, supports `NaN`	No built-in handling for missing data
Vectorized Operations	Yes (fast element-wise operations)	No (requires loops or list comprehension)
Performance	Optimized for large data, fast	Slower for large datasets
Missing Data	Built-in support for `NaN`	No built-in support (use `None`)
Aggregation Functions	Built-in (sum, mean, etc.)	Needs external functions or manual implementation
Data Alignment	Automatic alignment (different indices)	No automatic alignment
Integration with DataFrame	Core component of `DataFrame`	Can be converted to DataFrame

Conclusion:

pandas.Series is ideal for structured, labeled data and is a more powerful and efficient tool for data analysis, manipulation, and aggregation.
list is a general-purpose Python container, useful for simple collections of data, but lacks the advanced capabilities of pandas.Series.

PyTest Introduction

noreply@blogger.com (Unknown) — Tue, 11 Feb 2025 12:49:00 +0000

Pytest is a popular Python testing framework known for its simplicity and powerful features. It provides functionalities to:

Write and execute unit tests, integration tests, or functional tests.
Support for test fixtures to manage setup and teardown.
Parametrize tests to run the same test with multiple inputs.
Filter, skip, or mark tests for better control.
Generate test reports with plugins (like pytest-html).
Compatible with other Python libraries and tools.

How Does Pytest Know Which Functions to Test?

Pytest automatically discovers test functions based on naming conventions. When you run pytest, it looks for files, classes, and functions that match specific patterns.

Rules for Test Discovery in Pytest

File Names:
Pytest looks for files with names starting with test_ or ending with _test.
Example:
- test_example.py ✅
- example_test.py ✅
- example.py ❌ (Not discovered unless explicitly specified)

Function Names:
Pytest looks for functions inside those files with names starting with test_.
Example:

def test_addition():  # ✅ Will be discovered
    assert 1 + 1 == 2

def addition_test():  # ❌ Won't be discovered
    assert 1 + 1 == 2

Class Names:
If you want to organize tests into classes, pytest looks for classes whose names start with Test. These classes should not have an __init__ method.
Example:

class TestMath:  # ✅ Class will be discovered
    def test_addition(self):  # ✅ Function will be discovered
        assert 1 + 1 == 2

class MathTests:  # ❌ Class won't be discovered
    def test_subtraction(self):  # ❌ Function won't be discovered
        assert 2 - 1 == 1

Directories:
Pytest scans directories recursively for test files. By default, it looks in:
- The current working directory.
- Any subdirectories that don’t start with . or _.
Example directory structure:
```
project/
├── tests/
│   ├── test_math.py
│   ├── test_string.py
└── app/
    └── main.py
```
Running pytest from the project/ directory will automatically find tests/test_math.py and tests/test_string.py.

Examples

Discovered Test

File: test_sample.py

def test_sum():
    assert 2 + 2 == 4

Command:

$ pytest

Output:

========================================= test session starts =========================================
collected 1 item

test_sample.py .                                                                          [100%]
========================================== 1 passed in 0.01s ==========================================

Undiscovered Function

File: test_sample.py

def sum_test():  # Does not follow pytest naming convention
    assert 2 + 2 == 4

Command:

$ pytest

Output:

========================================= test session starts =========================================
collected 0 items

How to Override the Default Discovery Rules?

Run Specific Files/Functions Manually:
You can explicitly specify the file or function to test:
```
$ pytest test_example.py::test_specific_function
```

Change Naming Patterns:
Use the --pyargs or --collect-only options, or configure pytest.ini to adjust discovery:


# pytest.ini
[pytest]
python_files = *_tests.py  # Match files ending with '_tests.py'
python_classes = *TestCase  # Match classes ending with 'TestCase'
python_functions = test_*  # Match functions starting with 'test_'

Run All Functions:
Use the pytest --collect-only command to list all functions that match the discovery rules:
```
$ pytest --collect-only
```

Python - Lambda, generator, closures and decorators vs functions

noreply@blogger.com (Unknown) — Mon, 10 Feb 2025 14:12:00 +0000

While lambdas, generators, closures, and decorators are technically functions, they differ from regular user-defined functions in terms of purpose, scope, and functionality. Here's how they compare to user-defined functions:

1. Lambda Functions vs. User-Defined Functions

Key Differences:

Lambda:
- A lambda is a one-liner, anonymous function.
- Used for short, simple operations.
- Cannot contain statements (like if, for, etc.).
- Typically used as a temporary or inline function.
User-Defined Function:
- A named function defined using the def keyword.
- Can include multiple lines of code and more complex logic.
- Supports reusability across the program.

Example:

# Lambda
add_lambda = lambda x, y: x + y
print(add_lambda(3, 5))  # Output: 8

# User-defined function
def add_function(x, y):
    return x + y

print(add_function(3, 5))  # Output: 8

When to Use?

Use lambda for quick, simple tasks (e.g., sorting, filtering).
Use user-defined functions for more complex logic or when readability is important.

2. Generators vs. User-Defined Functions

Key Differences:

Generator:
- Defined like a function but uses the yield keyword instead of return.
- Produces a sequence of values lazily (one at a time), saving memory.
- Keeps track of the state between iterations.
User-Defined Function:
- Returns a single value or object and exits after execution.
- Cannot maintain state between function calls.

Example:

# Generator
def count_up_to(n):
    for i in range(1, n + 1):
        yield i

# User-defined function
def count_up_to_list(n):
    return list(range(1, n + 1))

# Using the generator
for num in count_up_to(5):
    print(num, end=" ")  # Output: 1 2 3 4 5

# Using the user-defined function
print(count_up_to_list(5))  # Output: [1, 2, 3, 4, 5]

When to Use?

Use generators for iterating over large datasets efficiently.
Use user-defined functions when you need the entire result at once.

3. Closures vs. User-Defined Functions

Key Differences:

Closure:
- A nested function that retains access to the variables in its enclosing scope even after the outer function has finished executing.
- Used for creating function factories or maintaining state.
User-Defined Function:
- Does not inherently retain any state beyond its local variables.
- Requires explicit state passing (e.g., arguments).

Example:

# Closure
def make_multiplier(factor):
    def multiplier(number):
        return number * factor
    return multiplier

times_two = make_multiplier(2)
print(times_two(5))  # Output: 10

# User-defined function
def multiplier(number, factor):
    return number * factor

print(multiplier(5, 2))  # Output: 10

When to Use?

Use closures for creating functions with predefined configurations or state.
Use user-defined functions when state is passed explicitly.

4. Decorators vs. User-Defined Functions

Key Differences:

Decorator:
- A higher-order function that takes another function as input and modifies or extends its behavior.
- Applied using the @decorator syntax.
- Adds functionality without modifying the original function.
User-Defined Function:
- Typically performs a specific operation.
- Does not modify other functions unless explicitly designed to do so.

Example:

# Decorator
def logger(func):
    def wrapper(*args, **kwargs):
        print(f"Function {func.__name__} is called with {args}")
        return func(*args, **kwargs)
    return wrapper

@logger
def greet(name):
    return f"Hello, {name}!"

print(greet("Alice"))
# Output:
# Function greet is called with ('Alice',)
# Hello, Alice!

# User-defined function
def greet(name):
    return f"Hello, {name}!"

print(greet("Alice"))  # Output: Hello, Alice!

When to Use?

Use decorators to extend or modify the behavior of existing functions (e.g., logging, authentication).
Use user-defined functions for standalone operations.

Summary Table:

Feature	Purpose	When to Use?
Lambda	Simple, inline, anonymous functions	Short operations like sorting or filtering
Generator	Lazy iteration over large data	Handling large datasets or infinite sequences efficiently
Closure	Retain state in a nested function	Function factories or functions with pre-configured parameters
Decorator	Modify/extend another function's behavior	Adding functionality (e.g., logging, authentication) to existing functions
User-Defined Function	General-purpose reusable functions	Any operation requiring more complex logic or structure

These features provide specialized ways to make your code more concise, efficient, or reusable in specific scenarios.

List and Tuple - Difference

noreply@blogger.com (Unknown) — Mon, 10 Feb 2025 14:09:00 +0000

The key difference between lists and tuples in Python is their mutability:

List: Ordered and mutable, meaning you can change its elements (add, remove, or modify them).
Tuple: Ordered but immutable, meaning once it's created, its elements cannot be changed.

Example: List

# Creating a list
my_list = [1, 2, 3]

# Modifying the list
my_list[0] = 10  # Update an element
my_list.append(4)  # Add a new element
my_list.remove(2)  # Remove an element

print(my_list)  # Output: [10, 3, 4]

Example: Tuple

# Creating a tuple
my_tuple = (1, 2, 3)

# Trying to modify the tuple
# my_tuple[0] = 10  # This will raise an error: TypeError: 'tuple' object does not support item assignment

# Accessing elements (this is allowed)
print(my_tuple[0])  # Output: 1

Key Characteristics:

Mutability:
- Lists allow you to modify their elements.
- Tuples do not allow modification after creation.
Performance:
- Tuples are slightly faster than lists due to their immutability.
Usage:
- Use lists when the data needs to change (e.g., dynamically adding/removing elements).
- Use tuples for fixed collections of data (e.g., coordinates (x, y) or configuration data).

Practical Difference Example:

List Use Case:

# Shopping list: It changes over time
shopping_list = ["milk", "bread"]
shopping_list.append("eggs")
print(shopping_list)  # Output: ['milk', 'bread', 'eggs']

Tuple Use Case:

# Days of the week: Fixed and unchanging
days_of_week = ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
print(days_of_week)  # Output: ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')

AttributeError: 'DataFrame' object has no attribute 'unique'

noreply@blogger.com (Unknown) — Tue, 24 Jan 2023 16:02:00 +0000

AttributeError: 'DataFrame' object has no attribute 'unique'

The error message "'DataFrame' object has no attribute 'unique'" occurs when you are trying to use the unique() method on a pandas DataFrame object, but this method is only available for pandas Series objects.

#1. Applying unique to dataframe

df = pd.DataFrame([{"A":"aaa","B":"bbb"}])

print(df.unique()) #Throws error

#2. Error can be reproduced if dataframe has multiple columns with same name.The error can pop-up if column is renamed mistakenly.

import pandas as pd
df = pd.DataFrame([{"A":"aaa","B":"bbb"}])
print(df)

print(df['A'].dropna().unique()) #works fine
df =df.rename(columns={ df.columns[1]:df.columns[0] }) # column

print(df)

print(df['A'].dropna().unique()) # 

Hope it helps!!

working with report model

noreply@blogger.com (Unknown) — Mon, 11 Sep 2017 08:00:00 +0000

To create a shared data source

In Solution Explorer, right-click Shared Data Sources, and then select Add New Data Source.
In the Name box, type: RMS.
In the Type drop-down list, select Report Server Model.
In the Connection string area, type: Server=<>; datasource=<>.
Select Credentials.
Select Use Windows Authentication (Integrated Security) and then click OK.

The data source appears in the Shared Data Sources folder in Solution Explorer.
On the File menu, click Save All.

XML parsing: line 1, character 75, illegal name character

noreply@blogger.com (Unknown) — Mon, 11 Sep 2017 08:00:00 +0000

When saving strings to XML, or when trying to extract text within tags it important to escape invalid characters . The following table shows the invalid XML characters and their escaped equivalents.

Invalid XML Character Replaced With
< <
> >
" "
' '
& &

if we try following code in SQL window, where we are trying to extract text from within html tags.

declare @v varchar(40)
Set @v='a & b'
Select cast(@v as XML).value('.','varchar(max)')

we will receive error like "XML parsing: line 1, character xx, illegal name character"

Solution:

declare @v varchar(40)
Set @v='a & b'
Select cast(replace(replace(replace(@v,'>','><![CDATA['),'</',']]></')+']]>','<![CDATA[]]>','') as XML).value('.','varchar(max)')

As we know CDATA section is "a section of element content that is marked for the parser to interpret as only character data, not markup." so we will include CDATA in such a way that text is inside it and parsing of it wouldn't result in error.

Hope it helps

Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS

noreply@blogger.com (Unknown) — Mon, 13 Jun 2016 12:55:00 +0000

When executing the Open function. I received the following exception:

Microsoft Office Excel cannot access the file ‘e:\report\report.xls’.

There are several possible reasons:

• The file name or path does not exist

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

SOLUTION:

For Windows Server x64: Create the following directory:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows Server x86: Create the following directory:

C:\Windows\System32\config\systemprofile\Desktop

Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead

noreply@blogger.com (Unknown) — Mon, 23 May 2016 12:13:00 +0000

.NET 4.0 allows primary interop assemblies (or rather, the bits of it that you need) to be embedded into your assembly so that you don't need to deploy them alongside your application.

Reason:
In most cases, this error is the result of code which tries to instantiate a COM object.

For example
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();

Solution #1
Typically, in .NET 4 you just need to remove the 'Class' suffix and compile the code:
Excel.Application xlapp = new Excel.Application();

Solution #2
For whatever reason, this assembly can't be embedded. Just open the Properties tab for the assembly in Visual Studio 2010 and set "Embed Interop Types" to "False".

Long running SQL Agent job

noreply@blogger.com (Unknown) — Tue, 12 Jan 2016 13:04:00 +0000

We tried to launch an .exe from SQL Server Agent Job and job keeps on running, it need to be stopped manually.

Steps to troubleshoot
#1. Any program that has windows interaction i.e. opens any type of window e.g. Notepad, ms-paint or excel etc.

#2. So it simply means we can only use console programs and that too without any prompts or wait for input.
Therefore, we can't execute Date or Time (DOS internal command) as they expect input.

Execute the .exe given in job step from command prompt to validate there are no wait for input.

Hope it helps

Unable to generate a temporary class (result=1)

noreply@blogger.com (Unknown) — Fri, 24 Jul 2015 05:46:00 +0000

While trying to Invoke Web Services we faced following error:
Error:
Unable to generate a temporary class (result=1).
error CS0029: Cannot implicitly convert type 'WebServices.Proxy.CustomType' to 'WebServices.Proxy.CustomType[]'
error CS0030: Cannot implicitly convert type 'WebServices.Proxy.CustomType[]' to 'WebServices.Proxy.CustomType'

Cause

A known issue with WSDL.exe can cause a proxy class to be generated incorrectly if an array of complex type includes an element that is also an array of complex type and for which only one element exists.

Resolution:

We need to open reference.cs file and change respective customtype from double dimension to single dimension. Rebuild the project and it started working fine.

Task failed because 'sgen.exe' was not found

noreply@blogger.com (Unknown) — Wed, 29 Apr 2015 14:22:00 +0000

In one of SSIS package we were trying to build the SSIS script task and it was failing with following message:

Task failed because "sgen.exe" was not found, or the correct Microsoft Windows SDK is not installed. The task is looking for "sgen.exe" in the "bin" subdirectory beneath the location specified in the InstallationFolder value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v6.0A. You may be able to solve the problem by doing one of the following: 1) Install the Microsoft Windows SDK for Windows Server 2008 and .NET Framework 3.5. 2) Install Visual Studio 2008. 3) Manually set the above registry key to the correct location. 4) Pass the correct location into the "ToolPath" parameter of the task.

Reason:
This issue occurs because the default setting for the SSIS script task creates the serialization assembly. The serialization assembly requires files that may not be installed by SQL Server 2008 alone.

Solution: Installing Windows SDK can resolve the issue, but I found following easier to follow with apparently no side effect.

Disable the Generate serialization assembly option. To do this, follow these steps:

Click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.
Open the project file or the solution file.
In Solution Explorer, double-click the SSIS Package to open the Package Designer.
Double-click Script Task in the Package Designer to open the Script Task Editor dialog box.
Click Edit Script to open the Microsoft Visual Studio Tools for Applications (VSTA) IDE.
In Project Explorer, right-click the project, and then click Properties.
Click the Build tab. In the Output area, click Off in the Generate serialization assembly list.
On the File menu, click Exit.

Error 13005 : Error translating SQL statement: line dbAMp

noreply@blogger.com (Unknown) — Tue, 13 Jan 2015 11:41:00 +0000

We started receiving error 13005 while executing sf_replicate. The error was due to xp_cmdshell which is making call to dbAmp.exe.

We also noted that sf_replicate3 was working fine, a peek into procedure revealed that it uses "Select * into XYZ from salesforce...XYZ" hence works.

Solution:

To resolve, upgrade to the latest version of DBAmp using the instructions at http://www.forceamp.com/upgrade.htm

Performance Improvement

noreply@blogger.com (Unknown) — Wed, 03 Sep 2014 07:52:00 +0000

Update Statistics:

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number

Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.

Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

Find Range of continous values

noreply@blogger.com (Unknown) — Mon, 01 Sep 2014 07:12:00 +0000

There are many ways to find out gaps & range, but this standard gaps-and-island solution that I found on net is coolest one. Basic idea of this solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

The value could be int or date.

DECLARE @data TABLE (i INT)

INSERT INTO @data
VALUES (1)
 ,(2)
 ,(4)
 ,(5)
 ,(10)
 ,(11)
 ,(15)
 ,(16)
 ,(17)
 ,(18)
 ,(19)
 ,(20)
 ,(21);

WITH tab
AS (
 SELECT i d
  ,ROW_NUMBER() OVER (
   ORDER BY i
   ) r
 FROM @data
 )
SELECT min(d)
 ,max(d)
FROM tab
GROUP BY d - r

Output:

Hope it helps

Easy way to split string

noreply@blogger.com (Unknown) — Wed, 23 Jul 2014 10:57:00 +0000

We generally use function to split a delimited string into rows. The code for same is listed below

CREATE FUNCTION
 [dbo].[fnSplitString] (
 @string NVARCHAR(MAX)
 ,@delimiter CHAR(1)
 )
RETURNS@output  
TABLE (splitdata NVARCHAR(MAX))

BEGIN
 DECLARE @start INT
  ,@end INT

 SELECT @start = 1
  ,@end = CHARINDEX(@delimiter, @string)

 WHILE @start < LEN(@string) + 1
 BEGIN
  IF @end = 0
   SET @end = LEN(@string) + 1

  INSERT INTO @output (splitdata)
  VALUES (SUBSTRING(@string, @start, @end - @start))

  SET @start = @end + 1
  SET @end = CHARINDEX(@delimiter, @string, @start)
 END

 RETURN
END GO

We will be using XQuery method to split the string in easy and efficient way.
Following steps are taken to split the string.

#1. Convert the string into formatted XML, 
for that replace the delimitor with any tag (used in example).
#2. Remove empty nodes like '' or ''
#3. list value of all nodes 


USE tempdb

DECLARE @x VARCHAR(MAX)
DECLARE @x1 XML
DECLARE @t1 DATETIME
DECLARE @t2 DATETIME

SET @x = '100|101|102|'
SET @x = replicate(@x, 1)

SELECT @x1 = cast
(replace
(replace
('<x>' + replace
(@x, '|', '</x><x>')
 + '</x>', '<x ></x>', '')
, '<x></x>', '')
 AS XML)


SET @t1 = getdate() --  start time of process

SELECT X.x1.value('.', 'varchar(max)')
FROM @x1.nodes('//x') X(x1) -- XQuery

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2) -- Find time difference in milliseconds
SET @t1 = getdate() --  start time of process

SELECT *
FROM [dbo].[fnSplitString](@x, '|')

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2)

Benchmarks for XQuery method to split the string
 -- XQuery
 -- 0ms for 3
 -- 0ms for 30
 -- 6ms for 300
 -- 36ms for 3000
 -- 350ms for 30000
 -- 7s for 300000

Benchmarks for Procedure method to split the string
 -- 3ms for 3
 -- 10ms for 30
 -- 100ms for 300
 -- Procedure 1m for 3000
 -- Procedure 11s for 30000
 -- Procedure 2m15s for 300000

Conclusion: Looking at performance and simplicity of XQuery, I will recommend it over procedural way. Hope it helps.

Easy Way to remove HTML tags in SQL Server

noreply@blogger.com (Unknown) — Fri, 20 Jun 2014 11:31:00 +0000

We were looking for a way to remove/strip HTML tags from varchar columns. On making search found some code samples, which suggested to create a stored procedure like following.

Click to view Stored Procedure StripHtml

CREATE FUNCTION [dbo].[StripHtml] (@HTMLText VARCHAR (MAX))
RETURNS VARCHAR (MAX)
AS
BEGIN
DECLARE @Start int
DECLARE @End int
DECLARE @Length int

-- Replace the HTML entity & with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity < with the '<' character
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity > with the '>' character
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity & with the '&' character
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity with the ' ' character
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Remove anything between tags
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END

RETURN LTRIM( RTRIM(@HTMLText))

END

This stored procedure does what it says, but it is lot of code and procedural. So, thought of using XML data type and value method of SQL Server as embedded below.

NB: HTML text must be properly formatted otherwise we will get XML Parsing error.

Hope it Helps.

0x8004271A ODSOLE Extended Procedure Error in srv_convert.

noreply@blogger.com (Unknown) — Mon, 14 Apr 2014 07:30:00 +0000

We were trying to make a hosted web service call and retrieve the response. Following code segment was working fine for smaller responses.

set nocount on
declare @objWinHttp int
declare @strLine varchar(8000)
declare @hr int

exec @hr =sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objWinHttp out
print @hr
EXEC sp_OAMethod @objWinHttp, 'Open',NULL,'GET','http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT',false
EXEC sp_OAMethod @objWinHttp, 'Send',NULL
--- Problematic call
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText',@strLine OUtPUT
---
print @hr
print @strLine
exec sp_OADestroy @objWinHttp

but when response text is larger than 4000 bytes it failed with following error
-2147211494

OLE Automation Error Information
HRESULT: 0x8004271a
Source: ODSOLE Extended Procedure
Description: Error in srv_convert.
Reason:
Length of response > 4000.
Microsoft has description about the issue at http://support.microsoft.com/kb/325492

Solution:
As a workaround we created a temporary table with nvarchar column having size as max. We inserted the output of procedure to the table

--- Changed the call : removed @strLine OUtPUT
Create table #tmp(dt nvarchar(max))
insert into #tmp
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText' --,@strLine OUtPUT
Select dt from #tmp -- single column/single row.
Drop Table #tmp -- clean up
---

It worked for us. Hope it help others.

SQL Agent + Excel issue

noreply@blogger.com (Unknown) — Fri, 07 Mar 2014 06:14:00 +0000

We have a SSIS package which is suppose to execute macro. This SSIS package works fine when it is executed from the development environment but fails with following message when package is scheduled as job.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'XYZ.xls'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.

Solution/Workaround:
it is a workaround for a bug in Windows:

1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
2. Set Full control permissions for directory Desktop (for user "SYSTEM")

Please do this for both 32 and 64 bit folder.

Hope it helps

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]

noreply@blogger.com (Unknown) — Fri, 06 Sep 2013 06:54:00 +0000

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

Occasionally, during setup, some .mof (Managed Object Format) files don't get installed or registered correctly. There is a program called mofcomp that is responsible for registering and storing the data associated with .mof files. If the .mof file information becomes damaged or compromised, or never installed correctly, the problem will result in an error message like above:

Solution:

Mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

noreply@blogger.com (Unknown) — Fri, 06 Sep 2013 06:47:00 +0000

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

Checklist in case of above error

#1. SQL Server service must be running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

#2. TCP/IP enabled.

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

#3. Add Port in Windows Firewall on SQL Server instance.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions >> Add Port

#4 Enable Remote Connection

In order to connect to machine other than local we need to enable remote connection on instance.

#5 Enable SQL Server Browser service

Variable Scope in T-SQL & PL/SQL

noreply@blogger.com (Unknown) — Mon, 26 Aug 2013 08:00:00 +0000

As per general programming point of view variable declared inner most block should not be available to outer blocks. Let's see if we can conclude how it is approached in T-SQl and PL/SQL.

Scope of TSQL Variables
begin
declare @a int -- vaiable in outer begin/end
set @a=20
Select @a
    begin
        Declare @b int -- @b declared inner begin/end
        set @b=30
        Select @b
    end
Select @a,@b -- @b is accessible
end

SQL Server scoping rules are per batch. BEGIN/END have no meaning on scope in this example.

That is, the variable is *not* declared per BEGIN/END. It is declared once in the batch, so accessible to batch.

MSDN (http://msdn.microsoft.com/en-us/library/ms188927.aspx) says “The scope of a local variable is the batch in which it is declared. “

Scope of PS/SQL Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.

Declare
      pa number; /* vaiable in outer begin/end*/

Begin
      pa :=20;

      declare
    pb number; /* declared inner begin/end */
      begin

       pb:=30;
      end;    pc number;
    pc:= pa+pb; /* pa is accessible while pb is not */

end;

Hope it helps.

provider: TCP Provider, error:

noreply@blogger.com (Unknown) — Tue, 20 Aug 2013 12:49:00 +0000

Error:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

General Explanation:
This error will show if a connection is drawn from the connection pool and the connection to the server has been lost.
There is no way for a connection in the pool to know that the connection has been severed.

Reason:
There are a few common reasons for this.
1 The server has been restarted, this will close the existing connections.
2 Someone or something has killed the SPID that is being used. Connection drop or Deadlock where a victim is chosen could be one of the reason.

Probale Solutions:
#1 Connection pooling (Not recommended)
Because of reason 1, we tried turning off connection pooling at application level even that did not help.

#2 TCP Chimney Offload
This may also be caused by other, non SQL Server related, reasons.
I have for example seen issues where the TCP Chimney Offload feature on the server machine is turned ON, causing this. Short info about this feature;
When TCP Chimney Offload is enabled and the NIC implements what is called the TCP Offload Engine, then some of the TCP processing is handed over to the hardware, i.e. the NIC.
By doing this, the CPU is offloaded, and since TCP could require a lot of processing this would mean that the CPU will be allowed to perform other tasks.

We tried by disabling it on server but it did not help.

#3. Deadlock
We checked using profiler for deadlock events, but there were none in trace.

#4 Setting Static IP address on NIC
It helped us in solving the issue.

Hope it helps.

Why is the Data Loader importing dates incorrectly?

noreply@blogger.com (Unknown) — Thu, 08 Aug 2013 13:10:00 +0000

We were trying to upload to an object.Most of the dates uploaded exactly as defined in the CSV file, however others were one day out e.g 2-Jul uploaded as 1-Jul.

While using DataLoader it turns out that the Time Zone setting within data loader has a big impact.

Solution #1:

we changed it to "GMT" all the dates loaded without issue.
We also check the "Use European date format (dd/mm/yyyy)" within Data Loader.

Another option we worked out is specifying these setting in process-conf.xml file.

Solution #2 (for Automating Data Loader)
process-conf.xml
....

....

specified the dates in CSV file as dd/mm/yyyy

Hope it Helps

Myth : NOLOCK as silver bullet for performance

noreply@blogger.com (Unknown) — Thu, 11 Jul 2013 05:33:00 +0000

It is general practice to recommend NOLOCK to improve performance of query, though it result in dirty reads. But if dirty reads are fine in context of a code segment NOLOCK is recommended most of time. While looking for Error 605 explanation and remedy stumbled upon this on microsoft site.

Error 601

Severity Level 12

Message Text

Could not continue scan with NOLOCK due to data movement.

Explanation

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft SQL Server is not able to continue the scan.

Action

This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.