Integration

How do I rename a database?

You can rename a database by using the code:

EXEC sp_renamedb 'OldName', 'NewName'

How do I rename a physical database filename?

To rename a physical database filename, use the code:

ALTER DATABASE <Database name>  MODIFY FILE  ( NAME = <current_logical_name>, NEWNAME = <new_logical_name>)

How do I change the database collation?

To change the database collation, use the following code:

use master
go
ALTER DATABASE <Database> collate <collation>
go

How do I find the XML (XSD) schema for a table or view in SQL?

If you need to find the XML (XSD) schema for a table/view in SQL the following can help:

select top 0 * FROM [table name] FOR XML AUTO, XMLSCHEMA
replace [table name] with appropriate table name

How do I calculate business working days using SQL?

To calculate the business working days ins SQL, you can use the below:

SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

How do I remove public holidays in a date range – design

Create a table with public days details and use them in the query to exclude those days from a date range.

For Example:

select count(*) from xx_publicHolidays where (date, between @startdate and @ endDate)

Date in the where condition is the column name in the table.

How do I reassociate an orphaned SQL user with SQL login?

If you ever have the need to move an SQL Server Database from one server or instance to the next then you may come across orphaned SQL Users, for example, the User is present in the database, but it is no longer associated with the SQL Login on the Server.

When that happens you have a couple of options.

You can delete the database user, re-add them, re-grant the appropriate role or rights, and be on your way.

It gets a bit more complicated, though, when the user owns schema(s) in the database.

If you try to delete and re-add them you’ll receive an error similar to the following:

"Drop failed for User 'sqlusername'.
An exception occurred while executing a Transact-SQL statement or batch.
The database principal owns a schema in the database, and cannot be dropped."

In order to get around that error, you would have to temporarily re-assign the owner of the schema(s) to another user in the DB before being able to delete their User Login.

In addition to the schemas the user may own, they may have been added to roles within the database, they may even have been granted explicit permissions that you would then have to manually reproduce (If you know what they were).

If that’s ‘the case, recreating those rights can be a meticulous task.

A much better alternative is to execute a statement very similar to the one shown below:

EXEC sp_change_users_login 'UPDATE_ONE' , 'sqlusername', 'sqlloginname'

It will re-associate the SQL User with the SQL Login and the Schema(s), Roles, and Permissions are retained.

How do I generate a unique ID for a form to store the form’s data in the database?

1. Create a procedure in the database with the input parameter and NEWID() function in the procedure’s body.

The parameter is not going to be used in the procedure but is necessary in order to map the procedure output to a form’s control using an Integration wizard which will require the input a value in the Input Parameter tab.

1. Map the control on the form to the procedure’s output as follows:

CREATE PROCEDURE <ProcedureName>
@type varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT NEWID() as <IDName>
END

How do I debug a Connector manually?

If you run into an issue that a connector is not configurable when selected in Data Integration Wizard, use following code in any OnLoad event and look at the error message:

{
XMConnect.Connector plug = (XMConnect.Connector)Activator.CreateInstance(Type.GetType("TYPE CONNECTOR ASSEMBLY NAME HERE"));
Dictionary<string, string> constructorParams = new Dictionary<string, string>();//add all required settings
constructorParams.Add("SETTINGNAME", "SETTINGVALUE");
plug.SetHiddenParams(constructorParams);
List<InitializingParameter> initializingParameters = plug.GetInitializingArgumentsList();
throw new Exception("***" + initializingParameters.Count + " parameters found***" );//success
}
catch(Exception ex)
{
throw new Exception(ex.Message + "\n" + ex.StackTrace);
}

How Do I Track Changes with Change Data Capture in SQL?

If you need to monitor changes to tables in SQL Server you can make use of a feature called Change Data Capture (CDC) which is currently available in SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

Following are some excerpts from the CDC MSDN page:

What does CDC do?

“Change data capture records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format.”

How do you consume this information?

“Table-valued functions are provided to allow systematic access to the change data by consumers.”

“The function that is used to query for all changes is named by prepending fn_cdc_get_all_changes_ to the capture instance name.”

How do I get started?

1. “…change data capture must be explicitly enabled for the database. This is done by using the stored procedure sys.sp_cdc_enable_db.”

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_enable_db;
GO

1. “When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.sp_cdc_enable_table. When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table.

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'HR'
, @source_name = N'Employee'
, @role_name = N'cdc_Admin';
GO

1. Now you can access information about changes to your table by using the function fn_cdc_get_all_changes_HR_Employee

USE AdventureWorks2012;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Employee')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Employee(@from_lsn, @to_lsn, N'all');
GO

“The first five columns of a change data capture change table are metadata columns.

These provide additional information that is relevant to the recorded change.

The remaining columns mirror the identified captured columns from the source table in name and, typically, in type.

These columns hold the captured column data that is gathered from the source table.

Each insert or delete operation that is applied to a source table appears as a single row within the change table.

The data columns of the row that results from an insert operation contain the column values after the insert.

The data columns of the row that results from a delete operation contain the column values before the delete.

An update operation requires one row entry to identify the column values before the update, and a second row entry to identify the column values after the update.

Each row in a change table also contains additional metadata to allow interpretation of the change activity.

The column __$start_lsn identifies the commit log sequence number (LSN) that was assigned to the change. The commit LSN both identifies changes that were committed within the same transaction, and orders those transactions.

The column __$seqval can be used to order more changes that occur in the same transaction. The column __$operation records the operation that is associated with the change: 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image).

The column __$update_mask is a variable bit mask with one defined bit for each captured column.

For insert and delete entries, the update mask will always have all bits set. Update rows, however, will only have those bits set that correspond to changed columns.”

To prevent seeing the same changes every time, store the ‘from’ and ‘to’ LSN number in another table and use the ‘to’ LSN number as the start of the range and the max LSN number as the end of the range in subsequent calls.

See the MSDN page for further details regarding changes to the scheme of your tables as well as how long the changes are stored before being cleaned up.

SQL Connector – invalid object name ‘#temp’ when reading input/output properties

When attempting to use the SQL Connector in the WorkFlow Designer may see the following (or similar) error message: invalid object name #temp.

This error will occur in a very specific scenario:

You are attempting to map the SQL Connector to a stored procedure and the stored procedure in question makes use of a temp table (or tables)

This error is caused by a limitation in SQL server, but can thankfully be easily avoided, in one of two ways:

Table Variable

The ideal solution when you encounter this issue is to make use of table variables (@temp) in your SQL Stored Procedure, and not temp tables (#temp).

Disable FMTONLY

Temp tables perform significantly faster than table variables, especially when processing large volumes of data. If the use of a table variable is not appropriate (possibly due to performance reasons), add the following statement to the start of your SQL Stored Procedure, before the temp table is addressed or referenced:

if 1=0 SET FMTONLY OFF

If you do use this approach to address your issue, please ensure that your stored procedure will execute successfully when all input parameters are passed a null value. You can verify this directly in SQL Server Management Studio.