Passing log on info to sub reports

I am trying to access a Crystal report from ASP. This report has a linked sub report. I am able to view the main report from the ASP page. but when i access the sub report i get the error--> Server has not been started. which means that my sub report is not getting the database log on info. i tried passin the info with the code available on net. but the first line itself fails saying Object does not suppot this method. My code is

set crSections=session("oRpt").sections
'if i print the error message here it says object does not support this method.

For Each crSection In crSections
crReportObjects = crSection.ReportObjects

'loop through all the report objects to find all the subreports
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then

'you will need to typecast the reportobject to a subreport()
'object once you find it

crSubreportObject = CType(crReportObject, SubreportObject)
'open the subreport object
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'set the database and tables objects to work with the subreport()
crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables
'loop through all the tables in the subreport and
'set up the connection info and apply it to the tables
For Each crTable In crTables
crTable.SetLogOnInfo "test" , "","test","test"
if crTable.TestConnectivity then
Response.Write "Connecting to sub" ' I am getting this message

end if

Next

End If
Next
Next

please point out what i am doin wrong. Greatly appreciate the help!The culprit is
crSubreportObject = CType(crReportObject, SubreportObject)
i commented this line and it works! :)

Passing 'date' parameter to Jet 4.0 linked server - collation problem?

Hello,
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.

Passing Date as aparameter to linked report from Parent report and then Dates format is Au

Hi
I need one urgent help.
When Passing Date as aparameter to linked report from Parent report,
the format of date is Automatically changing from dd/mm/yy to mm/dd/
yy. How to restrict it?
What is the solution for it in SP or rdl?
TIA.
PuneetPuneet,
Maybe you can also pass a parameter with the desired format, like "dd/mm/yy"
(this can be kept as a parameter in your application or database, so you can
change it dynamically).
Then, in the subreport, use formatting to display the date using the desired
format.
Andrei.
"appu" <ajmera.puneet@.gmail.com> wrote in message
news:1192120804.765315.73850@.o80g2000hse.googlegroups.com...
> Hi
> I need one urgent help.
> When Passing Date as aparameter to linked report from Parent report,
> the format of date is Automatically changing from dd/mm/yy to mm/dd/
> yy. How to restrict it?
> What is the solution for it in SP or rdl?
> TIA.
> Puneet
>

Passing a variable to a Linked Query (OPENROWSET for Excel Syntax)

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)

but I have not gotten all the ''''' + @.variable syntax right.

Here is my raw openrowset with what I am aiming at.

Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game? Many thank-yous, in advance.

Kind Regards,

Claudia.

You can make use of the QUOTENAME function to help you out here.

I couldn't get the MSDASQL Excel driver to work on my desktop, but below is an example that uses the same principles but with the Jet Excel driver. Simply modify the values of the provider, connection string, filename and query variables as appropriate.

Chris

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.Provider NVARCHAR(100)

DECLARE @.FIL NVARCHAR(256)

DECLARE @.ConnectionString NVARCHAR(1000)

DECLARE @.Query NVARCHAR(1000)

SET @.Provider = N'Microsoft.Jet.OLEDB.4.0'

SET @.FIL = N'C:\Company Folders\Documentation\INVENTORY.xls'

SET @.ConnectionString = N'Excel 8.0;DATABASE=' + @.FIL

SET @.Query = N'SELECT * FROM [Inventory$]'

SET @.SQL = N'SELECT *

FROM OPENROWSET(' + QUOTENAME(@.Provider, N'''') + N', '

+ QUOTENAME(@.ConnectionString, N'''') + N', '

+ QUOTENAME(@.Query, N'''') + N')'

EXEC sp_executesql @.SQL

Pass a variable to SET IDENTITY_INSERT

I'm quite new to programming with SQL Server.
I'm developing an SQL statement using Query Analyzer that read a table from a MSAccess Linked server database, and put the data in to a SQL server table.

I'd like that table name, db name, etc, are values stored in some variables, so, when i want to import another table, i need to change only the value of the @.table_name variable.

I can pass the value of this variable to all the statements of my script but one. I'm able to execute something like

EXEC ('select * from' + @.table_name + '... bla bla bla')

but i'm not able to execute the SET IDENTITY_INSERT @.table_name ON/OFF.
I tried many options like print, exec, quotes, double quotes, etc, but nothing to do.

I'd really appreciate all your helping answers.

Ciao

Darioif you wish to do this you will need to build a string containing the set statement and the insert statment. then EXEC(@.string).

Remember that EXEC() runs on a seperate thread than your current connection so any settings will be lost once the EXEC finishes.

declare @.TSQL varchar(1000)
CREATE TABLE #products (id int IDENTITY PRIMARY KEY, product varchar(40))

-- Inserting values into products table.
INSERT INTO #products (product) VALUES ('screwdriver')
INSERT INTO #products (product) VALUES ('hammer')

select * From #Products

-- Inserting ids and values into products table.
set @.TSQL = 'set identity_insert #products on
INSERT INTO #products (id,product) VALUES (10,''saw'')
INSERT INTO #products (id,product) VALUES (11,''shovel'')'

exec(@.TSQL)

select * from #products

drop table #Products|||The script is the following:

-- Variable declaration
DECLARE @.nome_db nvarchar(50)
DECLARE @.nome_tabella nvarchar(50)
DECLARE @.id_tabella int
DECLARE @.str_nome_campi nvarchar(255)
DECLARE @.str_tmp_nome_campo nvarchar(255)
DECLARE @.strdata nvarchar(255)
DECLARE @.str_sql nvarchar(255)
DECLARE @.str_sql_delete nvarchar(255)
DECLARE @.str_sql_use nvarchar(255)
DECLARE @.str_sql_debug nvarchar(255)
DECLARE @.str_id_insert_on nvarchar(50)
DECLARE @.str_id_insert_off nvarchar(50)

-- Set the variables
SET @.nome_db = 'Unitec_Script_Importazione'
SET @.nome_tabella = 'ANFRAGEDETAILS2'

SET @.str_sql_delete = 'DELETE FROM [' + @.nome_db + '].[dbo].[' + @.nome_tabella + ']'
SET @.str_sql_use ='USE [' + @.nome_db + ']'
SET @.str_sql_debug = 'SELECT TOP 10 * FROM ' + @.nome_tabella

-- Stringhe per settare Identity ... maledette!
SET @.str_id_insert_on = ' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' ON' + char(10)
SET @.str_id_insert_off =' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' OFF' + char(10)
-- Trovo, all'interno della tabella di sistema sysobjects, l'ID
-- della tabella. Usando questo valore posso trovare i campi relativi
SET @.id_tabella = (SELECT [id] FROM sysobjects WHERE [name] LIKE @.nome_tabella)
--SELECT @.id_tabella -- (debug - stampa il risultato della query)

DECLARE nome_campi CURSOR
FOR SELECT [name] FROM syscolumns where id = @.id_tabella
-- Open the cursor
OPEN nome_campi
-- Prendo la prima riga del cursore e la schiaffo dentro alla variabile temporanea
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo

SET CONCAT_NULL_YIELDS_NULL OFF

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str_nome_campi = @.str_tmp_nome_campo
SET @.strdata = @.str_nome_campi + @.strdata
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo
-- Aggiungo la virgola se necessario
SET @.str_tmp_nome_campo = @.str_tmp_nome_campo + ', '
END

SELECT @.strdata -- Debug: stampa il contenuto di strdata

CLOSE nome_campi
DEALLOCATE nome_campi

EXECUTE (@.str_sql_use)

EXECUTE sp_executesql @.str_sql_delete

/******* THIS IS WHERE I NEED HELP*********
Instead of the name of the table (ANFRAGEDETAILS2)
i want to use a variable @.table_name
*****************************************/
SET IDENTITY_INSERT ANFRAGEDETAILS2 ON

SET @.str_sql = @.str_sql + 'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')'

EXECUTE (@.str_sql)

SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF

--
--
--|||have you tried:

SET @.str_sql = @.str_sql + 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON
INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')
SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF'
EXECUTE (@.str_sql)|||Yes, 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON ... works
but 'SET IDENTITY_INSERT @.table_name ON ... Doesn't work|||AND
SET @.str_sql = @.str_sql
+ ' SET IDENTITY_INSERT [dbo].['+@.nome_tabella+'] ON '
+'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select '' + @.strdata + '' from ['' + @.nome_tabella + '']'')'
EXECUTE (@.str_sql)
WORKS !?|||Yes, it works!!!

Many thanks

Dario

Pass a variable to a linked server (FoxPro) query

I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.

Here is the code:
------------
DECLARE @.LastDate datetime
SELECT @.LastDate = MAX(DateChaged)
FROM tblPersonel
WHERE ACTIVE IN( 'A', 'T')

1. I tried:
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@.LastDate+''')

This line gives me an error msg:

Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]

2. I tried to use CTOD() - FOXPRO function to convert character to date.

SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@.LastDate+'')')

-this doesn't give any error, but doesn't limit the result set as it should.

Thanks all.I am surprised that #2 works, because OPENQUERY does not work with parameter parsing or dynamic SQL tricks that SQL lets you get away with.|||I read an article on MSDN, describing how to pass a variable to linked server query...at the same time I read elsewhere that you can not pass parametrs to OpenQuery...
Does anybody have any suggestions?
Thanks all.