Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Thursday, March 29, 2012
Extract Data From A Column
Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
quote:
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>
Extract color formatted text from code editor?
Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:
> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:
> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql
Extract color formatted text from code editor?
Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:
> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:
> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>
Monday, March 26, 2012
External proc
Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
Aju
Aju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
Aju
Aju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
External proc
Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
External proc
Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com
Friday, March 23, 2012
extending report server to support printing
Hi all
I am following the 'Deploying the Printer Delivery Sample' in order to add
printing functionality to reports.
After compiled code was put in both the reports server's and manager's bin
directories( and thier config files were
set accordingly), I can see the new printing delivery extension listed by
the server side (using the report server web service method
.ListExtensions(ExtensionTypeEnum.Delivery);).
But.. no sign for that extension when i open reports in the html viewer nor
in the subscriptions deliveries options..
I am not sure exactly where should i expect to see it if at all.. should i
create a button which will call the printing extension on click event'
I know that in the reports manager it should be listed in the subscription
deliveries options.
Another thing is that i am not sure i have added the codegroup section in
the *policy.config files correctly.
In the reports manager config file i had to remove it because it generated a
'no object reference' error..
I guess this should interfier at some point.
Thanks alot for your attention
ReaHi Rea:
Check Bryan's blog for some clarification on the codegroup placement:
http://weblogs.asp.net/bryanke/archive/2004/05/14/132110.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 26 Oct 2004 16:05:50 +0200, "Rea Peleg" <rea_p@.afek.co.il>
wrote:
>Hi all
>I am following the 'Deploying the Printer Delivery Sample' in order to add
>printing functionality to reports.
>After compiled code was put in both the reports server's and manager's bin
>directories( and thier config files were
>set accordingly), I can see the new printing delivery extension listed by
>the server side (using the report server web service method
>.ListExtensions(ExtensionTypeEnum.Delivery);).
>But.. no sign for that extension when i open reports in the html viewer nor
>in the subscriptions deliveries options..
>I am not sure exactly where should i expect to see it if at all.. should i
>create a button which will call the printing extension on click event'
>I know that in the reports manager it should be listed in the subscription
>deliveries options.
>Another thing is that i am not sure i have added the codegroup section in
>the *policy.config files correctly.
>In the reports manager config file i had to remove it because it generated a
>'no object reference' error..
>I guess this should interfier at some point.
>Thanks alot for your attention
>Rea
>
>
I am following the 'Deploying the Printer Delivery Sample' in order to add
printing functionality to reports.
After compiled code was put in both the reports server's and manager's bin
directories( and thier config files were
set accordingly), I can see the new printing delivery extension listed by
the server side (using the report server web service method
.ListExtensions(ExtensionTypeEnum.Delivery);).
But.. no sign for that extension when i open reports in the html viewer nor
in the subscriptions deliveries options..
I am not sure exactly where should i expect to see it if at all.. should i
create a button which will call the printing extension on click event'
I know that in the reports manager it should be listed in the subscription
deliveries options.
Another thing is that i am not sure i have added the codegroup section in
the *policy.config files correctly.
In the reports manager config file i had to remove it because it generated a
'no object reference' error..
I guess this should interfier at some point.
Thanks alot for your attention
ReaHi Rea:
Check Bryan's blog for some clarification on the codegroup placement:
http://weblogs.asp.net/bryanke/archive/2004/05/14/132110.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 26 Oct 2004 16:05:50 +0200, "Rea Peleg" <rea_p@.afek.co.il>
wrote:
>Hi all
>I am following the 'Deploying the Printer Delivery Sample' in order to add
>printing functionality to reports.
>After compiled code was put in both the reports server's and manager's bin
>directories( and thier config files were
>set accordingly), I can see the new printing delivery extension listed by
>the server side (using the report server web service method
>.ListExtensions(ExtensionTypeEnum.Delivery);).
>But.. no sign for that extension when i open reports in the html viewer nor
>in the subscriptions deliveries options..
>I am not sure exactly where should i expect to see it if at all.. should i
>create a button which will call the printing extension on click event'
>I know that in the reports manager it should be listed in the subscription
>deliveries options.
>Another thing is that i am not sure i have added the codegroup section in
>the *policy.config files correctly.
>In the reports manager config file i had to remove it because it generated a
>'no object reference' error..
>I guess this should interfier at some point.
>Thanks alot for your attention
>Rea
>
>
Wednesday, March 21, 2012
Extended Stored Procedures in SQL 2005?
The Extended Stored Procedures (Written in C++ unmanaged code) are supported in SQL Server 2005?
Thanks
Yes they are, but they are being deprecated.Just out of curiousity; what are you writing that cannot be done in a .NET method, but needs an xp?
Niels
Monday, March 19, 2012
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
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
Monday, March 12, 2012
extended procedures
I'm finding lots of code samples for writing extended procs, but *all* of
them
do nothing with SQL. Hello or calculating free disk space are common
examples.
I'm writing a sp because I want to work with SQL data. How do I do
that from an xp?
Do I have to go back around through ADO?
I would think that ODS would have a method in the API to interact with
the data, but you can't tell that from the sample code I've found so far.
Thanks,
Brad.Normally an XP will perform functions that aren't available (or aren't fast
enough) via standard T-SQL. If all you want to do is execute standard T-SQL
statements against your database, you probably want to write a regular SP.
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23v1TOVwYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> I'm finding lots of code samples for writing extended procs, but *all* of
> them
> do nothing with SQL. Hello or calculating free disk space are common
> examples.
> I'm writing a sp because I want to work with SQL data. How do I do
> that from an xp?
> Do I have to go back around through ADO?
> I would think that ODS would have a method in the API to interact with
> the data, but you can't tell that from the sample code I've found so far.
> --
> Thanks,
> Brad.
>
>
them
do nothing with SQL. Hello or calculating free disk space are common
examples.
I'm writing a sp because I want to work with SQL data. How do I do
that from an xp?
Do I have to go back around through ADO?
I would think that ODS would have a method in the API to interact with
the data, but you can't tell that from the sample code I've found so far.
Thanks,
Brad.Normally an XP will perform functions that aren't available (or aren't fast
enough) via standard T-SQL. If all you want to do is execute standard T-SQL
statements against your database, you probably want to write a regular SP.
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23v1TOVwYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> I'm finding lots of code samples for writing extended procs, but *all* of
> them
> do nothing with SQL. Hello or calculating free disk space are common
> examples.
> I'm writing a sp because I want to work with SQL data. How do I do
> that from an xp?
> Do I have to go back around through ADO?
> I would think that ODS would have a method in the API to interact with
> the data, but you can't tell that from the sample code I've found so far.
> --
> Thanks,
> Brad.
>
>
Friday, March 9, 2012
Expressions and other code?
Is there any way to write C# code in SQL Server Reporting Services?
yes, more details could be found here:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
Expression question
I don't know any other way to ask it than to post my code and ask what's wrong w/ it. It looks right to me, but I'm getting an error every time. Any and all suggestions are greatly appreciated!
This one returns a value of False:
=iif( Fields!Appearance.Value=4, Fields!Appearance.Value="Poor", iif( Fields!Appearance.Value=3,Fields!Appearance.Value="Fair", iif( Fields!Appearance.Value=2,Fields!Appearance.Value="Good", iif( Fields!Appearance.Value=1,Fields!Appearance.Value="Excellent", Fields!Appearance.Value="Unknown"))))
This one just errors out:
=Select Case Month(Fields!SubmitDate.Value)
case 1
Month(Fields!SubmitDate.Value)= "January"
case 2
Month(Fields!SubmitDate.Value)= "February"
case 3
Month(Fields!SubmitDate.Value)= "March"
case 4
Month(Fields!SubmitDate.Value)= "April"
case 5
Month(Fields!SubmitDate.Value)= "May"
case 6
Month(Fields!SubmitDate.Value)= "June"
case 7
Month(Fields!SubmitDate.Value)= "July"
case 8
Month(Fields!SubmitDate.Value)= "August"
case 9
Month(Fields!SubmitDate.Value)= "September"
case 10
Month(Fields!SubmitDate.Value)= "October"
case 11
Month(Fields!SubmitDate.Value)= "November"
case 12
Month(Fields!SubmitDate.Value)= "December"
case Else
Month(Fields!SubmitDate.Value)= "Unknown"
I've fixed the first expression...here's the new code:
=iif( Fields!Appearance.Value=4, "Poor", iif( Fields!Appearance.Value=3, "Fair", iif( Fields!Appearance.Value=2, "Good", iif( Fields!Appearance.Value=1, "Excellent","Unknown"))))
Still having trouble w/ the case statement though
|||
This one returns a value of False:
=iif( Fields!Appearance.Value=4, Fields!Appearance.Value="Poor", iif( Fields!Appearance.Value=3,Fields!Appearance.Value="Fair", iif( Fields!Appearance.Value=2,Fields!Appearance.Value="Good", iif( Fields!Appearance.Value=1,Fields!Appearance.Value="Excellent", Fields!Appearance.Value="Unknown"))))
This one just errors out:
=Select Case Month(Fields!SubmitDate.Value)
case 1
Month(Fields!SubmitDate.Value)= "January"
case 2
Month(Fields!SubmitDate.Value)= "February"
case 3
Month(Fields!SubmitDate.Value)= "March"
case 4
Month(Fields!SubmitDate.Value)= "April"
case 5
Month(Fields!SubmitDate.Value)= "May"
case 6
Month(Fields!SubmitDate.Value)= "June"
case 7
Month(Fields!SubmitDate.Value)= "July"
case 8
Month(Fields!SubmitDate.Value)= "August"
case 9
Month(Fields!SubmitDate.Value)= "September"
case 10
Month(Fields!SubmitDate.Value)= "October"
case 11
Month(Fields!SubmitDate.Value)= "November"
case 12
Month(Fields!SubmitDate.Value)= "December"
case Else
Month(Fields!SubmitDate.Value)= "Unknown"
I've fixed the first expression...here's the new code:
=iif( Fields!Appearance.Value=4, "Poor", iif( Fields!Appearance.Value=3, "Fair", iif( Fields!Appearance.Value=2, "Good", iif( Fields!Appearance.Value=1, "Excellent","Unknown"))))
Still having trouble w/ the case statement though
|||
Instead of using a case statement try this:
=MonthName(DatePart("m",Fields!SubmitDate.Value))
expression question
Hi
Dose "Expression" can write any syntax such as CASE... WHEN... Structure?
I have a serial code such as B1, B2, B3.
When B1, the "jump to url" to http://b1.aspx. B2 the http://b2.aspx
I wirte this use IIF now but feel this way dosen't make sense.
So is any other way can instead of IIF?
Thanks for help! (I use RS 2005)
Anginot sure if you can use the case statement, in theory I don't see why
not. I also was getting irritated writing lengthy or having the giant
iif statements, so instead I now create methods in the Code tab and
simply pass the value into the method and return the result.
seems neater this way|||Try using SWITCH instead of IIF.
The syntax is
SWITCH(test1,option1,test2,option2,...)
compared to
IIF(test1,option1,IIF(test2,option2,IIF(...)))
It reads the comma separated sequence in pairs and executes the first
one that has a test = true.
Dose "Expression" can write any syntax such as CASE... WHEN... Structure?
I have a serial code such as B1, B2, B3.
When B1, the "jump to url" to http://b1.aspx. B2 the http://b2.aspx
I wirte this use IIF now but feel this way dosen't make sense.
So is any other way can instead of IIF?
Thanks for help! (I use RS 2005)
Anginot sure if you can use the case statement, in theory I don't see why
not. I also was getting irritated writing lengthy or having the giant
iif statements, so instead I now create methods in the Code tab and
simply pass the value into the method and return the result.
seems neater this way|||Try using SWITCH instead of IIF.
The syntax is
SWITCH(test1,option1,test2,option2,...)
compared to
IIF(test1,option1,IIF(test2,option2,IIF(...)))
It reads the comma separated sequence in pairs and executes the first
one that has a test = true.
Expression issue with Custom Data Flow Component and Custom property
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @.[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @.[User::varCurrentDate] ), 2 ) + "\\" + (DT_WSTR, 4)YEAR( @.[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @.[User::varCurrentDate] ), 2 ) + ".VER"
@.[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912\189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @.[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912\189912.VER" and I expected "DB200701\200701.VER"
Any idea ?
Are you certain that the data flows are being executed in the order you expect?
What is the scope of your variable?
I tried a test case with 2 data flows connected to each other in the control flow. In the first, I had a row count transform populate a variable (of package scope). In the second, I set a dataflow property expression to use the variable, and confirmed that it did use the value set by the Row Count, so it seems your scenario should work...
Mark
|||Sorry,
You're right.
I was confused because even in debug mode (when execution is interupted with a DataViewer), the property value was "DB189912\189912.VER"
So I have added a ComponentMetaData.FireInformation with the property value, and the value was "DB200701\200701.VER"
Next time, I will start with the ComponentMetaData.FireInformation... ;-)
Sorry again
Thanks a lot
|||No problem, glad to hear you have it working!Wednesday, March 7, 2012
Expression for Running Total
I want to write an expression where the running total should be
calculated if the previous value for a column(invoice code) is not
equal to the current value for the invoice code.
The expression should be something like this:
iif( current invoice code is not equal to the previous invoice
code,sum(payment),0)
How can I compare the current and previous value of a column in the
expression?
In Crystal we can say calculate running total with the change of
Invoice Code in the Edit Running Total Field Box. Trying to do the same
concept in RS where the running total should be calculated with the
change of invoice code.
Please help!
ThanksI think you just need to add a (table) grouping to your data (based on
InvoiceCode). In the group header you would do the calculation of the total
payment. In the group details you can show the invoice detail rows.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"bak" <bakgroup@.gmail.com> wrote in message
news:1117132892.250745.323640@.o13g2000cwo.googlegroups.com...
>I want to write an expression where the running total should be
> calculated if the previous value for a column(invoice code) is not
> equal to the current value for the invoice code.
> The expression should be something like this:
> iif( current invoice code is not equal to the previous invoice
> code,sum(payment),0)
> How can I compare the current and previous value of a column in the
> expression?
> In Crystal we can say calculate running total with the change of
> Invoice Code in the Edit Running Total Field Box. Trying to do the same
> concept in RS where the running total should be calculated with the
> change of invoice code.
> Please help!
> Thanks
>|||This did not help. The solution to my problem is to write an
expression where if the current invoice code is not equal to the
previous invoice code then calculate sum. I am unable to find a VB.NET
function which helps me do this.
Can anyone tell me some good sites where I can get information about
all the VB.NET functions that can be used in Reporting Services. I
have been looking for the function in VS.NET documentation but unable
to find something that could help me compare the current and previous
value of a column.
Thanks.
calculated if the previous value for a column(invoice code) is not
equal to the current value for the invoice code.
The expression should be something like this:
iif( current invoice code is not equal to the previous invoice
code,sum(payment),0)
How can I compare the current and previous value of a column in the
expression?
In Crystal we can say calculate running total with the change of
Invoice Code in the Edit Running Total Field Box. Trying to do the same
concept in RS where the running total should be calculated with the
change of invoice code.
Please help!
ThanksI think you just need to add a (table) grouping to your data (based on
InvoiceCode). In the group header you would do the calculation of the total
payment. In the group details you can show the invoice detail rows.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"bak" <bakgroup@.gmail.com> wrote in message
news:1117132892.250745.323640@.o13g2000cwo.googlegroups.com...
>I want to write an expression where the running total should be
> calculated if the previous value for a column(invoice code) is not
> equal to the current value for the invoice code.
> The expression should be something like this:
> iif( current invoice code is not equal to the previous invoice
> code,sum(payment),0)
> How can I compare the current and previous value of a column in the
> expression?
> In Crystal we can say calculate running total with the change of
> Invoice Code in the Edit Running Total Field Box. Trying to do the same
> concept in RS where the running total should be calculated with the
> change of invoice code.
> Please help!
> Thanks
>|||This did not help. The solution to my problem is to write an
expression where if the current invoice code is not equal to the
previous invoice code then calculate sum. I am unable to find a VB.NET
function which helps me do this.
Can anyone tell me some good sites where I can get information about
all the VB.NET functions that can be used in Reporting Services. I
have been looking for the function in VS.NET documentation but unable
to find something that could help me compare the current and previous
value of a column.
Thanks.
expression
In RS, is it possible to code an expression so that if a field contains the
word "STAT" that whole row is highlighted in another color? If not, how about
a that field?
I can do it with numeric values, not text.
Thanks.On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> In RS, is it possible to code an expression so that if a field contains the
> word "STAT" that whole row is highlighted in another color? If not, how about
> a that field?
> I can do it with numeric values, not text.
> Thanks.
In Layout view, select the field that you want to change the
background color for and select F4 (for the Properties window). To the
right of Background Color, select <Expression...> and enter an
expression similar to the following:
=iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That worked. I ad the syntax wrong. Thanks!
"EMartinez" wrote:
> On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > In RS, is it possible to code an expression so that if a field contains the
> > word "STAT" that whole row is highlighted in another color? If not, how about
> > a that field?
> >
> > I can do it with numeric values, not text.
> >
> > Thanks.
>
> In Layout view, select the field that you want to change the
> background color for and select F4 (for the Properties window). To the
> right of Background Color, select <Expression...> and enter an
> expression similar to the following:
> =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On May 24, 9:12 pm, brian <b...@.discussions.microsoft.com> wrote:
> That worked. I ad the syntax wrong. Thanks!
> "EMartinez" wrote:
> > On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > > In RS, is it possible to code an expression so that if a field contains the
> > > word "STAT" that whole row is highlighted in another color? If not, how about
> > > a that field?
> > > I can do it with numeric values, not text.
> > > Thanks.
> > In Layout view, select the field that you want to change the
> > background color for and select F4 (for the Properties window). To the
> > right of Background Color, select <Expression...> and enter an
> > expression similar to the following:
> > =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
word "STAT" that whole row is highlighted in another color? If not, how about
a that field?
I can do it with numeric values, not text.
Thanks.On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> In RS, is it possible to code an expression so that if a field contains the
> word "STAT" that whole row is highlighted in another color? If not, how about
> a that field?
> I can do it with numeric values, not text.
> Thanks.
In Layout view, select the field that you want to change the
background color for and select F4 (for the Properties window). To the
right of Background Color, select <Expression...> and enter an
expression similar to the following:
=iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That worked. I ad the syntax wrong. Thanks!
"EMartinez" wrote:
> On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > In RS, is it possible to code an expression so that if a field contains the
> > word "STAT" that whole row is highlighted in another color? If not, how about
> > a that field?
> >
> > I can do it with numeric values, not text.
> >
> > Thanks.
>
> In Layout view, select the field that you want to change the
> background color for and select F4 (for the Properties window). To the
> right of Background Color, select <Expression...> and enter an
> expression similar to the following:
> =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On May 24, 9:12 pm, brian <b...@.discussions.microsoft.com> wrote:
> That worked. I ad the syntax wrong. Thanks!
> "EMartinez" wrote:
> > On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > > In RS, is it possible to code an expression so that if a field contains the
> > > word "STAT" that whole row is highlighted in another color? If not, how about
> > > a that field?
> > > I can do it with numeric values, not text.
> > > Thanks.
> > In Layout view, select the field that you want to change the
> > background color for and select F4 (for the Properties window). To the
> > right of Background Color, select <Expression...> and enter an
> > expression similar to the following:
> > =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
Sunday, February 26, 2012
Express Problems
Hi All
We recently moved from FW 1.1 to FW 2.0, and 2003 to 2005, also now using SQL Express.
The problem im having is that in code ie from this function:
visual basic code:Protected Function ExecuteNonQuery(ByVal sSql As String, ByVal eDBType As cDBSettings.DatabaseType, Optional ByVal sDatabaseName As String = "", Optional ByVal bUseTransaction As Boolean = True) As Boolean
'Executes a sql statement and returns the result
Dim oCommand As SqlCommand = Nothing
Dim oConnection As SqlConnection = Nothing
Dim oTransaction As SqlTransaction = Nothing
Dim bSuccess As Boolean
Try
oConnection = GetDBConnection(eDBType, sDatabaseName)
oConnection.Open()
If bUseTransaction Then
oTransaction = oConnection.BeginTransaction()
End If
oCommand = New SqlCommand(sSql, oConnection)
With oCommand
If bUseTransaction Then
.Transaction = oTransaction
End If
.ExecuteNonQuery()
If bUseTransaction Then
oTransaction.Commit()
End If
End With
bSuccess = True
Catch e As Exception
If Not oTransaction Is Nothing Then
oTransaction.Rollback()
End If
bSuccess = False
Finally
If Not oTransaction Is Nothing Then oTransaction.Dispose()
If Not oCommand Is Nothing Then oCommand.Dispose()
If Not oConnection Is Nothing Then oConnection.Close() : oConnection.Dispose()
oCommand = Nothing
oConnection = Nothing
oTransaction = Nothing
End Try
Return bSuccess
End Function
Running this SQL statement:
visual basic code:
if (SELECT DATABASEPROPERTYEX('GateKeeper_1014', 'UserAccess')) = 'MULTI_USER' exec sp_dboption N'GateKeeper_1014', N'dbo use only', N'true'
The problem throws an Error that i must declare a scalar variable, but i pasted that into the SQL Manager and it runs.
So i put sql.tolower and i get the problem that it cannot find gatekeeper_1014, but again if i paste this into the SQL Manager it runs fine.
Also the fact that other developers in my office having the same setup some require uppercase SQL and some require them to be lower on non reserved words, as soon as i have reserved words lower case it throughs a general SQL error.
I have found that the colliation might be the issue but i have tried the compatibility mode on the Express install but still no avail!
If anyone can shed some light on this or put me in the direction of some information, i would be grateful.
Regards
Danny
Hi Danny,
There are differences between running a T-SQL command in a query window and trunning it using a SqlCommand object. (I'm guessing you're using a SqlCommand.) I find that it's easier to run single statements in a SqlCommand at a time. Here is a sample that does what you want:
Sub Main()Dim cnn As SqlConnection
Dim cmd As SqlCommand
cnn = New SqlConnection("Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=True")
cnn.Open()
cmd = New SqlCommand("SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess')")
cmd.Connection = cnn
Dim s As String
s = cmd.ExecuteScalar()
If s = "MULTI_USER" Then
cmd.CommandText = "sp_dboption N'AdventureWorks', N'dbo use only', N'true'"
cmd.ExecuteNonQuery()
End If
cnn.Close()
End Sub
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
Sunday, February 19, 2012
Exporting to Excel; timeout
Exporting to Excel; timeout
I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”
Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service
Follow this KB http://support.microsoft.com/default.aspx/kb/909678Friday, February 17, 2012
Exporting temporary table to excel spreadsheet
How would I go about programatically exporting all the data queried in a temporary table to an excel spreadsheet. My code is inside a stored procedure that builds a temporary table wiht the desired information. This is the table I am trying to export.
Yes, we do have reporting services here. They're used with our more complex applications.
Thanks for your help today Bryan, it was most useful!
There is no easy way to do this like you've described above. Excel prefers to work with (non-temporary) tables and views. Is it possible to encapsulate your logic in a view?
Bryan
|||Yes, I could put all the final information in a view. Is this going to help?I'm a new in SQL and I'm not familiar with all the intricacies of the environment.
Thanks for the help.
Lauran
|||
So, one thing you can do is start with Excel and see what it most easily can work with. If you establish a connection to your SQL Server database from Excel, you should get a list of all the objects it has access to. You should see a list of tables and views (if you have any in the database). Once you create your view, it will show up in that list.
If you're new to SQL, moving logic from a stored proc to a view can be tricky because many folks use stored procs to create a series of temporary tables to store intermediary result sets. Views do not support this technique and will force you to write your query as a single SELECT statement (which is a good thing from a learning perspective).
Good luck,
Bryan
The procedure I'm running is collecting data and is supposed to output it. It's a report. Now the people who need this report want to do the minimal amount of work, which is why I think that if they need to start up excel and select objects and such, it won't be a good solution.
What I want is a way of creating the excel file behind the scenes with a minimal amount of effort.|||
Ah! Now I'm with you.
Do you have Reporting Services installed? With Reporting Services, you can set up a report, have it rendered to Excel, schedule it to be placed on a file share or delivered via email to your consumers, etc.
You should stay clear of a multi-stepped stored proc if using SSRS.
Thanks,
Bryan
Yes, we do have reporting services here. They're used with our more complex applications.
I am a Co-op student working here for 4 months. They asked this of me and left it in my hands - they didn't have any specific requirements. As long as they get that it's fine with them. I knew some SQL and I quickly drew up a solution for that. Personally I would like to make use of the reporting services but I don't know where to start.
Is using my already existing SQL code going to help or arriving at the report requires a different paradigm?
Any pointers?
|||
Try BCP.
You can either invoke it from a cmd window or use xp_cmdshell in SS.
Code Snippet
From cmd:
bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd
From SQL Server:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'
Look up BCP in BOL for more options and parameters.
|||Start by writing your code as a single SELECT statement. Open Business Intelligence Development Studio and create a new reporting services project. Set up a data source pointing to the database server. (Since SSRS is already in use, you may want to talk to some folks there about leveraging an existing shared data source.)
Add a report to your project by right-clicking the Reports folder in the Solution Explorer, selecting New Item, and then choosing Report. (Don't use the report wizard.)
On the DataSets tab, create a new data set leveraging the data source from above. Use your SELECT statement to return data.
Go to the layout tab and drag a table from the toolbox to the page. Open your data sets viewer (Ctrl+Alt+D if it isn't already open). Drag fields from your data set to the details row of the table. Go to the preview tab to verify the results.
So, at this point, you may want to spend a little time formatting, tweaking, etc. Once that is completed, deploy the report to your SSRS environment. You may need some help from the local admins to get everything set for this to work.
Now, locate your report in report manager. Open the report in the browser. You should see an "Select a format" drop-down. Select Excel from that drop down and click Export. When you get a pop-up dialog, select Open to view the report in Excel.
You may have a little back-and-forth to do to get the report as you want it in Excel. Books Online covers Excel rendering pretty well.
Once you have the report the way you want it, you can then set up a subscription to deliver the report to a file share or to an email address. When you set this up, you will have the option to select a format for the report file. You can use Excel.
There is a lot to learn in all of this. The BCP suggestion from above may get you where you want to go if you use a ".CSV" extension on the file b/c the extension is associated with Excel on most machines.
Good luck,
Bryan
Thanks for your help today Bryan, it was most useful!
Thanks for your reply too Dale.
Have a great day.
Subscribe to:
Posts (Atom)