How to change the data connection for another DB using different DSN name

Hi,

i have 2 MSSQL DBs.
im using DSN name to connect MSSQL DB to generate the report. for example, i have designed a report CR1 from the DB using dsn, named DSN1 which refers
the DB named Master1.

but now i want to connect the same report CR1 in a another dsn name called as
DSN2 which refers MSSQL DB named as Master2.
(because the both DB's have the same table, but i want the report to print from another DB's table. )

but at runtime if i do the coding from vb application like,

crystalreport1.connect = "DSN=DSN1"

(this coding works if the dsn name as DSN1)

its giving error msg like "error detected by database dll".

How could i make the data connectivity at runtime to point the different dsn name ie, DSN2. (consider i design the report manually at design mode of CR and connect the DB thru DSN ie, DSN1 refers Master1 DB and call it in vb application at runtime.):confused:
Hello

We have developed a report in crystal that connects with our local database. After development we want to deploy the same to our clients with different data soruce name and data base name.

Presently we are taking each report file and configuring the client database by replacing the existing data source.

How can we achieve this programmatically thru VB?

Thanks & Regards
subramanisql

How to call a stored procedure from a function

Hi
The idea is to generate the sequence for some of the fields in the table.
Since the identity property sets the sequence to only one of the field in
the table, decided to have a UDF that would create a new sequence value
generated for the fields.
But when the function is called i get the error
"Only functions and extended stored procedures can be executed from within a
function." Please suggest
The below table will hold the names of the fields that would require the
sequence to be generated and the last value generated updated by the stored
procedure.
CREATE table SEQ_GENERATOR_TBL
( seq_name varchar(50) not null,
last_value bigint default 0 not null);
GO
insert into SEQ_GENERATOR_TBL(seq_name)
values('SEQ_IS_GLOBAL_IDENTIFIER');
CREATE function SEQ_GENERATOR_FUNC
(@.p_seq_name varchar(50))
RETURNS bigint
AS
BEGIN
DECLARE @.ret_next_value bigint
SET @.ret_next_value = (select last_value+1 as next_value
from SEQ_GENERATOR_TBL
WHERE seq_name = @.p_seq_name);
EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
RETURN @.ret_next_value;
END
GO
CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
@.p_seq_name varchar(50),
@.p_curr_value bigint
AS
BEGIN
BEGIN TRANSACTION;
UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
WHERE seq_name = @.p_seq_name;
COMMIT TRANSACTION;
RETURN;
END
GOJP
Do you expect the same sequence as the IDENTITY property is set to? Have you
considered using computed column?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:CD9841BA-3ED6-4140-BC3D-3588FC522226@.microsoft.com...
> Hi
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from within
> a
> function." Please suggest
> The below table will hold the names of the fields that would require the
> sequence to be generated and the last value generated updated by the
> stored
> procedure.
> CREATE table SEQ_GENERATOR_TBL
> ( seq_name varchar(50) not null,
> last_value bigint default 0 not null);
> GO
> insert into SEQ_GENERATOR_TBL(seq_name)
> values('SEQ_IS_GLOBAL_IDENTIFIER');
> CREATE function SEQ_GENERATOR_FUNC
> (@.p_seq_name varchar(50))
> RETURNS bigint
> AS
> BEGIN
> DECLARE @.ret_next_value bigint
> SET @.ret_next_value = (select last_value+1 as next_value
> from SEQ_GENERATOR_TBL
> WHERE seq_name = @.p_seq_name);
> EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
> RETURN @.ret_next_value;
> END
> GO
> CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
> @.p_seq_name varchar(50),
> @.p_curr_value bigint
> AS
> BEGIN
> BEGIN TRANSACTION;
> UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
> WHERE seq_name = @.p_seq_name;
> COMMIT TRANSACTION;
> RETURN;
> END
> GO
>|||Where do you want to show the data?
If you use reports do the numbering there
Madhivanan|||JP (JP@.discussions.microsoft.com) writes:
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from
> within a function." Please suggest
Rework and redesign. A function must not change database state, why
updates are not permitted, and neither calls to stored procedure as
they could do about anything.
Itzik Ben-Gan discussed a couple of solution in his column T-SQL Black
Belt in SQL Server Magazine a couple of issues back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to Call a c#.net class from stored procedure

Hi,

I would like to know how can I call a c#.net class from a stored procedure.

I have a class file which will generate a pdf file from sql reporting services and mail that file. Now I want to call this class from a stored procedure. Is it possible, if so please send me the sample code.

Thanks in advance

Regards

Babu

Which version of SQL Server are you using ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

SQL Server 2005

|||

Please take a look at the SQLCLR samples in the setup cd. Or you can also look at the examples in Books Online. You can create a SQLCLR stored procedure for example to do this. You can start at the link below:

http://msdn2.microsoft.com/en-us/library/ms131102(SQL.90).aspx

How to bring out column names with bcp?

Hello, everyone:

I used bcp to generate a txt file as:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'

It works fine except without column names of table. Does any one have idea that can bring out column names from table?

Thanks

ZYTColumn names is not one of the arguments of bcp.

You could do a "select * from <table> where 0=1" and save the output to a txt file, or script out the table schema and carry that across to the new location.|||Or you could use a union query with the column headings as literal strings in the row.|||Or you could use a union query with the column headings as literal strings in the row.I was going to suggest that. The main problem is that you then need to convert all data to character... which then means you need to be a little clever if you need to order your export for whatever reason.

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.|||I was going to suggest that. The main problem is that you then need to convert all data to character... which then means you need to be a little clever if you need to order your export for whatever reason.

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.

I am honored|||Lol - no offense. I mean I think he had some complicated automation code - I've never tried it. My own method, as it happens, is virtually identical to yours.|||Never [played with it, but my guess is you could use com automation..good luck|||This the way I export my "headers" in specific situation but you can modify it so it works for you as well.

DECLARE @.tmp varchar(1000), @.fld_list varchar(8000), @.loop int, @.i int, @.select varchar(2000), @.val_list varchar(8000)

............... YOUR CODE HERE ................

SET @.i=(SELECT COUNT(*)
FROM information_schema.columns
WHERE TABLE_NAME='ut_RptQryProdTemp')

SET @.loop = 1
SET @.fld_list = ''
SET @.val_list = ''
WHILE (@.loop <= @.i)
BEGIN
SELECT @.select = 'SELECT ''['' + COLUMN_NAME + ' + '''],''' + ' AS F
INTO ##fld_list
FROM information_schema.columns
WHERE TABLE_NAME=''ut_RptQryProdTemp'' AND ORDINAL_POSITION=' + convert(varchar(3),@.loop)
EXEC (@.select)
SELECT @.tmp = (SELECT REPLACE(F, ',', '') + ' AS ' + REPLACE(F, 'PRODOUT],', '],') + '' FROM ##fld_list)
SELECT @.val_list = @.val_list + @.tmp
SELECT @.fld_list = @.fld_list + REPLACE(STUFF(@.tmp, 1, 0, 'CAST('), ' AS [', ' AS varchar) AS [')
DROP TABLE ##fld_list
SET @.loop = @.loop + 1
END
SET @.fld_list = LEFT(@.fld_list, LEN(@.fld_list) - 1)
SET @.val_list = LEFT(@.val_list, LEN(@.val_list) - 1)
SET @.val_list = REPLACE(@.val_list, ',[', ',''')
SET @.val_list = REPLACE(@.val_list, '] AS', ''' AS')
SET @.val_list = REPLACE(@.val_list, 'PRODOUT', '')
SET @.val_list = STUFF(@.val_list, 1, 1, '''')

EXEC ('SELECT ' + @.val_list + ' UNION ALL SELECT ' + @.fld_list + ' FROM ut_RptQryProdTemp ORDER BY _DEP, _FAM, _STYLE, _COLOR, [_SIZE]')

As I said this is for a very specific situation, but if you're good with sql syntax you should be able to modify it.

good luck!|||Never [played with it, but my guess is you could use com automation..good luck

Any docs on COM AUTOMATION please ?|||Hello, everyone:

Thanks for all replies.

UNION cannot be used in bcp statement as SELECT part. I tried and got error. Pootle got a great idea. I did it and worked. Creat a view with all varchar column. Insert column names as first row. Then insert data from table. bcp to txt from view. This query will run automatically. The txt file name is changed by date. No problem on file name in bcp.

ZYT