Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

extending RS for Connection String only

I know there have been many questions regarding extending RS to report off of
different data sources (xml, ado etc) - we are successfully running our
reports off of SQL Server stored procedures and are using the web services
(not url) to render directly to PDF from a custom asp .net UI. We would like
to leave this as is, tying the RS Datasource to a SQL Server stored procedure
and leaving it at that. Is it possible to use only the connection interfaces
(IDbConnection) in conjunction with the web services to programmatically
switch between datasources? Or do you have to implement the whole nine yards
(IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
custom dataset in order to switch the connection string?
Thanks,ok, I realize maybe that was a stupid question. Here is another one. If we
need to create a custom data extension in order to manipulate the connection
string, is it possible to create a 'custom' data extension that executes SQL
Stored Procedures using the .net SqlCommand for its 'custom data', seeing as
how we have the reports up and running using the stored procedures?
I am trying desperately to figure out the best approach to take. We need to
be able to switch to one of 60 + databases depending on the user requesting
the report. The custom code is in place, the reports done and working - what
would anyone suggest would be the best way to accomplish the last piece of
this puzzle?
"Myles" wrote:
> I know there have been many questions regarding extending RS to report off of
> different data sources (xml, ado etc) - we are successfully running our
> reports off of SQL Server stored procedures and are using the web services
> (not url) to render directly to PDF from a custom asp .net UI. We would like
> to leave this as is, tying the RS Datasource to a SQL Server stored procedure
> and leaving it at that. Is it possible to use only the connection interfaces
> (IDbConnection) in conjunction with the web services to programmatically
> switch between datasources? Or do you have to implement the whole nine yards
> (IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
> custom dataset in order to switch the connection string?
> Thanks,

Extended Stored Procedure: Loopback connection

Hi
How can I create a loopback connection? I havn't a DSN and want make a
connection to the SQL server in my Extended Stored Procedure. As I see I can
read the user and the password in my Extended Stored Procedure, but i havn't
the DSN. Can I make a (local) connection without a DSN?
I want to communicate with the SQL server in my extended stored procedure
via ODBC (SQLConnect, SQLExecDirect, SQLBindCol and so on).
A small code sample would be great.
Thanks
HansSQL Server ships a sample exactly showing this:
C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Samples\ods\unz
ip_ods.exe
Contains
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\ods\xp_
odbc
/ ****************************************
*******************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
****************************************
*******************************/
// This is an example of an extended procedure DLL built with Open Data
// Services. The functions within the DLL can be invoked by using the
extended
// stored procedures support in SQL Server. To register the functions
// and allow all users to use them run the ISQL script XP_ODBC.SQL.
//
// For further information on Open Data Services refer to the Microsoft Open
// Data Services Programmer's Reference.
//
// The extended procedures implemented in this DLL is:
//
// XP_GETTABLE_ODBC -- Used to show the creation of a new connection to
// SQL Server using ODBC that is bound to the initial client connection
#include <windows.h>
#include <tchar.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <srv.h>
// Miscellaneous defines.
#define XP_NOERROR 0
#define XP_ERROR 1
// Extended procedure error codes.
#define SRV_MAXERROR 50000
#define GETTABLE_ERROR SRV_MAXERROR + 1
#define REMOTE_FAIL 4002
void handle_odbc_err(PSTR szODBCApi,
SQLRETURN sret,
DBINT msgnum,
SQLHANDLE herror,
SQLSMALLINT htype,
SRV_PROC* srvproc);
// It is highly recommended that all Microsoft SQL Server (7.0
// and greater) extended stored procedure DLLs implement and export
// __GetXpVersion. For more information see SQL Server
// Books Online
ULONG __GetXpVersion()
{
return ODS_VERSION;
}
// XP_GETTABLE_ODBC
// Returns the result of the SQL statement
// select * from <szTable>
//
// Parameters:
// srvproc - the handle to the client connection that
// got the SRV_CONNECT.
//
// Returns:
// XP_NOERROR
// XP_ERROR
//
// Side Effects:
// Returns messages and/or a result set to client.
RETCODE xp_gettable_odbc(srvproc)
SRV_PROC *srvproc;
{
HENV henv = SQL_NULL_HENV;
HDBC hdbc = SQL_NULL_HDBC;
HSTMT hstmt = SQL_NULL_HSTMT;
SQLRETURN sret;
RETCODE rc;
char acBindToken[256];
// ODBC column attributes.
TCHAR acColumnName[MAXNAME];
SQLINTEGER cbColData;
SQLSMALLINT eSQLType;
SQLINTEGER iNumAttr;
SQLSMALLINT cbAttr; // pointer to storage for descriptor info
PBYTE* ppData = NULL;
SQLINTEGER* pIndicators = NULL;
DBINT rows = 0L; // number of rows sent
PTSTR szDSN = _T("local"); // for integrated security to work you need to
// specify a local server in the ODBC setting
// in the Control Panel in Windows
int bImpersonated;
TCHAR acUID[MAXNAME];
TCHAR acPWD[MAXNAME];
int nParams;
DBINT paramtype;
TCHAR szTable[MAXNAME * 3]; // database.owner.table
TCHAR szExec[128 + (MAXNAME * 3)];
SQLSMALLINT nCols;
SQLSMALLINT nCol;
RETCODE rcXP = XP_ERROR; // Assume failure until shown otherwise.
// Get number of parameters.
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 1) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid Parameter",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an Extended Stored Procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// If parameter is not varchar (should be a table name), send an
// error and return.
paramtype = srv_paramtype(srvproc, nParams);
if (paramtype != SRVVARCHAR) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an Extended Stored Procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// Terminate parameter string with NULL.
memcpy(szTable, srv_paramdata(srvproc, 1),
srv_paramlen(srvproc, 1));
szTable[srv_paramlen(srvproc, 1)] = '\0';
// Allocate an ODBC environment handle
sret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Env",
sret,
(DBINT) REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
return(XP_ERROR);
}
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER);
// Allocate an ODBC connection handle
sret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Dbc",
sret,
(DBINT)REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(XP_ERROR);
}
// Check for integrated security.
if (strcmp(srv_pfield(srvproc, SRV_LSECURE, (int *)NULL), "TRUE") == 0) 
3;
// Client has accessed using some form of integrated security
// Impersonate client and set SQL_INTEGRATED_SECURITY option
bImpersonated = srv_impersonate_client(srvproc);
// Connect to DSN using integrated security
SQLSetConnectAttr(hdbc, SQL_INTEGRATED_SECURITY,
(SQLPOINTER) SQL_IS_ON, SQL_IS_INTEGER);
_tcscpy(acUID, _T(""));
_tcscpy(acPWD, _T(""));
}
else {
// Client used standard login. Set the user name and password.
#ifdef UNICODE
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_USER, NULL),
-1, acUID, MAXNAME);
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_PWD, NULL),
-1, acPWD, MAXNAME);
#else
strncpy(acUID, srv_pfield(srvproc, SRV_USER, NULL),
MAXNAME);
strncpy(acPWD, srv_pfield(srvproc, SRV_PWD, NULL),
MAXNAME);
#endif
}
if (!SQL_SUCCEEDED(
sret = SQLConnect(hdbc, (SQLTCHAR*) szDSN, SQL_NTS,
(SQLTCHAR*) acUID, SQL_NTS, (SQLTCHAR*) acPWD, SQL_NTS)
)) {
handle_odbc_err("SQLConnect",
sret,
(DBINT)REMOTE_FAIL,
hdbc,
SQL_HANDLE_DBC,
srvproc);
goto SAFE_EXIT;
}
// Process data after successful connection
sret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle",
sret,
(DBINT)REMOTE_FAIL,
hdbc,
SQL_HANDLE_DBC,
srvproc);
return(XP_ERROR);
}
// Get the client session token...
rc = srv_getbindtoken(srvproc, acBindToken);
if (rc == FAIL) {
srv_sendmsg(srvproc,
SRV_MSG_ERROR,
GETTABLE_ERROR,
SRV_INFO,
(DBTINYINT) 0,
NULL,
0,
0,
"Error with srv_getbindtoken",
SRV_NULLTERM);
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// ...bind it as an ODBC parameter for the stored procedure call...
_tcscpy(szExec, _T("{call sp_bindsession(?)}"));
sret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 255, 0, acBindToken, 256, NULL);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLBindParameter",
sret,
(DBINT)REMOTE_FAIL,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
// ...and, using sp_bindsession, bind our session to the client's
// session so that we share transaction space.
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
if (!((sret == SQL_SUCCESS) ||(sret == SQL_SUCCESS_WITH_INFO))) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
// SELECT the result set.
_tcscpy(szExec, _T("SELECT * FROM "));
_tcscat(szExec, szTable);
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
// Get the number of columns in the ODBC result set.
SQLNumResultCols(hstmt, &nCols);
ppData = (PBYTE*) malloc(nCols * sizeof(PBYTE));
pIndicators = malloc(nCols * sizeof(SQLINTEGER));
if (ppData == NULL || pIndicators == NULL)
goto SAFE_EXIT;
// Build the column description for this results set.
for (nCol = 0; nCol < nCols; nCol++) {
// Get the column name, length and data type.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_NAME,
(SQLTCHAR*) acColumnName, // returned column name
MAXNAME, // max length of rgbDesc buffer
&cbAttr, // number of bytes returned in rgbDesc
&iNumAttr);
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_OCTET_LENGTH,
NULL,
0,
NULL,
&cbColData);
// Get the column's SQL Server data type, then reset the length
// of the data retrieved as required.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_CA_SS_COLUMN_SSTYPE,
NULL,
0,
NULL,
&eSQLType);
// Over-write the column length returned by ODBC with the correct value
// to be used by ODS
switch( eSQLType ) {
case SQLMONEYN:
case SQLMONEY:
cbColData = sizeof(DBMONEY);
break;
case SQLDATETIMN:
case SQLDATETIME:
cbColData = sizeof(DBDATETIME);
break;
case SQLNUMERIC:
case SQLDECIMAL:
cbColData = sizeof(DBNUMERIC);
break;
case SQLMONEY4:
cbColData = sizeof(DBMONEY4);
break;
case SQLDATETIM4: //smalldatetime
cbColData = sizeof(DBDATETIM4);
break;
}
// Allocate memory for row data.
if ((ppData[nCol] = (PBYTE) malloc(cbColData)) == NULL)
goto SAFE_EXIT;
memset(ppData[nCol], 0, cbColData);
// Bind column
SQLBindCol(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_C_BINARY, // No data conversion.
ppData[nCol],
cbColData,
&(pIndicators[nCol]));
// Prepare structure that will be sent via ODS back to
// the caller of the extended procedure
srv_describe(srvproc,
nCol + 1,
acColumnName,
SRV_NULLTERM,
eSQLType, // Dest data type.
(DBINT) cbColData, // Dest data length.
eSQLType, // Source data type.
(DBINT) cbColData, // Source data length.
(PBYTE) NULL);
}
// Initialize the row counter
rows = 0;
// Get each row of data from ODBC until there are no more rows
while((sret = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) {
if (!SQL_SUCCEEDED(sret)) {
handle_odbc_err("SQLFetch",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
goto SAFE_EXIT;
}
// For each data field in the current row, fill the structure
// that will be sent back to the caller.
for (nCol = 0; nCol < nCols; nCol++) {
cbColData = (pIndicators[nCol] == SQL_NULL_DATA ?
0 : pIndicators[nCol]);
srv_setcollen(srvproc, nCol+1, (int) cbColData);
srv_setcoldata(srvproc, nCol+1, ppData[nCol]);
}
// Send the data row back to SQL Server via ODS.
if (srv_sendrow(srvproc) == SUCCEED)
rows++;
}
if (rows > 0)
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, rows);
else
srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);
// We got here successfully, let the client know.
rcXP = XP_NOERROR;
SAFE_EXIT:
// Free the data buffers.
if (ppData != NULL)
{
for (nCol = 0; nCol < nCols; nCol++)
free(ppData[nCol]);
free(ppData);
}
if (pIndicators != NULL)
free(pIndicators);
// Free handles.
if (hstmt != SQL_NULL_HSTMT)
SQLFreeStmt(hstmt, SQL_DROP);
if (hdbc != SQL_NULL_HDBC)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeEnv(henv);
// Revert back to SQL Server's user account.
if( bImpersonated )
srv_revert_to_self(srvproc);
return (rcXP);
}
// HANDLE_ODBC_ERR
// This routine is called to send messages to clients when an ODBC
// function returns what could be considered an error (e.g., SQL_ERROR,
// SQL_INVALID_HANDLE).
//
// Parameters:
// szODBCApi - The name of the failing function.
// srODBAPI - The SQLRETURN of the failing function.
// msgnum - The ODS user message code.
// herror - The ODBC handle involved in the error.
// htype - The ODBC handle type.
// srvproc - Contains additional client information.
//
// Returns:
// none
//
void handle_odbc_err(PSTR szODBCApi,
SQLRETURN sret,
DBINT msgnum,
SQLHANDLE herror,
SQLSMALLINT htype,
SRV_PROC* srvproc)
{
SQLTCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH + 1];
SQLSMALLINT cbErrorMsg;
SQLSMALLINT nRec = 1;
// If sret is SQL_SUCCESS, return without doing anything
if (sret == SQL_SUCCESS)
return;
while (
SQLGetDiagField(htype, herror, nRec++, SQL_DIAG_MESSAGE_TEXT,
szErrorMsg, SQL_MAX_MESSAGE_LENGTH, &cbErrorMsg)
== SQL_SUCCESS)
{
// If sret is SUCCESS_WITH_INFO, send as "message" (severity
// <= 10, we use zero), else send to client as "error"
// (severity > 10, we use 11).
srv_sendmsg(srvproc,
SRV_MSG_INFO,
msgnum,
(DBTINYINT) (sret == SQL_SUCCESS_WITH_INFO ? 0 : 11),
(DBTINYINT) 1,
NULL,
0,
0,
szErrorMsg,
SRV_NULLTERM);
}
}
"Hans Stoessel" <hstoessel.list@.pm-medici.ch> wrote in message
news:%23Vtf%23S3YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi
> How can I create a loopback connection? I havn't a DSN and want make a
> connection to the SQL server in my Extended Stored Procedure. As I see I
> can
> read the user and the password in my Extended Stored Procedure, but i
> havn't
> the DSN. Can I make a (local) connection without a DSN?
> I want to communicate with the SQL server in my extended stored procedure
> via ODBC (SQLConnect, SQLExecDirect, SQLBindCol and so on).
> A small code sample would be great.
> Thanks
> Hans
>

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h>
#define XP_NOERROR 0
#define XP_ERROR 1
#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}
// typedef const char* (_MakeODBCConnection)(void);
static const char* _szMessage = "ODBC Working out...";
void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}
SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}
/*-- This is where it fails --*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)" {DSN=TestOdbc;UID=test;PWD=test;DATABASE
=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName, SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/
SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}
static void
_CheckODBCConnection(void)
{
// _MakeODBCConnection pFunction = NULL;
// _szMessage = pFunction();
}
extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}
Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )I dont know if this will make any difference, but maybe you can try adding
";trusted_connection=no"
- Neil
"Anil Saharan" <aks@.discussions.microsoft.com> wrote in message
Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h>
#define XP_NOERROR 0
#define XP_ERROR 1
#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}
// typedef const char* (_MakeODBCConnection)(void);
static const char* _szMessage = "ODBC Working out...";
void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}
SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}
/*-- This is where it fails --*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)" {DSN=TestOdbc;UID=test;PWD=test;DATABASE
=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName, SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/
SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}
static void
_CheckODBCConnection(void)
{
// _MakeODBCConnection pFunction = NULL;
// _szMessage = pFunction();
}
extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}
Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )|||Nope, It does not solve the problem

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,

I have a loopback connection using ODBC in the DLL initialization code
of
the SQL Server ESP Module (SQL Server 2000). The loopback connection
works
fine when the DSN is specifed with the "NT Authentication", however the
same
fails when specified with the "SQL Server user authentication". I have
tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them

works. Also the same code works fine on SQL Server 2005. Is this a
known
problem with some fix, or am I doing something wrong here??

The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL
application.
//

#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h
#define XP_NOERROR 0
#define XP_ERROR 1

#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}

// typedef const char* (_MakeODBCConnection)(void);

static const char* _szMessage = "ODBC Working out...";

void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;

if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}

SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}

/*------ This is where it fails ------*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE))
{
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}

/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName,
SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/

SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;

case DLL_THREAD_ATTACH:
break;

case DLL_THREAD_DETACH:
break;

case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}

static void
_CheckODBCConnection(void)
{
//_MakeODBCConnection pFunction = NULL;
//_szMessage = pFunction();
}

extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}

Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )Anil Kumar Saharan (aksaharan@.gmail.com) writes:
> I have a loopback connection using ODBC in the DLL initialization code
> of the SQL Server ESP Module (SQL Server 2000). The loopback connection
> works fine when the DSN is specifed with the "NT Authentication",
> however the same fails when specified with the "SQL Server user
> authentication". I have tried using both the SQLConnect and
> SQLDriverConnect calls, butu none of them works.

I don't really see why you would use SQL authentication for a loopback -
Windows authentication appears to be the best choice.

Then again, we have an SP that loops back, and in 6.5 days it used
SQL authentication, since we could not rely on Window authentication
being on.

Also, a DSN for a loopback seems to be an overkill. (Then again I have
always found DSN to be an overkill in all situations. Never understood
them.)

Anyway, there is something funny in your code:

> /*------ This is where it fails ------*/
> /* Tried both the with/Without database name */
> if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
> (SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,
> (SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut,
> SQL_DRIVER_COMPLETE))
> {

The call to GetWindow appears to be incomplete, and the above looks
like a syntax error to me. In any case, calling GetWindow in a
extended stored procedure, appears to be a really bad thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Soory for the mistake there in the code... I was trying out something
and just messed up before pasting it here... Kindly read the valid Part
to be the "SQLConnect" statement for the connection..

It works fine with Windows authentication but fails for the SQL Server
Authentication method.

Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
has its own known pitfalls, and so does SQL authentication, but that
does not solve the problem. Is there a reason for that code to fail for
SQL Server Authentication (with SQLConnect) ??

Anil
~~~~~~~~~~~~~
Work: http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
> Soory for the mistake there in the code... I was trying out something
> and just messed up before pasting it here... Kindly read the valid Part
> to be the "SQLConnect" statement for the connection..
> It works fine with Windows authentication but fails for the SQL Server
> Authentication method.
> Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
> has its own known pitfalls, and so does SQL authentication, but that
> does not solve the problem. Is there a reason for that code to fail for
> SQL Server Authentication (with SQLConnect) ??

I have to admit that I don't have much expierence of ODBC programming,
so I cannot answer questions about SQLConnect.

But two questions:
1) Can you get an error message from ODBC, telling you why the login
failed?
2) Stupid check: SQL authentication is enabled on the server?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But two questions:
> 1) Can you get an error message from ODBC, telling you
> why the login failed?
The error is :
Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
Driver]Timeout expired]

This is the return error for SQLConnect failure, I'm not sure whether it
is getting in some kind of deadlock or something similar due to the
loopback ..

> 2) Stupid check: SQL authentication is enabled on the
> server?
SQL Authentication is enabled, and the same DSN works fine with SQL
authentication when used from outside to connect.

Anil Kumar
~~~~~~~~~~~~
http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
>> But two questions:
>> 1) Can you get an error message from ODBC, telling you
>> why the login failed?
> The error is :
> Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
> Driver]Timeout expired]
> This is the return error for SQLConnect failure, I'm not sure whether it
> is getting in some kind of deadlock or something similar due to the
> loopback ..

Mysterious. The most likely reason for a timeout while connecting is
that the server does not exist. But what I can see in the MDAC Books
Online, you ought to get HYT01 in that case. Unless you first started
transaction, acquired locks on the sysxlogins table, and the called your
XP, I can't see how you could block yourself. (And I don't think you
can get locks on sysxlogins easily.)

I still sort of suspect that you are connecting to the wrong server.
I don't really what is that DSN (which must be a System DSN by the way),
and if I were do to this myself, I would pass @.@.servername to the
XP. In fact, as long as you are not using a named instance, there's
no need to specify server at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>SQL Authentication is enabled, and the same DSN works fine with SQL
>authentication when used from outside to connect.

maybe i dont't understand what is it all about
but i would play with client network utility on the server machine
namely to check if there is an alias defined (with the same name as it
appears in DSN) on server machine ?
(and playing with protocols)

extended connection managers for data flow destinations?

It looks like you can only extended connection managers for data flow sources.

Is there anyway to develop a custom connection manager for the SQL destination in the data flow destinations?

I can’t use hardcoded connection strings.I can’t use configurations because they are not encrypted.I already have managed code that will give the corrected connection string.I already have a custom connection manager that I use from the data flow sources.I just need one for data flow destinations but I can’t see a way to extend into the OLE DB?

The OLEDB Destination is just like the OLEDB source and can use the same connection managers. However, you are using the SQL destination and that is a special destination and needs the specific connection manager that it supports. If you want to use your custom connection manager then switch to using the OLEDB destination instead and it should just work.

HTH,

Matt

|||

oledb destinations only support the special destinations as well. there is no way I can see to use a custom connection manger in any of the destinations.

except for the script component and in that you can do whatever you want.

|||

Actually they support anything they see as an OLEDB connection. I can create different types of OLEDB connections and the OLEDB destination will support them (e.g. excel, sql (sqlncli), sql (sqloledb), etc). It does indeed have to be an OLEDB type connection though.

Matt

|||

ok so I think you might have answered my question.

If I develop a custom connection mgr derived from connectionmanagerbase I can't use that custom connection mgr with any of the stock data flow destinations.

because only OLEDB connections can be used and there is no way to create a custom OLEDB connection.

so there is no way to do what I wanted to do.

except the script componet can do what I want but I have to override the correct portions....

|||

Well there are ways to create custom OLEDB connections but you would have to write your own OLEDB provider, which not many would want to do so I guess in your case a script would probably be the best alternative.

Matt

extend time out setting

Hi,
I really need some help here. I have a few sql clients that connects to
my sql server through wireless connection. Sometimes, these wireless
connections link is down for a few seconds. When user try to continue
to run some query, the db process dead error message appears. Is the
anyway to extend the time out setting to longer time so that this
problem could be minimise?
Thanks a million.Hi
I think this would be a configuration setting on the wireless adapter rather
than the SQL Server connection. You may want to also try sending some form o
f
keep-alive although this would increase the network traffic!
John
"masterwee@.gmail.com" wrote:

> Hi,
> I really need some help here. I have a few sql clients that connects to
> my sql server through wireless connection. Sometimes, these wireless
> connections link is down for a few seconds. When user try to continue
> to run some query, the db process dead error message appears. Is the
> anyway to extend the time out setting to longer time so that this
> problem could be minimise?
> Thanks a million.
>|||The dbprocess dead error indicates a terminated connection rather than a
timeout. The application needs to be smart enough to attempt to reconnect
following the error.
Hope this helps.
Dan Guzman
SQL Server MVP
<masterwee@.gmail.com> wrote in message
news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> Hi,
> I really need some help here. I have a few sql clients that connects to
> my sql server through wireless connection. Sometimes, these wireless
> connections link is down for a few seconds. When user try to continue
> to run some query, the db process dead error message appears. Is the
> anyway to extend the time out setting to longer time so that this
> problem could be minimise?
> Thanks a million.
>|||I have similar problems on some remote sites. Some are running the client
over ISDN which naturally timesout and should pick up the line again when
there is activity. But when the user generates some activity we get the db
process dead message. We migrated these sites from Sybase which never had
these issues and seemed more robust in these situations.
Is there any config options we can use on the client or server to make the
connections less fickle?
Is there any updated client that might be more reliable?
We are using SQL Server 2000 Sp4 but the client is the one on the original
SQl Server 2000 CD.
Thanks,
"Dan Guzman" wrote:

> The dbprocess dead error indicates a terminated connection rather than a
> timeout. The application needs to be smart enough to attempt to reconnect
> following the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <masterwee@.gmail.com> wrote in message
> news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
>|||Hi Marty
Unless you have the SQL Server tools on the client PC then there is no point
in using it on the client. You should make sure that a recent version of MDA
C
is on there, although I don't think this will cure the issue as the server
can not reach the client and it can not instigate the ISDN connection.
As Dan has stated you will need to handle the error and reconnect the client
if you get this error.
Has the client application changed from powerbuilder?
John
"Marty" wrote:
[vbcol=seagreen]
> I have similar problems on some remote sites. Some are running the client
> over ISDN which naturally timesout and should pick up the line again when
> there is activity. But when the user generates some activity we get the db
> process dead message. We migrated these sites from Sybase which never had
> these issues and seemed more robust in these situations.
> Is there any config options we can use on the client or server to make the
> connections less fickle?
> Is there any updated client that might be more reliable?
> We are using SQL Server 2000 Sp4 but the client is the one on the original
> SQl Server 2000 CD.
> Thanks,
>
> "Dan Guzman" wrote:
>|||John,
When any activity happens on the client, it wakes up the ISDN connection and
should carry on where it left off in the application, but it looks like the
Client does not re-connect to the server correctly.
I'm not sure which SQL Server tools you refer to?
As for MDAC, it will be the one on the CD, should there be a later version?
The application is written in C++ not Powerbuilder. It is the database which
has been migrated to SQL Server from Sybase, and the old Sybase Open Client
did not report these errors on the same ISDN line.
Thanks,
M
"John Bell" wrote:
[vbcol=seagreen]
> Hi Marty
> Unless you have the SQL Server tools on the client PC then there is no poi
nt
> in using it on the client. You should make sure that a recent version of M
DAC
> is on there, although I don't think this will cure the issue as the server
> can not reach the client and it can not instigate the ISDN connection.
> As Dan has stated you will need to handle the error and reconnect the clie
nt
> if you get this error.
> Has the client application changed from powerbuilder?
> John
> "Marty" wrote:
>|||Hi Marty
Use the MDAC checker from
http://msdn.microsoft.com/data/ref/mdac/downloads/. MDAC can be updated
through other products such as XP SP1, so you may not be on the same version
at all sites. You will need to change the client application so that if the
error is detected the connection is closed and re-open it. I don't know if
the Open Client has the intellegence to silently reconnect or if it was
sending a keep alive.
John
"Marty" wrote:
[vbcol=seagreen]
> John,
> When any activity happens on the client, it wakes up the ISDN connection a
nd
> should carry on where it left off in the application, but it looks like th
e
> Client does not re-connect to the server correctly.
> I'm not sure which SQL Server tools you refer to?
> As for MDAC, it will be the one on the CD, should there be a later version
?
> The application is written in C++ not Powerbuilder. It is the database whi
ch
> has been migrated to SQL Server from Sybase, and the old Sybase Open Clien
t
> did not report these errors on the same ISDN line.
> Thanks,
> M
>
> "John Bell" wrote:
>

extend time out setting

Hi,
I really need some help here. I have a few sql clients that connects to
my sql server through wireless connection. Sometimes, these wireless
connections link is down for a few seconds. When user try to continue
to run some query, the db process dead error message appears. Is the
anyway to extend the time out setting to longer time so that this
problem could be minimise?
Thanks a million.Hi
I think this would be a configuration setting on the wireless adapter rather
than the SQL Server connection. You may want to also try sending some form of
keep-alive although this would increase the network traffic!
John
"masterwee@.gmail.com" wrote:
> Hi,
> I really need some help here. I have a few sql clients that connects to
> my sql server through wireless connection. Sometimes, these wireless
> connections link is down for a few seconds. When user try to continue
> to run some query, the db process dead error message appears. Is the
> anyway to extend the time out setting to longer time so that this
> problem could be minimise?
> Thanks a million.
>|||The dbprocess dead error indicates a terminated connection rather than a
timeout. The application needs to be smart enough to attempt to reconnect
following the error.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<masterwee@.gmail.com> wrote in message
news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> Hi,
> I really need some help here. I have a few sql clients that connects to
> my sql server through wireless connection. Sometimes, these wireless
> connections link is down for a few seconds. When user try to continue
> to run some query, the db process dead error message appears. Is the
> anyway to extend the time out setting to longer time so that this
> problem could be minimise?
> Thanks a million.
>|||I have similar problems on some remote sites. Some are running the client
over ISDN which naturally timesout and should pick up the line again when
there is activity. But when the user generates some activity we get the db
process dead message. We migrated these sites from Sybase which never had
these issues and seemed more robust in these situations.
Is there any config options we can use on the client or server to make the
connections less fickle?
Is there any updated client that might be more reliable?
We are using SQL Server 2000 Sp4 but the client is the one on the original
SQl Server 2000 CD.
Thanks,
"Dan Guzman" wrote:
> The dbprocess dead error indicates a terminated connection rather than a
> timeout. The application needs to be smart enough to attempt to reconnect
> following the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <masterwee@.gmail.com> wrote in message
> news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> > Hi,
> >
> > I really need some help here. I have a few sql clients that connects to
> > my sql server through wireless connection. Sometimes, these wireless
> > connections link is down for a few seconds. When user try to continue
> > to run some query, the db process dead error message appears. Is the
> > anyway to extend the time out setting to longer time so that this
> > problem could be minimise?
> >
> > Thanks a million.
> >
>|||Hi Marty
Unless you have the SQL Server tools on the client PC then there is no point
in using it on the client. You should make sure that a recent version of MDAC
is on there, although I don't think this will cure the issue as the server
can not reach the client and it can not instigate the ISDN connection.
As Dan has stated you will need to handle the error and reconnect the client
if you get this error.
Has the client application changed from powerbuilder?
John
"Marty" wrote:
> I have similar problems on some remote sites. Some are running the client
> over ISDN which naturally timesout and should pick up the line again when
> there is activity. But when the user generates some activity we get the db
> process dead message. We migrated these sites from Sybase which never had
> these issues and seemed more robust in these situations.
> Is there any config options we can use on the client or server to make the
> connections less fickle?
> Is there any updated client that might be more reliable?
> We are using SQL Server 2000 Sp4 but the client is the one on the original
> SQl Server 2000 CD.
> Thanks,
>
> "Dan Guzman" wrote:
> > The dbprocess dead error indicates a terminated connection rather than a
> > timeout. The application needs to be smart enough to attempt to reconnect
> > following the error.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <masterwee@.gmail.com> wrote in message
> > news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> > > Hi,
> > >
> > > I really need some help here. I have a few sql clients that connects to
> > > my sql server through wireless connection. Sometimes, these wireless
> > > connections link is down for a few seconds. When user try to continue
> > > to run some query, the db process dead error message appears. Is the
> > > anyway to extend the time out setting to longer time so that this
> > > problem could be minimise?
> > >
> > > Thanks a million.
> > >
> >|||John,
When any activity happens on the client, it wakes up the ISDN connection and
should carry on where it left off in the application, but it looks like the
Client does not re-connect to the server correctly.
I'm not sure which SQL Server tools you refer to?
As for MDAC, it will be the one on the CD, should there be a later version?
The application is written in C++ not Powerbuilder. It is the database which
has been migrated to SQL Server from Sybase, and the old Sybase Open Client
did not report these errors on the same ISDN line.
Thanks,
M
"John Bell" wrote:
> Hi Marty
> Unless you have the SQL Server tools on the client PC then there is no point
> in using it on the client. You should make sure that a recent version of MDAC
> is on there, although I don't think this will cure the issue as the server
> can not reach the client and it can not instigate the ISDN connection.
> As Dan has stated you will need to handle the error and reconnect the client
> if you get this error.
> Has the client application changed from powerbuilder?
> John
> "Marty" wrote:
> > I have similar problems on some remote sites. Some are running the client
> > over ISDN which naturally timesout and should pick up the line again when
> > there is activity. But when the user generates some activity we get the db
> > process dead message. We migrated these sites from Sybase which never had
> > these issues and seemed more robust in these situations.
> >
> > Is there any config options we can use on the client or server to make the
> > connections less fickle?
> > Is there any updated client that might be more reliable?
> > We are using SQL Server 2000 Sp4 but the client is the one on the original
> > SQl Server 2000 CD.
> >
> > Thanks,
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> > > The dbprocess dead error indicates a terminated connection rather than a
> > > timeout. The application needs to be smart enough to attempt to reconnect
> > > following the error.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > <masterwee@.gmail.com> wrote in message
> > > news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> > > > Hi,
> > > >
> > > > I really need some help here. I have a few sql clients that connects to
> > > > my sql server through wireless connection. Sometimes, these wireless
> > > > connections link is down for a few seconds. When user try to continue
> > > > to run some query, the db process dead error message appears. Is the
> > > > anyway to extend the time out setting to longer time so that this
> > > > problem could be minimise?
> > > >
> > > > Thanks a million.
> > > >
> > >|||Hi Marty
Use the MDAC checker from
http://msdn.microsoft.com/data/ref/mdac/downloads/. MDAC can be updated
through other products such as XP SP1, so you may not be on the same version
at all sites. You will need to change the client application so that if the
error is detected the connection is closed and re-open it. I don't know if
the Open Client has the intellegence to silently reconnect or if it was
sending a keep alive.
John
"Marty" wrote:
> John,
> When any activity happens on the client, it wakes up the ISDN connection and
> should carry on where it left off in the application, but it looks like the
> Client does not re-connect to the server correctly.
> I'm not sure which SQL Server tools you refer to?
> As for MDAC, it will be the one on the CD, should there be a later version?
> The application is written in C++ not Powerbuilder. It is the database which
> has been migrated to SQL Server from Sybase, and the old Sybase Open Client
> did not report these errors on the same ISDN line.
> Thanks,
> M
>
> "John Bell" wrote:
> > Hi Marty
> >
> > Unless you have the SQL Server tools on the client PC then there is no point
> > in using it on the client. You should make sure that a recent version of MDAC
> > is on there, although I don't think this will cure the issue as the server
> > can not reach the client and it can not instigate the ISDN connection.
> >
> > As Dan has stated you will need to handle the error and reconnect the client
> > if you get this error.
> >
> > Has the client application changed from powerbuilder?
> >
> > John
> >
> > "Marty" wrote:
> >
> > > I have similar problems on some remote sites. Some are running the client
> > > over ISDN which naturally timesout and should pick up the line again when
> > > there is activity. But when the user generates some activity we get the db
> > > process dead message. We migrated these sites from Sybase which never had
> > > these issues and seemed more robust in these situations.
> > >
> > > Is there any config options we can use on the client or server to make the
> > > connections less fickle?
> > > Is there any updated client that might be more reliable?
> > > We are using SQL Server 2000 Sp4 but the client is the one on the original
> > > SQl Server 2000 CD.
> > >
> > > Thanks,
> > >
> > >
> > >
> > > "Dan Guzman" wrote:
> > >
> > > > The dbprocess dead error indicates a terminated connection rather than a
> > > > timeout. The application needs to be smart enough to attempt to reconnect
> > > > following the error.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > <masterwee@.gmail.com> wrote in message
> > > > news:1162278624.684826.264940@.m73g2000cwd.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I really need some help here. I have a few sql clients that connects to
> > > > > my sql server through wireless connection. Sometimes, these wireless
> > > > > connections link is down for a few seconds. When user try to continue
> > > > > to run some query, the db process dead error message appears. Is the
> > > > > anyway to extend the time out setting to longer time so that this
> > > > > problem could be minimise?
> > > > >
> > > > > Thanks a million.
> > > > >
> > > >

expression-based connection strings difficult to manage

Expression-based connection strings are great but they are specific to each report which makes them difficult to maintain. Shared expression-based connection strings are not supported.

I'm looking for a way around this. I'm hoping to write a custom data extension, and in the custom data extension, do all the "dynamic" logic that sets the connection string (which you normally would be doing in your expression-based connection string).

I know the above can be done. What I don't know is how, inside a custom data extension, to get the value of expressions like User!UserID? (I want the connection to run under stored credentials, but I want to customize the connection string and add a property to it based on the UserID who is logged into Report Manager.)

Create a assembly with a static method that takes a text parameter(userid) and returns the connectionstring and add that assembly (look in BOL for custom code or something like that on how to add it to a report and deploy the assembly to the report server) and then in the datasource it is something like this =Assembly.Class.Function(User!UserID).

I have done it myself, but in my case it was depending on the reportpath.|||

Interesting workaround, SteffoS. Thanks for the reply. That may be the best available. The only downside is that you have to actually use expression-based connection strings. And in doing that, there are two unfortunate problems:

1. Every report must call that expression instead of doing development as normal

2. (More importantly) you can't preview your dataset when it's tied to an expression-based connection string. The workaround to that is switching to a shared datasource during development, then making it an expression before you deploy, but that's a pain in the rear.

Anyone else have thoughts?

Expression based connection strings

Hi Folks,
I am having difficulty using expression based connection strings. I have
followed the instructions in this posting
http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx
, and as far as I can tell, I have everything set up correctly, but when I
put in the ServerName parameter and run the report I get an error,
"An error has occurred during report processing.
Cannot create a connection to data source 'AWDQuery'.
For more information about this error navigate to the report server on the
local server machine, or enable remote errors "
When I send the connection string to a text box to check it, it is fine. I
even copied it out of the text box and inserted it into another report to
ensure there wasn't a stray control character embedded. Here is the data
source connection string from the XML file.
"data source=" & Parameters!ServerName.Value & ";initial catalog=AWDQuery;"
ServerName is a valid parameter on the report.
I am using SQL Server 2005.
Any ideas?
Thanks,
BobHere is the error message from the log.
w3wp!processing!b!1/30/2007-13:29:49:: e ERROR: Data source 'BrandNew': An
error has occurred. Details:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'BrandNew'. -->
System.ArgumentException: Format of the initialization string does not
conform to specification starting at index 0.
w3wp!processing!b!1/30/2007-13:29:49:: e ERROR: An exception has occurred in
data source 'BrandNew'. Details:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'BrandNew'. -->
System.ArgumentException: Format of the initialization string does not
conform to specification starting at index 0.
w3wp!processing!b!1/30/2007-13:29:49:: i INFO: Merge abort handler called
for ID=-1. Aborting data sources ...
w3wp!processing!b!1/30/2007-13:29:49:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'BrandNew'. -->
System.ArgumentException: Format of the initialization string does not
conform to specification starting at index 0.
-- End of inner exception stack trace --
w3wp!webserver!b!01/30/2007-13:29:49:: e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'BrandNew'. -->
System.ArgumentException: Format of the initialization string does not
conform to specification starting at index 0.
"Bob McDermott" wrote:
> Hi Folks,
> I am having difficulty using expression based connection strings. I have
> followed the instructions in this posting
> http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx
> , and as far as I can tell, I have everything set up correctly, but when I
> put in the ServerName parameter and run the report I get an error,
> "An error has occurred during report processing.
> Cannot create a connection to data source 'AWDQuery'.
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors "
> When I send the connection string to a text box to check it, it is fine. I
> even copied it out of the text box and inserted it into another report to
> ensure there wasn't a stray control character embedded. Here is the data
> source connection string from the XML file.
> "data source=" & Parameters!ServerName.Value & ";initial catalog=AWDQuery;"
> ServerName is a valid parameter on the report.
> I am using SQL Server 2005.
> Any ideas?
> Thanks,
> Bob
>

Express remote connection on a LAN help

I have recently installed SQL Server Express on a desktop machine for a program that we use. I am in need of adding additional machines to gain access to the server for the database. Right now, the program is working on the local machine fine. What do I need to do or enable for access on a local LAN? All machines are XP Pro on a workgroup. Also, the software we use has a remote connection manager that requires inputting the server address for the machine. Is it better to use the computer name or IP address, and then what is that format? SQL Instance name is 'uesafh' if that helps. Any other information that I can provide, please let me know.

Thanks!

"What do I need to do or enable for access on a local LAN?"

enable remote connections first, launch Sql Surface Area Config tool (comes with Express) and select the connections and services link. you will then see a Remote Connections setting, set it to enabled.

"Is it better to use the computer name or IP address, and then what is that format?"

either is fine, though I find the probabilty of renaming a server is higher than assigning it a new IP, so I generally will use IP.

TCP/IP protocol connection format: ServerName or IP[\NamedInstance][,Port# (defaults to 1433)]

if you intend on using tcp/ip just ensure it is enabled and part of the protocol rotation in Sql Config Mgr. (another tool that ships with Express)

|||Ok, thanks for the help so far.

So for the server name to connect to on the remote systems in the LAN, would the format be:

'\\192.168.0.30\uesafh' ? I had tried that before and it would connect, however the connection would drop after a couple of logins and then would not be restored by even rebooting when attempting to login.

The program we use allows for multiple users to log into it, using their own login for the program that is kept and verified in the db. Should I use a 'sa' login with a password as part of the remote connection instead of Windows Authentication?|||

192.168.0.30\uesafh (this is the correct format assuming its listening on 1433)

you should be able to use either but be aware that windows authentication is using your currently logged on account so you should be in the same domain/workgroup and have assigned access. If you are just testing it you may want to use sql authentication as opposed to windows, but i believe in sql 2005 sql authentication is also of by default.

|||Ok, thanks for the help so far. I have been reading however, that I need to install SQL express on each client computer? Is that right? Is there any necessary software installs that I need to make these connections work? I was wondering particularly about .NET 2.0, MSXML 6.0, or do I need anything to get the connection to work? I have also read that I needed to install the 213 m.b. SQL server express toolkit?

Thanks again for the help!|||

http://msdn.microsoft.com/vstudio/express/sql/download/

SQL Server Management Studio Express

SQL Server Management Studio Express is installed by running the setup program for either SQL Server Express with Advanced Services or SQL Server Express Toolkit. Before you install SQL Server Management Studio Express, you must install MSXML 6.0.

thats all you need