DPE and Report Models

To my horror I have discovered that you can not specify a provdier that
is anything other than SQL Data Provider in a report model project.
Here's my problem. I'd like to provide one set of models for my schema
and be able to dynamically change the connection string using a DPE
that we have developed.
This way I publish the models and the datasource and we can connect to
different DB's at runtime.
If this can be done in a standard report project why can't it be done
in a report model project?
If this is "impossible" are there any plans to support custome DPE's
with report models in the next service pack / version.
Help, at the moment we have to maintain and publish a set of models for
every client we have that has their own database and majority of the
schema's are identical.
Regards
Toby.Ok found some more providers, but no DPE.
Toby|||Tobi wrote:
> Ok found some more providers, but no DPE.
> Toby
Anybody for any Ideas? Anybody There? Help?sql

Download and create a Report Project from a Report Server

Before I start coding ...

Is there any existing software/trick/hack to create a Report Project from the DataSources and Folders/Reports on a Report Server, i.e. the inverse of Deployment?

Would there be anybody interested in such a thing, and, if not, why not?

I don't think there's any way to create a report project from the report server, but you can get the actual RDL files from the server.

Go to your Report Manager. (http://ServerName/Reports)
Drill through to the report you want the definition of.
Click on the properties tab.
Click on General from the options on the left.
Under the 'Report Definition' section, click 'Edit'.
This will prompt you to download the RDL file.
You can then add this to your report project that you already have created.

Hope this helps.

Jarret

Double Byte Characters scrambled in PDF Output

Does anybody know how I can Clean up some European Characters such as "?"
in a text field:
"45000 m² new office project designed by Arquitectonica. Revised proposal
and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
Synthese as option. Ten competitors, including Coteba. Contract award
expected by 15 Sept 04."
Is there a certain data type to use or is there a conversion fucntion to
use?
HelpWhat you can do in this case is :
write custom code for all the clean up.
You can write code tab of the Report Properties in the Report tab under main menu.
Write your own VB.NET code.
>--Original Message--
>Does anybody know how I can Clean up some European Characters such as "?"
>in a text field:
>"45000 m=B2 new office project designed by Arquitectonica. Revised proposal
>and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
>Synthese as option. Ten competitors, including Coteba. Contract award
>expected by 15 Sept 04."
>Is there a certain data type to use or is there a conversion fucntion to
>use?
>Help
>
>.
>

Don't want to use cursors but no way around it

During my long SQL-carreer I've managed to steer clear of Cursors. But now I have a project at hand where I do not see a feasable way to NOT use a cursor.

From a pool of available numbers (IMSIs) I need to allocate new ones and add them to a batch table. In total there are three tables affected by the following SP. A batch table storing info on the batch, a table that contains the newly allocated IMSIs and a table holding all IMSIs with a state field to indicate that they are free or not. With in the WHILE loop I need to set the state from 'F'(ree) to 'A'llocated and I have no clue how to update the ImsiCursor. So two questions:
Can this be done without a cursor?
How to update the State attribute of the current cursor location?

ALTER PROCEDURE dbo.spCreateBatch
(
@.BatchID AS int
, @.BatchSize AS int
, @.ImsiCount as smallint
, @.PurchaseOrder AS varchar(12)
)
AS
DECLARE @.counter5 AS char(5)
DECLARE @.FreeIMSIs AS BIGINT
SELECT @.FreeIMSIs = COUNT(Imsi) FROM tblImsiPool WHERE ImsiRangeID=1 AND STATE='F'
IF @.FreeIMSIs < @.BatchSize
BEGIN
RAISERROR ('Not enough free IMSIs in range', 16, 1)
RETURN
END

DECLARE ImsiCursor CURSOR LOCAL FORWARD_ONLY FOR SELECT p.Imsi, p.State FROM tblImsiPool p WHERE p.ImsiRangeID=1 AND p.State='F'

SET NOCOUNT ON
BEGIN TRANSACTION CreateBatch
DECLARE @.BatchID5 AS CHAR(5)
SET @.BatchID5 = RTRIM(LTRIM(@.BatchID))
SET @.BatchID5 = REPLICATE('0', 5 - LEN(@.BatchID5)) + RTRIM(@.BatchID5)
INSERT INTO
tblBatch (BatchID, Vendor, Creation, Required, BatchSize, ImsiCount, PurchaseOrder, PIN1Default, PIN2Default, MBPINDefault, State)
VALUES (@.BatchID5, 'GA', DEFAULT, DATEADD(m, 1, GETDATE()), @.BatchSize, @.ImsiCount, @.PurchaseOrder, 'RANDOM', 'RANDOM', 'RANDOM','C')
DECLARE @.counter AS int
DECLARE @.IccIDBase AS VARCHAR(19)
DECLARE @.IccID AS VARCHAR(20)
DECLARE @.Imsi AS NUMERIC(15)
DECLARE @.state AS CHAR(1)
SELECT @.Imsi = MAX(DefaultImsi) FROM tblSim

OPEN ImsiCursor
FETCH NEXT FROM ImsiCursor INTO @.Imsi, @.State

SET @.counter = 0
WHILE( @.counter <= @.BatchSize )
BEGIN
SET @.counter5 = @.counter
SET @.counter5 = REPLICATE('0', 5 - LEN(RTRIM(@.counter5))) + RTRIM(@.counter5)
SET @.IccIDBase = '89234507' + @.BatchID5 + @.counter5
SET @.IccID = RTRIM(@.IccIDBase) + CONVERT( CHAR(1), dbo.luhn( @.IccIDBase ) )
IF LEN(@.IccID) < 20
SET @.IccID = @.IccID + 'F'

INSERT INTO tblSIM(BatchID, Vendor, ICCID, Pin, Puk, Pin2, Puk2, DefaultImsi)
VALUES( @.BatchID5, 'GA', @.IccID, '', '', '', '', @.Imsi)

UPDATE ImsiCursor SET State = 'A' -- does not work
FETCH NEXT FROM ImsiCursor INTO @.Imsi, @.State

SET @.counter = @.counter + 1
END

CLOSE ImsiCursor
DEALLOCATE ImsiCursor

IF @.@.ERROR = 0
COMMIT TRANSACTION CreateBatch
ELSE
ROLLBACK TRANSACTION CreateBatch

RETURN

From what you have said, it seems reasonable that you could do this without cursors, buit it might be tricky.

You would just do an update to set the number of rows you want to allocate to not allocated (then in 2005, you can use the OUTPUT clause to get the rows you changed, or you can lock the rows with a SELECT into your temp table and and XLOCK in a transaction).

Now it looks like this code:

SET @.counter5 = @.counter
SET @.counter5 = REPLICATE('0', 5 - LEN(RTRIM(@.counter5))) + RTRIM(@.counter5)
SET @.IccIDBase = '89234507' + @.BatchID5 + @.counter5
SET @.IccID = RTRIM(@.IccIDBase) + CONVERT( CHAR(1), dbo.luhn( @.IccIDBase ) )
IF LEN(@.IccID) < 20
SET @.IccID = @.IccID + 'F'

INSERT INTO tblSIM(BatchID, Vendor, ICCID, Pin, Puk, Pin2, Puk2, DefaultImsi)
VALUES( @.BatchID5, 'GA', @.IccID, '', '', '', '', @.Imsi)

Is the meat of the query. You could probably translated the variable to calculations in a SELECT query.

It is all too messy to work with for the uneducated. Please post some sample data and minimal tables that just cover the problems you are having and it will be easier to demonstrate.

Dont know where to put a subfunction/query into my SQL Script

Hi,

I'm kind of database administrator in our company and have to manage a project database, where people add their work times to a SQL database (Access frontend). Like time they work on a day, on a specific project, vacances etc.).

I made a script which reports me:
- for each person, how long did they work on a project (Auftrag_ID) on a single day
- limited by a period (Monat_von to Monat_bis means month_from to month_to)

The script was fine, but I now had also to output a comment for every week for each person and then for each of the persons Auftrag_IDs (projects) so I inner joined the Table "WO-Zeiterfassung" where this information is stored. But I have to found out for each date, which is the date of the sunday in that week to Innerjoin it on the sunday date (I'm not sure if there is an easier way)... so for the moment I made a "subscript", function whatever you call that, to give me the date of the sunday for each day, which I marked in the code below. But I have no idea where I must put that part into the query AND if it is correct :-) the same goes with the inner join part of the query (also marked with commentary code):

DECLARE @.Auftrag_ID int
DECLARE @.Jahr int
DECLARE @.Monat_von int
DECLARE @.Monat_bis int

DECLARE @.Wochentag int
DECLARE @.DifferenzTage int
DECLARE @.WoSonntagsDatum datetime

SELECT @.Auftrag_ID = '3216'
SELECT @.Jahr = '2006'
SELECT @.Monat_von = '1'
SELECT @.Monat_bis = '4'

SET DATEFIRST 1

/* WHERE TO PUT THE FOLLOWING PART, and is it correct? */
(
SET @.Wochentag = (SELECT Datepart(dw, TKMA.Datum))
SET @.DifferenzTage = ( 7-@.Wochentag )
SET @.WoSonntagsDatum = (SELECT DateAdd(d, @.DifferenzTage, TKMA.Datum))
)
/* ende */

SELECT
Personal.[Personal-ID]
,Personal.[Name] + ' ' + Personal.[Vorname] AS NameVorname
,KstStell.[KostenstellenNo] AS KstSt
,KstStell.[Kostenstellenname] AS KstName
,TKMA.[Auftrag-ID]
,TKMA.[Auftrag-Kurzbezeichnung]
,KstStell.[Kostenstellenverant]
,Personal2.[Name] + ' ' + Personal2.[Vorname] AS ProjVerantwortlicher
,TKMA.Datum
,MONTH(TKMA.Datum) AS Monat
,TKMA.Stunden
,TKMA.ProjektNrIntern AS ProjNrIntern
,TKMA.ProjektNr AS ProjNr
,TKMA.Projektkurzbezeichnung AS ProjBezeichnung
,TKMA.Erf_abgeschlossenJN AS abgeschlossen
,TKMA.KJahr AS Jahr
FROM
Tageskalender_MA_Details TKMA
INNER JOIN
[Personal_maXis] Personal ON Personal.[Personal-ID] = TKMA.[Personal-ID]
INNER JOIN
[Kostenstellen maXis] KstStell ON Personal.[KSTNR-ID] = KstStell.[KSTNR-ID]
INNER JOIN
[Projekte] Prj ON TKMA.[ProjektNrIntern] = Prj.[ProjektNrIntern]
/* AND HERE THE INNERJOIN RELATED PART I ADDED FOR THIS NEW STUFF */
INNER JOIN
[WO-Zeiterfassung] WOZE ON @.WoSonntagsDatum = WOZE.[So-Datum] AND Personal.[Personal-ID] ON WOZE.[Personal-ID] AND TKMA.[Auftrag-ID] ON WOZE.[Auftrag-ID]
/* TILL HERE */
LEFT OUTER JOIN
[Personal_maXis] Personal2 ON Personal2.[Personal-ID] = Prj.[Personal-ID]
WHERE
(TKMA.[Auftrag-ID] = @.Auftrag_ID)
AND
(TKMA.KJahr = @.Jahr)
AND
(MONTH(TKMA.Datum) >= @.Monat_von)
AND
(MONTH(TKMA.Datum) <= @.Monat_bis)
AND
(TKMA.Erf_abgeschlossenJN = '-1')

Here is the error it outputs in query analyzer:
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'SET'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 53
Incorrect syntax near the keyword 'ON'.

I've no clue what I make wrong :-\

Hope someone can help me,

ShihanI think SQL Server is expecting a "From Clause" in the following:

SET @.Wochentag = (SELECT Datepart(dw, TKMA.Datum))

Domino VBA

Hi,
I am trying to read data from a domino databases (located in our LAN
not on my local machine!) from my access project. I pass a code to the
function checkCodeInDomino to be verified on Domino. On my local
machine everything went fine, but suddenly there came up following
problem:

I was coding and testing with the code below, and I have never been
asked for my lotus password, now when starting my function, there comes
a box up (dont know why this changed?) where I have to enter my lotus
password, when entering my notes password, my function works fine.
But I have to distribute my application to other clients and users, and
it doesnt make sense to use my password.
So I received a public account from our domino admin, for all of us.
When using "DomSession.Initialize (publicLotusPassword)" there are no
errors.
But the problem is, that that's not a real solution for my
appliacation, because each user has to enter his own password, I dont
want that,
but entering a hard coded password doesnt work, because the password
that is asked is referring to
"c:\lotus\notes\data\wk\$userName.ID"

Is there any way to use "DomSession.Initialize" in some kind of a
"generic" way, so that any other user can use the function
"checkCodeInDomino"?
Please remember that the domino database is always on a remote machine
and
"'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
does not work!

Here is my code...

Function checkCodeInDomino(checkCode As Long)
On Error GoTo Err_Quit_Click

Dim DomDir As NotesDatabase
Dim DomContacts As NotesView
Dim DomDoc As NotesDocument
Dim StrName As String

Dim DomSession As NotesSession
Dim serverName As String
Dim databaseFile As String

Set DomSession = CreateObject("Lotus.NotesSession")
serverName = "myServerName"
databaseFile = "myFile"

DomSession.Initialize

Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
Set DomContacts = DomDir.GetView("myView")
Set DomDoc = DomContacts.GetFirstDocument

While Not (DomDoc Is Nothing)

If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
checkCodeInDomino = True
Exit Function
End If
Set DomDoc = DomContacts.GetNextDocument(DomDoc)
Wend

checkCodeInDomino = False

Exit_Quit_Click:
Exit Function

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Function

Thank you for your attention!

Peter NeumaierI think you'll have more luck asking in a newsgroup related to Notes or
Domino, as opposed to a database group.

Have you tried looking for documentation at http://www.lotus.com?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
> Hi,
> I am trying to read data from a domino databases (located in our LAN
> not on my local machine!) from my access project. I pass a code to the
> function checkCodeInDomino to be verified on Domino. On my local
> machine everything went fine, but suddenly there came up following
> problem:
> I was coding and testing with the code below, and I have never been
> asked for my lotus password, now when starting my function, there comes
> a box up (dont know why this changed?) where I have to enter my lotus
> password, when entering my notes password, my function works fine.
> But I have to distribute my application to other clients and users, and
> it doesnt make sense to use my password.
> So I received a public account from our domino admin, for all of us.
> When using "DomSession.Initialize (publicLotusPassword)" there are no
> errors.
> But the problem is, that that's not a real solution for my
> appliacation, because each user has to enter his own password, I dont
> want that,
> but entering a hard coded password doesnt work, because the password
> that is asked is referring to
> "c:\lotus\notes\data\wk\$userName.ID"
> Is there any way to use "DomSession.Initialize" in some kind of a
> "generic" way, so that any other user can use the function
> "checkCodeInDomino"?
> Please remember that the domino database is always on a remote machine
> and
> "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
> does not work!
> Here is my code...
> Function checkCodeInDomino(checkCode As Long)
> On Error GoTo Err_Quit_Click
> Dim DomDir As NotesDatabase
> Dim DomContacts As NotesView
> Dim DomDoc As NotesDocument
> Dim StrName As String
> Dim DomSession As NotesSession
> Dim serverName As String
> Dim databaseFile As String
> Set DomSession = CreateObject("Lotus.NotesSession")
> serverName = "myServerName"
> databaseFile = "myFile"
> DomSession.Initialize
> Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
> Set DomContacts = DomDir.GetView("myView")
> Set DomDoc = DomContacts.GetFirstDocument
> While Not (DomDoc Is Nothing)
> If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
> checkCodeInDomino = True
> Exit Function
> End If
> Set DomDoc = DomContacts.GetNextDocument(DomDoc)
> Wend
> checkCodeInDomino = False
> Exit_Quit_Click:
> Exit Function
> Err_Quit_Click:
> MsgBox Err.Description
> Resume Exit_Quit_Click
> End Function
> Thank you for your attention!
> Peter Neumaier|||Hi Douglas,

thank you for your answer, but I think that my problem is more VBA and
ACCES than it is to domino databases.

So the only problem I have to solve is, how to use a default password
when accessing the domino database ...

For sure I found documentation on lotus.com, but there isnt anything
regarding my problem

"Douglas J. Steele" <NOSPAM_djsteele@.NOSPAM_canada.com> wrote in message news:<0cCdnUdLW-09ddDfRVn-vA@.rogers.com>...
> I think you'll have more luck asking in a newsgroup related to Notes or
> Domino, as opposed to a database group.
> Have you tried looking for documentation at http://www.lotus.com?
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
> "Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
> news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
> > Hi,
> > I am trying to read data from a domino databases (located in our LAN
> > not on my local machine!) from my access project. I pass a code to the
> > function checkCodeInDomino to be verified on Domino. On my local
> > machine everything went fine, but suddenly there came up following
> > problem:
> > I was coding and testing with the code below, and I have never been
> > asked for my lotus password, now when starting my function, there comes
> > a box up (dont know why this changed?) where I have to enter my lotus
> > password, when entering my notes password, my function works fine.
> > But I have to distribute my application to other clients and users, and
> > it doesnt make sense to use my password.
> > So I received a public account from our domino admin, for all of us.
> > When using "DomSession.Initialize (publicLotusPassword)" there are no
> > errors.
> > But the problem is, that that's not a real solution for my
> > appliacation, because each user has to enter his own password, I dont
> > want that,
> > but entering a hard coded password doesnt work, because the password
> > that is asked is referring to
> > "c:\lotus\notes\data\wk\$userName.ID"
> > Is there any way to use "DomSession.Initialize" in some kind of a
> > "generic" way, so that any other user can use the function
> > "checkCodeInDomino"?
> > Please remember that the domino database is always on a remote machine
> > and
> > "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
> > does not work!
> > Here is my code...
> > Function checkCodeInDomino(checkCode As Long)
> > On Error GoTo Err_Quit_Click
> > Dim DomDir As NotesDatabase
> > Dim DomContacts As NotesView
> > Dim DomDoc As NotesDocument
> > Dim StrName As String
> > Dim DomSession As NotesSession
> > Dim serverName As String
> > Dim databaseFile As String
> > Set DomSession = CreateObject("Lotus.NotesSession")
> > serverName = "myServerName"
> > databaseFile = "myFile"
> > DomSession.Initialize
> > Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
> > Set DomContacts = DomDir.GetView("myView")
> > Set DomDoc = DomContacts.GetFirstDocument
> > While Not (DomDoc Is Nothing)
> > If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
> > checkCodeInDomino = True
> > Exit Function
> > End If
> > Set DomDoc = DomContacts.GetNextDocument(DomDoc)
> > Wend
> > checkCodeInDomino = False
> > Exit_Quit_Click:
> > Exit Function
> > Err_Quit_Click:
> > MsgBox Err.Description
> > Resume Exit_Quit_Click
> > End Function
> > Thank you for your attention!
> > Peter Neumaier|||While I recognize that you're experiencing the problem using automation from
VBA, you're still issuing commands to directly to Domino. I would think that
people more familiar with the Domino programming model would be better able
to help than people familiar with VBA.

YMMV, though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:98284637.0504020824.4b7a20c9@.posting.google.c om...
> Hi Douglas,
> thank you for your answer, but I think that my problem is more VBA and
> ACCES than it is to domino databases.
> So the only problem I have to solve is, how to use a default password
> when accessing the domino database ...
> For sure I found documentation on lotus.com, but there isnt anything
> regarding my problem
>
> "Douglas J. Steele" <NOSPAM_djsteele@.NOSPAM_canada.com> wrote in message
> news:<0cCdnUdLW-09ddDfRVn-vA@.rogers.com>...
>> I think you'll have more luck asking in a newsgroup related to Notes or
>> Domino, as opposed to a database group.
>>
>> Have you tried looking for documentation at http://www.lotus.com?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
>> news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
>> > Hi,
>> > I am trying to read data from a domino databases (located in our LAN
>> > not on my local machine!) from my access project. I pass a code to the
>> > function checkCodeInDomino to be verified on Domino. On my local
>> > machine everything went fine, but suddenly there came up following
>> > problem:
>>> > I was coding and testing with the code below, and I have never been
>> > asked for my lotus password, now when starting my function, there comes
>> > a box up (dont know why this changed?) where I have to enter my lotus
>> > password, when entering my notes password, my function works fine.
>> > But I have to distribute my application to other clients and users, and
>> > it doesnt make sense to use my password.
>> > So I received a public account from our domino admin, for all of us.
>> > When using "DomSession.Initialize (publicLotusPassword)" there are no
>> > errors.
>> > But the problem is, that that's not a real solution for my
>> > appliacation, because each user has to enter his own password, I dont
>> > want that,
>> > but entering a hard coded password doesnt work, because the password
>> > that is asked is referring to
>> > "c:\lotus\notes\data\wk\$userName.ID"
>>> > Is there any way to use "DomSession.Initialize" in some kind of a
>> > "generic" way, so that any other user can use the function
>> > "checkCodeInDomino"?
>> > Please remember that the domino database is always on a remote machine
>> > and
>> > "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
>> > does not work!
>>> > Here is my code...
>>> > Function checkCodeInDomino(checkCode As Long)
>> > On Error GoTo Err_Quit_Click
>>> > Dim DomDir As NotesDatabase
>> > Dim DomContacts As NotesView
>> > Dim DomDoc As NotesDocument
>> > Dim StrName As String
>>> > Dim DomSession As NotesSession
>> > Dim serverName As String
>> > Dim databaseFile As String
>>> > Set DomSession = CreateObject("Lotus.NotesSession")
>> > serverName = "myServerName"
>> > databaseFile = "myFile"
>>> > DomSession.Initialize
>>> > Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
>> > Set DomContacts = DomDir.GetView("myView")
>> > Set DomDoc = DomContacts.GetFirstDocument
>>> > While Not (DomDoc Is Nothing)
>>> > If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
>> > checkCodeInDomino = True
>> > Exit Function
>> > End If
>> > Set DomDoc = DomContacts.GetNextDocument(DomDoc)
>> > Wend
>>> > checkCodeInDomino = False
>>> > Exit_Quit_Click:
>> > Exit Function
>>> > Err_Quit_Click:
>> > MsgBox Err.Description
>> > Resume Exit_Quit_Click
>>> > End Function
>>> > Thank you for your attention!
>>> > Peter Neumaier
>