Can't execute View script using SMO

Hi !

I have another problem. Here is now what i'm doing:
Dim unTransfert As New Transfer(BDConfig)
unTransfert.CopyAllObjects = True

unTransfert.DropDestinationObjectsFirst = True
unTransfert.CopySchema = True
unTransfert.CopyData = False
unTransfert.Options.ContinueScriptingOnError = True
unTransfert.DestinationServer = ServeurLocal.Name.ToString
unTransfert.DestinationDatabase = NouvelleBD.Name.ToString
unTransfert.Options.IncludeIfNotExists = True
NouvelleBD.ExecuteNonQuery(unTransfert.ScriptTransfer())

This failed because of my views. If i tried to copy tables only it's working fine. I've tried to copy one by one the view and then i notice that most of then failed and i think i know the reason but i don't know what to do. I've notice that the view that failed are view where the SELECT contain other view. Since those view are not created yet i've got those error. This is a big problem. We have over 300 views and i don't know which one need which one.
Is it possible to copy the views without having this kind of problem ?

Thanks !This is the first time i'm using SMO so i'm probably making some mistakes. Instead of doing this line:
NouvelleBD.ExecuteNonQuery(unTransfert.ScriptTransfer())

Now i'm doing this:
unTransfert.TransferData()

I still have the same problem. But i've notice something. If i add:
unTransfert.CopyAllObjects = false
unTransfert.CopyAllTables = True
unTransfert.CopyAllViews = True

After running for some seconds i've got an error saying it can create this view because this table doesn't exists. In fact the table exist in my BD but while doing the tranfer the views seems to be create before all the table are added. Did i do something wrong ? All i want is to copy all object from one BD to a new one and i need to do this online.

I've been working on this for 2 days and it still not working :-(....

Is someone have an idea of what i'm missing ?

Thanks !!

can't execute tasks after moving .dtsx to new server

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

bobbins wrote:

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

The easiest way to redeploy SSIS Packages to a different server is to use the Deployment Utility: http://msdn2.microsoft.com/en-us/library/ms141190.aspx|||

Hi bobbins,

Thoughts / suggestions:

The 'document is opened by another project' error is interesting. First, make sure Business Intelligence Development Studio(BIDS) is shut down on your first server. Instead of creating a new empty project and importing the package, try copying the entire project folder to your shared drive and opening it from the BIDS on your second server. This sounds more like a file locking issue to me.

Hope this helps,

Andy

|||

Thanks for the replies.

I copied the entire project folder to the new server and it is ok. The only issue that arises is that some of the connection managers lose their login information, these are ADO.NET ODBC connections to an Ingres database, so I suspect this is just normal behaviour anyway.

Thanks again.

Can't execute sp in vb.net

Hellou
I'm trying to execute simple sp with 5 input parmeters from vb.net. This sp works great when it is run from SQL Server management studio, but when I want to execute it from vb.net, I allways get this error:
"Syntax error, permission violation, or other nonspecific error"
Here's the code:

Cnn.Open("...")

Comm.ActiveConnection = Cnn

Comm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

Comm.CommandText = "spSklVnos-Prenos" 'that's thename of the sp

RetParam.Direction = ADODB.ParameterDirectionEnum.adParamOutput

RetParam.Type = ADODB.DataTypeEnum.adInteger

Comm.Parameters.Append(RetParam)

P1.Direction = ADODB.ParameterDirectionEnum.adParamInput

P1.Type = ADODB.DataTypeEnum.adVarChar

P1.Size = 1

P1.Value = LocParA

Comm.Parameters.Append(P1)

P2.Direction = ADODB.ParameterDirectionEnum.adParamInput

P2.Type = ADODB.DataTypeEnum.adInteger

P2.Value = LocPar1

Comm.Parameters.Append(P2)

P3.Direction = ADODB.ParameterDirectionEnum.adParamInput

P3.Type = ADODB.DataTypeEnum.adInteger

P3.Value = LocPar2

Comm.Parameters.Append(P3)

P4.Direction = ADODB.ParameterDirectionEnum.adParamInput

P4.Type = ADODB.DataTypeEnum.adInteger

P4.Value = LocPar3

Comm.Parameters.Append(P4)

P5.Direction = ADODB.ParameterDirectionEnum.adParamInput

P5.Type = ADODB.DataTypeEnum.adInteger

P5.Value = PrevzemIDx

Comm.Parameters.Append(P5)

Comm.Execute()

Am I missing something?
Thanks for any suggestion.
Dido

Your syntax looks fine to me, but I'm not a VB.NET expert. Make sure your datatypes for the parameters match the stored procedure exactly. You might try asking this question on the VB forums, since it doesn't seem to be related to SSIS.

Can't Execute Package from BIDS Solution Explorer Post SP2 Install

Yesterday, SQL Server 2005 SP2 was installed on our DEV Server (Windows 2003 SP1 x86) and now I cannot right-click a package in the Solutions Explorer window in BIDS and Execute Package. When I do, I get the following error

===================================

Object reference not set to an instance of an object. (Microsoft Visual Studio)

Program Location:

at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)

I know I could do this yesterday morning pre SP2. I've attempted this with a couple of different packages and solutions, so it is probably not a corrupt package (unless now they are all corrupt). I can execute in debug mode if I open the package first and then hit Start Debugging. However this is not always an optimal solution because I sometimes have many connected packages and sometimes what gets executed is not what is expected.

Your thoughts would be appreciated.

I'm having the identical problem. If I create a new package, it will work for a short period of time. I didn't measure the time precisely, but it is within 15 minutes and an hour or so. I appreciate your run-in-debug solution, but it is still not a clean fix.sql

Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

can't decrypy the child package?

I had a child package AAA

The ProtectLevel of AAA is "EncryptAllWithPassword" and PackagePassword is "passtest"

now I put a Execute Package Task on Control Flow TAB of Package BBB

in this task I want to Execute package AAA,after set Password "passtest" of Execute Package Task ,I execute package in command line

DTExec.exe /F BBB.dtsx

I get error message,

can't decrypt package AAA

Where can I set the PackagePassword of AAA in Execute Package Task.

i haven't attempted to do this, but have you tried using a parent variable? http://msdn2.microsoft.com/en-us/library/ms345179.aspx|||

I didn't use any parent variable in child package,

the password protected child package can't be executed in parent package,how can I set the child's password in parent package?

|||

ycjj wrote:

I didn't use any parent variable in child package,

the password protected child package can't be executed in parent package,how can I set the child's password in parent package?

i understand that you aren't now using a parent variable. i am suggesting that you try using a parent variable and set it to the the child package password. then you should be able to use this variable when the parent package executes the child package.|||

thank you for the answer.

I will try it.

|||

thank you!

you are right, it works after I set the parent variable

can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
atte,
Hernnsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernn
|
|

can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
--
atte,
Hernánsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernán
|
|