Exec Proc with date parameter

Hi,
I have spent whole night to figure this out and right now i am hands up..
please help.
I have stored procedure (dsTPint_GLIntegrationRun) which have nested cursors
and cursors call external stored procedure to execute.
dsTPint_GLIntegrationRun have parameter of companyid which is database id. i
want to run stored procedure residing in the database which is equal to the
database passed as parameter.
Now one of the the parameters for the external store procedure is date, i am
passing date and keep getting on error incorrect syntax near '1'. I changed
the date parameter and supplied constant value for debugging and now i am
getting error cannot convert nvarchar into int. all the external stored
procedure looks fine, source table is hundered percent accurate, i am not
sure whats wrong. following is the syntax:
Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + ' OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' + @.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', ' +
@.Year + ', ''' + @.Currency + '''')
-- Exec dsTPint_GLIntegrationRun 'WSZM'
this Exec statement is producing the error and the parameter which is
producing the error is @.TrnxDate.
Thanks
DhariwalHave you tried replacing EXEC with PRINT to check for syntax errors?
PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
@.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
', ' +
@.Year + ', ''' + @.Currency + '''')
Also, you may want to use sp_executeSQL instead; it handles paramaters
much better than building a string.|||Remember that you are building a character string for your SQL statement.
Consequently, all of the variables and expressions must be character types.
Also, because you are passing a string containing constants to EXEC, you
can't have output parameter values. You need to use sp_executesql to return
output values from dynamic SQL. Below is an example you can tweak to
specify your actual datatypes and variable names.
DECLARE @.SQL nvarchar(4000),
@.dbname sysname,
@.oErrString varchar(50),
@.NEXTJNLNBR int,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int
SET @.dbname = N'MyDatabase'
SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
@.oErrString output,
@.NEXTJNLNBR output,
@.GPBatchNumber,
@.TrnxDate,
@.Reference,
@.Ortrxnum,
@.Period,
@.Year,
@.Currency'
EXEC sp_executesql @.SQL,
N'@.oErrString varchar(50) output,
@.NEXTJNLNBR int output,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int',
@.oErrString OUTPUT,
@.NEXTJNLNBR OUTPUT,
@.GPBatchNumber = @.GPBatchNumber,
@.TrnxDate = @.TrnxDate,
@.Reference = @.Reference,
@.Ortrxnum = @.Ortrxnum,
@.Period = @.Period,
@.Year = @.Year,
@.Currency = @.Currency
Hope this helps.
Dan Guzman
SQL Server MVP
"K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
> Hi,
> I have spent whole night to figure this out and right now i am hands up..
> please help.
> I have stored procedure (dsTPint_GLIntegrationRun) which have nested
> cursors
> and cursors call external stored procedure to execute.
> dsTPint_GLIntegrationRun have parameter of companyid which is database id.
> i
> want to run stored procedure residing in the database which is equal to
> the
> database passed as parameter.
> Now one of the the parameters for the external store procedure is date, i
> am
> passing date and keep getting on error incorrect syntax near '1'. I
> changed
> the date parameter and supplied constant value for debugging and now i am
> getting error cannot convert nvarchar into int. all the external stored
> procedure looks fine, source table is hundered percent accurate, i am not
> sure whats wrong. following is the syntax:
> Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', '
> +
> @.Year + ', ''' + @.Currency + '''')
> -- Exec dsTPint_GLIntegrationRun 'WSZM'
> this Exec statement is producing the error and the parameter which is
> producing the error is @.TrnxDate.
> Thanks
> Dhariwal|||Hi Stu,
Thanks for response.
I did try Print to see the statement, PRINT produces error that int cannot
be converted to string as there are parameters with int , date and varchar.
I will check sp_executeSQL to see if that helps.
Thanks
Dhariwal
"Stu" wrote:

> Have you tried replacing EXEC with PRINT to check for syntax errors?
> PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
> ', ' +
> @.Year + ', ''' + @.Currency + '''')
> Also, you may want to use sp_executeSQL instead; it handles paramaters
> much better than building a string.
>|||Dan and STU , you guys are stars. sp_executesql did the magic.
Thanks for your precious time, i really appreciated your help.
regards,
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Hi Dan,
One small issue still.
I cant retrieve values from OUTPUT parameters, they are NULL.
Following is the syntax:
-- build the exec query for the database specified in input parameter
SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
@.Ortrxnum, @.Period, @.Year, @.Currency')
SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
execute sp_executesql @.SQLString , @.ParmDefinition ,
@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumber
, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
PRINT @.NEXTJNLNBR
(This print returns nothing at all)
Also i noted that i had to declare @.NEXTJNLNBR before the above code else i
get error that varialbe needs to be declared.
Following is the PRINT of the above statement:
Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currency
Thanks
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Dhariwal,
Your declaration of @.ParmDefinition is probably too short to hold the
complete parameter definition.
before you execute sp_executesql, try PRINT @.ParmDefinition to
see, or change its definition to
DECLARE @.ParmDefinition(1000)
for example.
Steve Kass
Drew University
K M Dhariwal wrote:
>Hi Dan,
>One small issue still.
>I cant retrieve values from OUTPUT parameters, they are NULL.
>Following is the syntax:
> -- build the exec query for the database specified in input parameter
> SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
>OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
>@.Ortrxnum, @.Period, @.Year, @.Currency')
> SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
>OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
>@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
> execute sp_executesql @.SQLString , @.ParmDefinition ,
> @.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumbe
r
>, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
>@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
>PRINT @.NEXTJNLNBR
>(This print returns nothing at all)
>Also i noted that i had to declare @.NEXTJNLNBR before the above code else
i
>get error that varialbe needs to be declared.
>Following is the PRINT of the above statement:
>Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
>@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currenc
y
>Thanks
>Dhariwal
>
>"Dan Guzman" wrote:
>
>|||>>@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT
try this
1. you should declare all out parameters twice once in exec scope(param def)
and other in your calling code and then
@.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
print @.NEXTJNLNBR
Regards
R.D
"Steve Kass" wrote:

> Dhariwal,
> Your declaration of @.ParmDefinition is probably too short to hold the
> complete parameter definition.
> before you execute sp_executesql, try PRINT @.ParmDefinition to
> see, or change its definition to
> DECLARE @.ParmDefinition(1000)
> for example.
> Steve Kass
> Drew University
> K M Dhariwal wrote:
>
>|||Hi Steve & RD.
The problem was in the logic of external proc and the statement wasn't even
getting to the line where it must populate the value of output parameter.
Thanks for the help guys.
Regrads,
Dhariwal
"R.D" wrote:
> try this
> 1. you should declare all out parameters twice once in exec scope(param de
f)
> and other in your calling code and then
> @.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
> print @.NEXTJNLNBR
> Regards
> R.D
> "Steve Kass" wrote:
>

Exclude Null Dates

I can't seem to figure out how to exclude records with a null in a date
field as part of a filter expression. I've tried all of the following:
!=<blank>
!= System.DBNull
!=DBNull
!=""
!=''Try using IsNothing, like:
=IIF(IsNothing(Fields!MyDateField.Value), 1, 0)
Kaisa M. Lindahl Lervik
"Cindy Mikeworth" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:utiawBl6GHA.4232@.TK2MSFTNGP02.phx.gbl...
>I can't seem to figure out how to exclude records with a null in a date
>field as part of a filter expression. I've tried all of the following:
> !=<blank>
> != System.DBNull
> !=DBNull
> !=""
> !=''
>|||Awesome! That did the trick. Thanks for the reference tips as well.

Exclude column from replication, but leave column on subscriber.

It seems I can't figure out how to have two tables, say integer columns named
A, B, and C, with a rowguid column.
I want that table structure on my subscriber (A, B, C, rowguid) but I only want
to replicate data from columns A and B.
As it stands, replication tries to create it on the subscriber as A, B,
rowguid, leaving the C column off.
Thanks for any help.
Brian K
should have specified, this is merge replication, SQL Server 2000.
Brian
In article <4IJtc.16319051$Id.2708018@.news.easynews.com>, Brian Keener wrote:
> It seems I can't figure out how to have two tables, say integer columns named
> A, B, and C, with a rowguid column.
> I want that table structure on my subscriber (A, B, C, rowguid) but I only want
> to replicate data from columns A and B.
> As it stands, replication tries to create it on the subscriber as A, B,
> rowguid, leaving the C column off.
> Thanks for any help.
> Brian K
|||Brian,
is it a requirement that the rowguid changes independantly on publisher and
subscriber? If not and the guid can't change then why not include it?
Regards,
Paul Ibison
|||In article <OT6YKXNREHA.3300@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> is it a requirement that the rowguid changes independantly on publisher and
> subscriber? If not and the guid can't change then why not include it?
> Regards,
> Paul Ibison
>
Hmm, rowguid is not a column I'm worried about. It can stay the same
across the board. Row C need to be able to change independently on
the two tables.
Brian K
|||Brian,
you'd have to edit the merge triggers to enable this by preventing entries
into MSmerge_contents from changes to C, and I wouldn't recommend it. It
would also make validation impossible. You could however achieve the same
functionality using views - the view would be a join returning columns A, B,
C, rowguid while the underlying tables would be
table1: A,B,rowguid and
table2: C,pk (one of A or B)
Then you just replicate table1. If changes can occur to cols A/B and C in
one update then you'll need to set up instead of triggers.
HTH,
Paul Ibison
|||its not the merge triggers you edit for this. Its the stored procedures that
is uses which look like this
sp_ins_3477CF08A8A2481D3269F4FE171245B8
look for a statement that looks like this.
if @.retcode<>0 or @.@.ERROR<>0
begin
set @.errcode= 0
goto Failure
end
insert into [dbo].[authors] ( [au_id] , [au_lname] , [au_fname] , [phone] ,
[address] , [city] , [state] , [zip] , [contract] , [msrepl_tran_version] ,
[rowguid] ) values ( @.p1 , @.p2 , @.p3 , @.p4 , @.p5 , @.p6 , @.p7 , @.p8 , @.p9 ,
@.p10 , @.p11 )
select @.rowcount= @.@.rowcount, @.error= @.@.error
do this for both the insert and update procedures
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23zbYbaOREHA.3012@.tk2msftngp13.phx.gbl...
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A,
B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
|||Hilary,
I agree that it could be done in the stored procedure, but think the trigger
modification is simpler:
For an update, what I had in mind is something like
IF Not UPDATE(C)
BEGIN
existing trigger code
END
The insert and delete triggers could be left as they are, as both should
propagate through the normal merge replication.
Cheers,
Paul Ibison
|||Paul
After posting my solution I had reservations about it.
The advantage of your solution is the update trigger will fire but won't
write replication metadata for updates to the columns you wish to exclude.
My solution is that all updates will be propagated to the subscriber, but
will not be applied.
In retrospect I think your solution is perhaps the better one.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23c9lNXaREHA.2408@.tk2msftngp13.phx.gbl...
> Hilary,
> I agree that it could be done in the stored procedure, but think the
trigger
> modification is simpler:
> For an update, what I had in mind is something like
> IF Not UPDATE(C)
> BEGIN
> existing trigger code
> END
> The insert and delete triggers could be left as they are, as both should
> propagate through the normal merge replication.
> Cheers,
> Paul Ibison
>
|||In article <#zbYbaOREHA.3012@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A, B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
Pretty much what I assumed.
I've already implemented this as a view in our test environment, but
thanks for the information.
Brian K

Excel Worksheets Become Corrupt -- Bad Metadata?

One of my main users has had a few Office 2003 SP2 worksheets go corrupt on him and I can't seem to figure out why.

For performance reasons, I recommended that he use page filters whenever possible. As of late, he'll send a workbook along that has a few page filters (between 3 and 4) and one of the page filters gets mixed up somehow.

For example, let's say he has a Product Line page filter and a Product Name page filter. The Product Line page filter has multiple selections enabled and he will go ahead and select a few product lines and save the file so he can simply refresh the data in the future. Eventually, the Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable, usually resulting in a strange error indicating that Excel cannot complete the task with available resources when the drop-down is selected. The other page filters seem to behave normally and the report will still operate until the corrupted page filter(s) are selected.

I've seen 2 of his worksheet/workbooks exhibit this behavior this week. He says he's encountered this before and has rebuilt these reports from scratch only to end up with the same issue.

The AS2005 cube has not undergone any recent changes.

Any thoughts?

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

|||

Tigran.Hayrapetyan wrote:

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

Thanks for the reply Tigran. What are some other details that I can provide?

As for the same names, the values for Product Name and Product Line are vastly different from one another.|||

What I would like to know is what exactly happens in Page filter. When you use the dropdown for Product Name hierarchy, do members from Product Lines hierarchy appear in the Tree control?

But whatever the case, I think the best solution could be to involve Microsoft Support in resolving this issue.

|||

I came accross similar issues and found that the most easiest way to solve this is to use the Excel pivot table wizard to remove all the offending dimensions from the pivot view, refresh the pivot and put back the dimensions in the pivot view.

This will happen mostly when you change dimensions names or when the dimensions member names content change while the client has set a selection on a given member. (no longer in the cube).

Also be carefull with ColumnKeys and ColumNames It lloks like Excel will choke on dimensions where you defined a name and a key column while it was initially only a key column instead of using both a name and a key,

Excel will now use a key (internally, hard to spot). This is mostly impacting Excel views where you use VBA to control the pivot.

Hope it helps a little bit.

So, on the same topic, the issue I am facing now is linked to default member of a time dimension to be the current date.

Initially it works, however, as soon as I update the cube, it will stop to work because the current date set in the Excel view is no longer present in the cube as the current date set as default in the cube. Excel will not pick-up these changes dynamically without removing and putting back-in the dimension. (Excel2003 PTS9)

This lack of dynamic refresh of Excel members is annoying and was already an annoyance in SQL2000.

I would rather have the selector set-back to "ALL" rather than chocking on a missing member.

End users can cope with a selector reset to all, they cannot cope with a cube returning cryptic error messages.

Philippe.

|||

One more comment.

When using VBA to pilot a pivot from code, if you attempt to select a member that is no longer in the cube, you may end-up corrupting your pivot table.

I suspect tha this can also happen in some other cases where you do not use VBA.

Symptoms are as follow:

- Calculated members returns #value error code

- Break-down by individual members are no longer correct while the total remain correct.

Only way out is to rebuild a new pivot table from scratch.

This was happening a lot with Excel XP, it still happen with Excel2003 but very unfrequently.

Just to be on the safe side, when I am done developing a cube and its Excel pivot front-end, I always re-create the pivot from scratch just to make sure it is as clean as it can get.

Philippe

Excel to SQl

Hi All

Thnks for the time

I was trying to figure out how to insert the data that I read from an excel sheet into multiple tables in SQL Database 2005.

// Connection string to the excel file

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source= D:\\UserData.xls;" +

"Extended Properties=Excel 8.0;";

//Command to read data is

SELECT ID,ProductName,ProductDesc,CategoryName FROM [Products$]

//Connection string for sql 2005 database

string sqlConnectionString = "DataSource=MyServer\\MSSQLEXPRESS;Initial Catalog=TestExcel;Integrated Security=True";

And what is your question ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Excel Objects From SSIS

I'm trying to use Excel objects from within an SSIS Script Task but I can't figure out how to add a reference the object library. I'm using Excel 2003 and tried Add Reference from the Project menu but there is no entry for Microsoft Excel Object Library or Microsoft Excel 11.0 Object Library. Any suggestions? Thanks.You would need to copy the Excel PIA to a specific folder for it to become available in the VSA Add Reference dialog. http://support.microsoft.com/kb/306149

Note that automation of Office apps from server-side components that often run unattended, like Integration Services packages, is discouraged. For more information, see 257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757.

-Doug
|||I've got the Excel 2003 PIA installed. It appears to be in the GAC. Using windows explorer, I'm given the option to uninstall it from c:\windows\assembly, but not the option to copy or move it to another folder. Using the command line, when I navigate to c:\windows\assembly, I don't find the file listed in c:\windows\assembly, but instead I find a lot of nested folders. How do I move the PIA to somewhere that VSA can see it?

I'm trying to create the excel file that SSIS will soon be exporting data to. Is there another way to do that (programatically) inside SSIS?

Thanks,

Matthew Martin|||That's a lot of overhead to invoke excel.exe, and discouraged since packages often run unattended.

I would (1) use the Export Wizard to create the destination spreadsheet file initially or (2) save a blank one somewhere as a "template" and use a Script task (for example) to make a copy of it each time the package runs.

I once saw the syntax to create a new Excel file by using the Jet provider (through ADO rather than Excel), but now can't find it. Since the provider normally expects an existing database (naturally), I seem to recall that it wasn't obvious.

Best regards,

-Doug