Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need help converting date format.

Hi and thanx for reading my post..

I have a reg_date field in my MSSQL DB which is formatted like this :

dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

Have tried different variations of : convert(char(8) but not sure how i do this really..

Have already searched the net for a solution but had to post it since i didn't find anything useful..

Hope someone can help me out

Best regards
Mirador-/select ((CONVERT(CHAR(6),getdate(),104)) + RIGHT(CONVERT(CHAR(10),getdate(),104),2))|||select (CONVERT(CHAR(10),getdate(),4))

using 104 adds the century to the year, which then has to be eliminated in the first response. Just using 4 returns only the last two digits of the year. Be carefule though, as this type of date format will be a problem in your code in 96 years ;-)|||Sorry, make that CHAR(8).|||Hi and thanx for the quick reply !

Have tried the code but still got trouble with this errormsg i get :

--> Incorrect syntax near '.03.'

This is the code :

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) LIKE " & strTheDate.

ps!.. (strTheDate is asp variable..)

I have debugged the strTheDate and i know it's the correct format : 24.12.03 ex.

ps!.. the field is a DateTime format, just to ensure that :)

Best reagards
Mirador|||Can;t say I think this is the best way of doing this, but I haven't had enough coffee to do a re-write (and blindman will undercut me anyway).

Try this (note the single quotes!):

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) = CONVERT(char(8), CAST('" & strTheDate & "' AS DATETIME),4)

This will convert strTheDate to the same format.

Need help adding 3rd table to a Join

Hi can someone help me I'm brainless, I need to add a field from a third database to this view

CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

The field I need to add is COLLECTION, with Left Outer Join

mCollection
ITEM_ID
COLLECTIONAnd COLLECTION is a member of which table?|||I need the COLLECTION field from the mCollection Table to be included in the mProduct_Search view|||Nevermind my brain came back to life, I have answered my own question...
CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH], B.COLLECTIONS as [COLLECTIONS],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

Left Outer Join (Select * from mCollections) B

on mProduct.ITEM_ID = B.ITEM_ID

Need good idea

Hi guys

We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.

Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.

Obviously such approach is very ineffective, the alternative is to
create trigger on update event and then execute stored procedure which
will re-calculate and update the hash value. The problem with this
approach is that end user could then change the date in the tables and
then run this store procedure to adjust hash value.

We are looking for some solution that could speed up the hash value
updating without allowing authorized user to do it

Thanks in advance,
LeonVlad Olevsky wrote:
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
> Thanks in advance,
> Leon
In DB2 for LUW you can define the column as a generated column.
I presume you have some sort of UDF already that does the actually
hashing. Last I heard SS 2005 will have persistent generated columns as
well.
In general (x-product) you can use a combination of a check constraint
and (before) triggers.

One must but wonder WHY this column is required. Are you affraid of
corruption or sabotage?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||"Vlad Olevsky" <leonid4142@.yahoo.com> schrieb im Newsbeitrag news:50540181.0505030658.64f68390@.posting.google.c om...
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
As Frank pointed out, try to create a trigger which calls a function.
Let the function run with the grants of the caller and give only
authorized callers the exec grant of the function.

Greetings!
Volker|||Vlad Olevsky wrote:

> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on update event and then execute stored procedure which
> will re-calculate and update the hash value. The problem with this
> approach is that end user could then change the date in the tables and
> then run this store procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing authorized user to do it
> Thanks in advance,
> Leon

This may come as a shock to you Leon but the solution in each of the
products whose usenet group you copied on this uses a completely
different solution.

I'd suggest you start by apologizing, to all, for your lack of
identifying the product and version and for posting to every usenet
group you can spell.

And then repost in the one, and only, group where your query is
appropriate.

Thank you.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Serge Rielau (srielau@.ca.ibm.com) writes:
> In DB2 for LUW you can define the column as a generated column.
> I presume you have some sort of UDF already that does the actually
> hashing. Last I heard SS 2005 will have persistent generated columns as
> well.

Actually, SQL 2000 has it as well. The difference is that PERSISTED is
a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
a computed colum, without indexing it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Serge Rielau (srielau@.ca.ibm.com) writes:
>>In DB2 for LUW you can define the column as a generated column.
>>I presume you have some sort of UDF already that does the actually
>>hashing. Last I heard SS 2005 will have persistent generated columns as
>>well.
>
> Actually, SQL 2000 has it as well. The difference is that PERSISTED is
> a keyword in SQL 2005, and, I assume, that in SQL 2005 you can persist
> a computed colum, without indexing it.
Yes, in SS2000 the generated column is virtual (i.e. not persisted). The
planned syntax in the standard is "GENERATED BY REFERENCE", being the
default for compatibility with SS2000.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||Serge Rielau (srielau@.ca.ibm.com) writes:
> Yes, in SS2000 the generated column is virtual (i.e. not persisted).

Unless, as I said, it is indexed, in which case it is implicitly persisted.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need Data in Title Case

Hey Friends...

In database I have description field in Uppar case but I want to display it in Title Case

Like :

In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE

But in report I want like : Take Back Control Of Your Change

and I am using crystal report 8.5 so can anybody help me please

Thanks

-Jayesh Mendpara
jmendpara@.gmail.comDoes 8.5 have the ProperCase function? If not I guess you'll have to code it yourself in a formula.|||No,

in Crystal report 8.5 ProperCase function doesn't support

Need call a DB function in the middle of the dataflow process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.

If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.

The data flow is Like:

SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB

Thanks in Advance

Jessie

HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:

EXEC ? = DB_Function(?)

Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.

HTH,
Ccote

|||

Hi, Ccote,

Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.

before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.

In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.

The OLE command does not let me to add any new column to the OLE command output.

I got error when I click on REFRESH, “Invalid parameter number ‘

Where I’m missing here?

Thanks

Jessie

|||

I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?

Thanks

Need an expression format to put decimals .00

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...

if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.

thank you very much for the help / information....

Hello,

You can do this a couple different ways...

1. Select your textbox that will hold Fields!SequenceNO.Value, then change the Format property to #.00

or

2. Replace Fields!SequenceNO.Value with the expression: =Format(Fields!SequenceNO.Value, "#.00")

Both of these will make all of your numbers in the column have 2 decimals.

Hope this helps.

Jarret

Need a little help

Is there a way to include either a checkbox or a yes/no field in SQL? Here is why I ask. I have a small db that I am creating that will house data about cars. I want to display two of these cars based off of a user selected field in SQL, or on a webform. There will be about a hundred or so cars that will be available, but I only want the ones that the user selects to show on my page. What is the best way to accomplish this?

DUUUUHHHHH.... how about adding a bit field to the db and a checkbox field to the Grid View!!

|||YesNo fields in Sql Server are bit datatypes - 1 or 0. The value can be read as 1 or 0, true or false.

Need a good idea

Hi guys
We have a following problem. For security reasons in each table in our
DB we have addition field which is calculated as hash value of all
columns in particular row.
Every time when some field in particular row is changed we create and
call select query from our application to obtain all fields for this
row and then re-calculate and update the hash value again.
Obviously such approach is very ineffective, the alternative is to
create trigger on updating event and then execute stored procedure
which will re-calculate and update the hash value. The problem with
this approach is that end user could then change the date in the
tables and then run this stored procedure to adjust hash value.
We are looking for some solution that could speed up the hash value
updating without allowing unauthorized user to do itHave you checked out CHECKSUM() in BOL?
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||If you want to enforce that values can only be changed through your
application, the best way to do that is make sure that only your application
has permissions to use certain stored procedures and tables. For this you
can use application roles.
Having a trigger on the table to calculate the hash value won't do anything
useful, because anyone who updates the table, will fire that trigger and the
hash value will updated correctly.
Jacco Schalkwijk
SQL Server MVP
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||How are you using this hash value? Is it supposed to provide some
user-authentication? Without understanding the application of this it's
difficult to recommend an alternative.
David Portas
SQL Server MVP
--|||If the Checksum() function is not sufficient, you might try a computed colum
n.
Thomas
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Jacco raised a very good point. If your intent is to store a hash value of
some sort to indicate that a row has not been tampered with by any means
outside of your application, you should probably generate the hash code and
insert it from the application side, not via a trigger or other mechanism
internal to the database that users would have access to via QA.
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||you could continue to use a trigger, but the trigger only does something if
called by your application, so if someone changed a row via query analyser
the trigger will not fire.. see below
create trigger mytrigger on mytable after update
as
begin
if app_name() = 'myapp'
begin
' do stuff here
end
end
go
"Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
news:50540181.0505030708.b3397b6@.posting.google.com...
> Hi guys
> We have a following problem. For security reasons in each table in our
> DB we have addition field which is calculated as hash value of all
> columns in particular row.
> Every time when some field in particular row is changed we create and
> call select query from our application to obtain all fields for this
> row and then re-calculate and update the hash value again.
> Obviously such approach is very ineffective, the alternative is to
> create trigger on updating event and then execute stored procedure
> which will re-calculate and update the hash value. The problem with
> this approach is that end user could then change the date in the
> tables and then run this stored procedure to adjust hash value.
> We are looking for some solution that could speed up the hash value
> updating without allowing unauthorized user to do it|||Thanks Mark!
The only one remark. To prevent end-user from looking and modifying the
trigger code we could create trigger using 'WITH ENCRYPTION' flag.
This flag encrypts the syscomments entries that contain the text of
CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being
published as part of SQL Server replication. So tamper will never know
what we check within trigger.
Mark wrote:
> you could continue to use a trigger, but the trigger only does
something if
> called by your application, so if someone changed a row via query
analyser
> the trigger will not fire.. see below
> create trigger mytrigger on mytable after update
> as
> begin
> if app_name() = 'myapp'
> begin
> ' do stuff here
> end
> end
> go
> "Vlad Olevsky" <leonid4142@.yahoo.com> wrote in message
> news:50540181.0505030708.b3397b6@.posting.google.com...
our
and
this

Navigation: Jump to URL

Hello,
I have been trying for a while to get one field in my report to link to
a page on my local server. I have looked in the older post as well as on
the internet and have found so examples that just don't work for me. I
am running SRS SP2 and using an MSCRM database as my source. The code I
am using is as follows: = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={' &
Fields!leadid.Value.ToString & '}'))". I am incredibly new to javascript
and sort of know what this code does but I don't know if I am using the
function correctly and if this function will even work. When I use the
hyperlinked field, my screen flickers and nothing happens. So any help
you can offer will be greatly appreciated.
Regards,
Jeff JonesYou are assembling a string here. Change it to this:
= "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
Fields!leadid.Value.ToString & "'}'))".
You need the additional double quotes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeffrey Jones" <jeffjones176@.kc.rr.com> wrote in message
news:eTc1s1PpFHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
> I have been trying for a while to get one field in my report to link to a
> page on my local server. I have looked in the older post as well as on the
> internet and have found so examples that just don't work for me. I am
> running SRS SP2 and using an MSCRM database as my source. The code I am
> using is as follows: => "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={' &
> Fields!leadid.Value.ToString & '}'))". I am incredibly new to javascript
> and sort of know what this code does but I don't know if I am using the
> function correctly and if this function will even work. When I use the
> hyperlinked field, my screen flickers and nothing happens. So any help you
> can offer will be greatly appreciated.
> Regards,
> Jeff Jones|||Bruce L-C [MVP] wrote:
> You are assembling a string here. Change it to this:
> = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
> Fields!leadid.Value.ToString & "'}'))".
> You need the additional double quotes.
>
Thank you for pointing that out. Though, after I change my syntax, I
still can't get the code to work. Could this be an explorer setting? I
have tried this same code but with google.com replaced with my crm url.
My screen flashes and windows makes a 'ding' sound and then nothing.
Looked in the event logs. Nothing. Looked at the task manager as I was
firing the link. Nothing. I don't really know what is happening with
this. Any other suggestions would be helpful.
Thanks,
Jeff|||Here is a working one for me. Take the following line and cut and paste it
into the jump to url.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Note that you have to have SP1 or SP2 installed for this to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeffrey Jones" <jeffjones176@.kc.rr.com> wrote in message
news:eMg$$o0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Bruce L-C [MVP] wrote:
>> You are assembling a string here. Change it to this:
>> = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
>> Fields!leadid.Value.ToString & "'}'))".
>> You need the additional double quotes.
>>
> Thank you for pointing that out. Though, after I change my syntax, I still
> can't get the code to work. Could this be an explorer setting? I have
> tried this same code but with google.com replaced with my crm url. My
> screen flashes and windows makes a 'ding' sound and then nothing. Looked
> in the event logs. Nothing. Looked at the task manager as I was firing the
> link. Nothing. I don't really know what is happening with this. Any other
> suggestions would be helpful.
> Thanks,
> Jeff|||Bruce L-C [MVP] wrote:
> Here is a working one for me. Take the following line and cut and paste it
> into the jump to url.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Note that you have to have SP1 or SP2 installed for this to work.
>
I have learned that spacing matters with this particular section of the
program. The syntax of the day is: ="javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={"&
Fields!leadid.Value &"}'))". There is a space between the amperstands
and the Fields name. Any thing less than this exact syntax will not
work. Also the link will not work in the VS dev. env. The report needs
to be deployed before it will work worth a darn. Since leadid is a GUID
and SRS does not play nice with GUIDs, you need to use <CAST(Lead.LeadId
as nvarchar(80)) as leadid> when creating your dataset. Follow all of
this and you will have a nice day.
Regards,
Jeff

Navigation problem

I have one report that uses "navigate" to another report through the data
region field. This works fine in the development environment (i.e. visual
studio), but when I deploy the reports and try it online on the live
environment, the 2nd report does not even open - i.e. goes to 2nd report
screen, but no report being generated and just blank report window. Nothing
happens.
I have SP2 on developer side, but not on server yet. Could this be the
issue? Other reports that use navigation (but created in SP1 development
environment) work.
Ideas?Does your production server has all the latest versions of your custom dlls.
--
Message posted via http://www.sqlmonster.com|||I found out that the problem is that the 2nd report must be runnable right
away, (i.e. cannot prompt user to enter any parameter values). Thus, I had
to give some parameters default values. This is not good. I am guessing,
using jump to URL would solve this problem, but is not a satisfactory
solution.
"Patrick P via SQLMonster.com" wrote:
> Does your production server has all the latest versions of your custom dlls.
> --
> Message posted via http://www.sqlmonster.com
>|||The intent of jump to report is to allow for drill through. You can map the
parameters of the second report to fields or parameters or really any
expression. You are right, you can use jump to URL if you are wanting the
user to fill in additional parameters. Not sure why that is not a
satisfactory solution. A little more work but after you have one jump to url
working the second time you do it is easier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bamboo" <bamboo@.discussions.microsoft.com> wrote in message
news:15945379-C022-4084-BF9C-15FF90133663@.microsoft.com...
>I found out that the problem is that the 2nd report must be runnable right
> away, (i.e. cannot prompt user to enter any parameter values). Thus, I
> had
> to give some parameters default values. This is not good. I am guessing,
> using jump to URL would solve this problem, but is not a satisfactory
> solution.
> "Patrick P via SQLMonster.com" wrote:
>> Does your production server has all the latest versions of your custom
>> dlls.
>> --
>> Message posted via http://www.sqlmonster.com