Deserialization failed

I am trying to set the <Top> value of an image thru code.
<Image Name="Marker">
<ZIndex>35</ZIndex>
<Height>0.14583in</Height>
<Width>0.15625in</Width>
<Source>Embedded</Source>
<Style />
<Value>marker</Value>
<Top>=Code.GetPosition(Fields!TotalScore.Value, "1.55208in")</Top>
The error I receive is: "Deserialization failed:
=Code.GetPosition(Fields!TotalScore.Value, "1.55208in") cannot be parsed as a
unit because it does not contain numeric values. Examples of valid unit
strings are 1pt and .5in."
Is there a way to set the <Top> values thru code?
Thanks,
Phill SmogerPhill - Did you ever figure this out? I am trying to do the same thing for
the Width.
Thanks. Jen
"SmogerP" wrote:
> I am trying to set the <Top> value of an image thru code.
> <Image Name="Marker">
> <ZIndex>35</ZIndex>
> <Height>0.14583in</Height>
> <Width>0.15625in</Width>
> <Source>Embedded</Source>
> <Style />
> <Value>marker</Value>
> <Top>=Code.GetPosition(Fields!TotalScore.Value, "1.55208in")</Top>
> The error I receive is: "Deserialization failed:
> =Code.GetPosition(Fields!TotalScore.Value, "1.55208in") cannot be parsed as a
> unit because it does not contain numeric values. Examples of valid unit
> strings are 1pt and .5in."
> Is there a way to set the <Top> values thru code?
> Thanks,
> Phill Smoger
>|||<Top> and other position elements are always interpreted as constant values
representing measurement units. Expressions are not supported at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen Rubin" <JenRubin@.discussions.microsoft.com> wrote in message
news:94FD98AD-5CA1-4CE0-915A-AC760CF0291E@.microsoft.com...
> Phill - Did you ever figure this out? I am trying to do the same thing for
> the Width.
> Thanks. Jen
> "SmogerP" wrote:
>> I am trying to set the <Top> value of an image thru code.
>> <Image Name="Marker">
>> <ZIndex>35</ZIndex>
>> <Height>0.14583in</Height>
>> <Width>0.15625in</Width>
>> <Source>Embedded</Source>
>> <Style />
>> <Value>marker</Value>
>> <Top>=Code.GetPosition(Fields!TotalScore.Value,
>> "1.55208in")</Top>
>> The error I receive is: "Deserialization failed:
>> =Code.GetPosition(Fields!TotalScore.Value, "1.55208in") cannot be parsed
>> as a
>> unit because it does not contain numeric values. Examples of valid unit
>> strings are 1pt and .5in."
>> Is there a way to set the <Top> values thru code?
>> Thanks,
>> Phill Smoger

Describe command.

Hi,

I have some (OLD) college notes that say if I type

Describe tablename;
Name NOT NULL? Type

in my SQL code window in access once run it will give me the structure of the table.

When I try to run I get a Invalid SQL statement error 'DELETE', 'INSERT'...

Where am I going wrong.

Thanksoh, it's simple, your old notes are wrong, access doesn't support that commandsql

Derived Column or script

Dear All,

I am pretty new to SQL2005 technologies.

I have a table like this:

Name

Code

Peter

10

Eric

20

I am interested in translation of the “code” to “job role”:

Name

Code

Job role

Peter

10

Director

Eric

20

Consultant

I have tried to use Derived Column and also script – but I am unable to get it to work.

Any hints/solution would greatly appreciate.

Best Regards, T

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

|||

Sagestore wrote:

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

Does not seem like the correct syntax, I think it should be:

[Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )

|||

Do you have the translations stored in a table somewhere? If so, the LOOKUP component can be used.

-Jamie

|||

The translation is hardcoded which is fine.

However the number of options are more than 2 "consultan" and "director" - at least up to 5 different options.

So I need someting like:

Case 10 => Director

Case 20 => Consultant

Case 30 => Admin

Etc.

Thanks for input so far - but still im waiting for a soluion.

T

|||

Michael gave you the solution - you just need to extend it to cover all eventualities:

[Code] == 9999 ? "Something else!" : ([Code] == 30 ? "Admin" : ([Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )))

-Jamie

|||

Thanks all of you - now I finally got it to work.

I am extremely happy with this kind of support.

Thanks again and take care, T

Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

Deployment 'utility' script using sqlcmd

Hi all,
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath = 'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH

ErrorAbort:
[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a "proper" coding language like .Net, c# etc. but (a) it's just a simple utility script (b) it teaches me moore about usage of SQLCMD and (c) I do not have any skill in any normal programming language, hence I was planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a list of scripts registered in it, with some sequence logic - like creating parent tables before children tables - and then a cursor (or a better method if I can find it) that fetches a sqlcmd filename execution command, executes it, and on success fetches the next one based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find a way to confirm the successful execution of the previous command....
|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client SQL commands - it would fetch them by referring to the table, and fetching the path to the SQL file, and building a SQLCMD to execute that script
I guess, as you say, I could add a generic update ##SQLScriptTracker table, then check it on the new execution, or abort. I had hoped for a neater solution - i.e. SQLCMD being able to return a returncode that it gets from a SQL file it ran....
|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.
Thanks
|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

sql

Deployment on load balanced servers.

Hi, I'm trying to solve the problem with deploying sql code on load-balanced application server.
Normally when we put new feature\fix live we take one server out of the loop, than we put new app code, we test it and then we bring it back to the loop. But we've started to have a problem when we had to change stored procedures at the same time to run the new app code, becouse either 'test' server won't work either production application won't work. I wonder is there any good solution for this problem.

Regards
Piotr.
P.S. Sorry for the forum topic but I didn't know where to put it.

what I understand is that your stored procedure signature is still the same but its implementation is changed in such a way that only the new application code can work with it. Your configuration is mulitple application servers talking to 1 SQL Server. In that case, let us say you have 10 application servers. I will take 5 (let us call them group-A) of those out of the loop, install new app code. Note, your app is still up and running though at 1/2 the capacity. you can test your group-A with another SQL Server (say the test SQL Server that has new stored procs). Once it is done, you can take other group of app servers offline (this will cause short term unavailability of your application), change the stored proc on the production SQL Server, and then bring group-A online. Now you can do the same with other set of application servers.

|||That was very helpful, but I'm more after stored procedure versioning solution. Sometimes we need old and new code running (part of requests are hitting old and other new code). Thanks in advance.|||http://weblogs.asp.net/fmarguerie/archive/2003/02/24/2880.aspx fyi.sql

deploying packages

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

WTF? ·%$·&$%%& i`m sure my connections are ok,, cause i prevoiusly checked them, the problem occurs when deploying the package.. it seems to be an error when connecting to the source but whyy?, and what about the package configurations ? should i use them,, and how?

Does the error message occur while you 'deploy' them or when you try to execute them after deployment. I am assuming 'deploy' at the least means they are now on another computer from where you created the package. Did you modify the package property 'protectionlevel' before you deployed? If not the default value may be causing your issues and you will want to read about the protectionlevel property in books online.

Hope that helps.

|||

in my solution proyect works fine.. so i have the deployment utility folder, then i copy it to the target computer, the error appears when i'm using the wizard to deploy it.. i`ve changed the protection level and it remains the same error... which is the correct option?

sorry if my english sucks..

|||

apparently i′ve found a possible solution.. in the project properties on the security tab.. i stablished a package password, then skept the prevalidation of the package during the deployment, once in my target server i run the package giving the password package and bingo!!! it worked.. i don`t know if this is the most viable solution but it worked anyway.. please can anybody tell me if this is the best option?

Deploying CLR Stored Procedures to a client, is there a better way

I'm developing an application locally, and after I complete major revisitions/bugfixes, I transfer the code/database changes to the client. If I change the code for a CLR stored procedure locally, I can just deploy the project to the local database, which handles everything for me.

Transferring to the client's database is more difficult, however. In order to update the CLR procedures, I need to drop my regular stored procedures, drop my functions, drop the CLR procs, and then re-add the CLR procs, functions and stored procedures to avoid errors based on dependencies. The stored procs/functions don't change, and it seems like there should be a way to just insert the new CLR stuff without dealing with the regular stored procedures or userdefined functions.

Basically, I'm asking whether there is a better way to do this, or if I'm stuck doing this excessive routine every time I need to update.

Thanks in advance

it sounds like your in the "debug" phase right now, so you could use multiple assemblies to seperate those SQLCLR routines that you know are still giving you problems and those that are not. Once fully tested you could then re-merge them back into one assembly.

You could also look at using the scripting wizard to script the assembly and all of it's dependencies from your local/dev box.

Derek

|||

Have you tried using ALTER ASSEMBLY from T-SQL to deploy changes to the assembly.

Detailed documentation on ALTER ASSEMBLY is available at http://msdn2.microsoft.com/en-us/library/ms186711(d=ide).aspx

Thanks,

-Vineet

Deploying an Assembly in "Analysis Server" through Code

Hi,

I have a requirement wherein I have to deploy an assembly in "Analysis Server" through Code. I tried using "CREATE ASSEMBLY .... " which works fine with Database Server, but is not being recognized as a command in "Analysis Server".

Is there any other way of doing this except the Wizards.

Thanks!!

You could go a couple of routes...

1. XMLA - If you add an assembly via Management Studio and click the Script button at the top of the dialog, you'll see the XMLA for adding an assembly. Then you will need to figure out how to encode the bytes of your DLL as it expects and create your own XMLA request.

2. AMO - If you use Analysis Management Objects, you can do this through an API and don't have to mess with XMLA.

I would suggest #2. I haven't seen code to do exactly this. But there is some open source code to do the reverse (inspect a deployed DLL) which might help you get a feel for the AMO objects you'd use:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=ListFunctions&referringTitle=Home

Hope that gives you enough info to get a little further than before. Post back here if not.

Deploying an Assembly in "Analysis Server" through Code

Hi,

I have a requirement wherein I have to deploy an assembly in "Analysis Server" through Code. I tried using "CREATE ASSEMBLY .... " which works fine with Database Server, but is not being recognized as a command in "Analysis Server".

Is there any other way of doing this except the Wizards.

Thanks!!

You could go a couple of routes...

1. XMLA - If you add an assembly via Management Studio and click the Script button at the top of the dialog, you'll see the XMLA for adding an assembly. Then you will need to figure out how to encode the bytes of your DLL as it expects and create your own XMLA request.

2. AMO - If you use Analysis Management Objects, you can do this through an API and don't have to mess with XMLA.

I would suggest #2. I haven't seen code to do exactly this. But there is some open source code to do the reverse (inspect a deployed DLL) which might help you get a feel for the AMO objects you'd use:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=ListFunctions&referringTitle=Home

Hope that gives you enough info to get a little further than before. Post back here if not.

Deploying a managed code trigger

I am working on a managed code trigger using visual studio 2005 and SQL 2005
.
When I try to deploy it I get the following error:
Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
level of the current database to a higher value to enable this feature. See
help for the stored procedure sp_dbcmptlevel.
I have set the assembly permission level to safe.
The database I am using is one I restored from SQL 2000 into SQL 2005. I
tried setting the datbase compatibility level to 8, and then to 7, and still
get the same error. Any ideas?
Ron Coffee, MCSD> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and
> still
> get the same error. Any ideas?
CLR triggers were introduced in SQL 2005. Have you tried setting the
database compatibility level to 90 (SQL 2005)?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ron_Coffee" <Ron_Coffee@.discussions.microsoft.com> wrote in message
news:181E0D5F-D699-477C-B266-31D2AE5B464E@.microsoft.com...
>I am working on a managed code trigger using visual studio 2005 and SQL
>2005.
> When I try to deploy it I get the following error:
> Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
> level of the current database to a higher value to enable this feature.
> See
> help for the stored procedure sp_dbcmptlevel.
>
> I have set the assembly permission level to safe.
> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and
> still
> get the same error. Any ideas?
> --
> Ron Coffee, MCSD|||You'll need compatibility level 9 -- SQL 2005 is version 9.0
"Ron_Coffee" wrote:

> I am working on a managed code trigger using visual studio 2005 and SQL 20
05.
> When I try to deploy it I get the following error:
> Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
> level of the current database to a higher value to enable this feature. Se
e
> help for the stored procedure sp_dbcmptlevel.
>
> I have set the assembly permission level to safe.
> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and sti
ll
> get the same error. Any ideas?
> --
> Ron Coffee, MCSD|||Thanks! That solved it
--
Ron Coffee, MCSD
"Dan Guzman" wrote:

> CLR triggers were introduced in SQL 2005. Have you tried setting the
> database compatibility level to 90 (SQL 2005)?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ron_Coffee" <Ron_Coffee@.discussions.microsoft.com> wrote in message
> news:181E0D5F-D699-477C-B266-31D2AE5B464E@.microsoft.com...
>
>

Deploying a full database through XMLA and C#

Hi all,

I want to create , deploy and process the XMLA scripts for a full projectin SSAS . I want to do this through code C#. I have the XMLA's. I want the code to check wether the database exists and tehn drop it if it exists and create a new database , create and deploy and process the DSV, cubes and other objects one by one taking the XMLA. I also want to capture the log as to what happened , i mean wether it was sucessful or it threw an error.

Please give me some sample code as to how to go about it...

Regards...

Girija Shankar

Hi,

You can use AMO to check if the database exist, to drop it, to re-create it, to run XMLA scripts.

Sample code for running XMLA scripts with AMO:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516930&SiteID=1

You mentioned that you want to drop the database if it exists and then re-create it. You can do that in a single step, the 'Create' command has the 'AllowOverwrite' parameter:

<Create AllowOverwrite='true'>

... the database definition here ...

</Create>

Adrian Dumitrascu

|||Hi Adrian,

Thanks for the answer. But I think you didnot get my question. I want to check in the server wether the database exists or not. if it exists i will drop that and take the xmla script from a specified location as a xmla file and then process it to cretae the necessary objects. then i process the cubes present in the Database one by one. Now the point is how do i check wether it exists and i want to catch the processing results ( wether success or failure). The XMLA will be a predifined file existing on local system.

Regards....
Girija Shankar

Deployed report with custom assembly fail when calling web service

Hi,
I'm developing a report that calls a web service using a custom assembly.
I've got the code working in VS.NET, but when I deploy to my local Report
Server, I get this:
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.SecurityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed
I have added the following to RSReportServer.config:
<CustomAssemblies>
<Assembly Name="cmcs.wsclient.dll, Version=1.0.xxx.xxxx,
Culture=neutral, PublicKeyToken=xxx">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
<IPermission class="System.Security.Permissions.WebPermission"
version="1"
Unrestricted="true"
/>
<IPermission
class="System.Security.Permissions.EnvironmentPermission"
version="1"
Unrestricted="true"
/>
</PermissionSet>
</Assembly>
<Assembly Name="cmcs.msrs.webserviceclient, Version=1.0.xxx.xxxx,
Culture=neutral, PublicKeyToken=xxx">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
<IPermission class="System.Security.Permissions.WebPermission"
version="1"
Unrestricted="true"
/>
<IPermission
class="System.Security.Permissions.EnvironmentPermission"
version="1"
Unrestricted="true"
/>
</PermissionSet>
</Assembly>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execute"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
In rssrvpolicy.config, I have changed the PermissionSet for
Report_Expressions_Default_Permissions to FullTrust and added the following:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Attributes="LevelFinal"
Name="ReportServer-cmcs.msrs.webserviceclient"
Description="Codegroup generated by the .NET
Configuration tool">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program
Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/cmcs.msrs.webserviceclient.dll"/>
</CodeGroup>
It works to call a HelloWorld method returning a string from the custom
assembly, so there is no total failure, but calling the method to invoke the
web service fails.
Please help, I feel that I'm totally stuck!
JonasBy moving my CodeGroup in rssrvpolicy.config to right under the first
default CodeGroup, it I got it to work ...
<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="Nothing">
<IMembershipCondition
class="AllMembershipCondition"
version="1"
/>
***** RIGHT UNDER HERE *****
It seems like my CodeGroup wasn't used by ReportServer until the move. We'll
just have to see how long time it will work before it breaks again ;-)
Jonas" <Jonas@.nospam.pl> wrote in message
news:u$YVj30IGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm developing a report that calls a web service using a custom assembly.
> I've got the code working in VS.NET, but when I deploy to my local Report
> Server, I get this:
> System.Security.SecurityException: Request for the permission of type
> System.Security.Permissions.SecurityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed
> I have added the following to RSReportServer.config:
> <CustomAssemblies>
> <Assembly Name="cmcs.wsclient.dll, Version=1.0.xxx.xxxx,
> Culture=neutral, PublicKeyToken=xxx">
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execution, Assertion"
> />
> <IPermission class="System.Security.Permissions.WebPermission"
> version="1"
> Unrestricted="true"
> />
> <IPermission
> class="System.Security.Permissions.EnvironmentPermission"
> version="1"
> Unrestricted="true"
> />
> </PermissionSet>
> </Assembly>
> <Assembly Name="cmcs.msrs.webserviceclient, Version=1.0.xxx.xxxx,
> Culture=neutral, PublicKeyToken=xxx">
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execution, Assertion"
> />
> <IPermission class="System.Security.Permissions.WebPermission"
> version="1"
> Unrestricted="true"
> />
> <IPermission
> class="System.Security.Permissions.EnvironmentPermission"
> version="1"
> Unrestricted="true"
> />
> </PermissionSet>
> </Assembly>
> <Default>
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execute"
> />
> </PermissionSet>
> </Default>
> </CustomAssemblies>
> In rssrvpolicy.config, I have changed the PermissionSet for
> Report_Expressions_Default_Permissions to FullTrust and added the
following:
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Attributes="LevelFinal"
>
Name="ReportServer-cmcs.msrs.webserviceclient"
> Description="Codegroup generated by the .NET
> Configuration tool">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="file://C:/Program
> Files/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportServer/bin/cmcs.msrs.webserviceclient.dll"/>
> </CodeGroup>
> It works to call a HelloWorld method returning a string from the custom
> assembly, so there is no total failure, but calling the method to invoke
the
> web service fails.
> Please help, I feel that I'm totally stuck!
> Jonas
>

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)