Program Connection: create

Showing posts with label create. Show all posts

Wednesday, March 28, 2012

Property AnsiNullsStatus is not available for UserDefinedFunction...

If you create a table UDF in a SQL 8 database from Server Management
Studio, and then attempt to edit it again, you get the following error:
Property AnsiNullsStatus is not available for UserDefinedFunction
'[dbo].[TF_TEST]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.
I have reproduced this with a number of UDFs
The UDF appears to be OK, but it is kind of irritating to have to open
up a different tool to edit it again.
Hello,
I have tested the issue on my side, but I didn't reproduce the issue. To
help me troubleshoot the issue, please post here the detail steps to
reproduce the issue. For your reference, I tested the issue by performing
the following steps:
1. Create a function on a SQL server 2000 database using SQL server 2005
management studio.
create function myuf()
returns table
return (select * from authors)
select * from test5.dbo.myuf()
2. Alter the function:
alter function myuf()
returns table
return (select * from authors where au_id='172-32-1176')
The following command works fine:
select * from test5.dbo.myuf()
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Try these steps:
1.Create a multi-statement table function (not an inline; that works
OK) on a SQL 2000 db using SQL server 2005 management studio. I used
the wizard, but doing it by hand produces the same results:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:ScpoRich
-- Create date: 11/25/2005
-- Description:Test Table Function
-- =============================================
CREATE FUNCTION MSDNTest
(
-- Add the parameters for the function here
@.p1 int,
@.p2 char
)
RETURNS
@.Table_Var TABLE
(
c1 int,
c2 int
)
AS
BEGIN
insert into @.Table_Var(c1,c2)
values(@.p1,100)
RETURN
END
GO
2.The following command works fine:
SELECT * FROM [pubs].[dbo].[MSDNTest] (1,'')
3.The following command works fine:
ALTER FUNCTION MSDNTest
(@.p1 int, @.p2 char)
)
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
insert into @.Table_Var(c1,c2)
values(100,@.p1)
RETURN
END
GO
4.Right click function in the Object explorer and select 'Modify' from
the context menu.
5.Observe a dialog box with the following message:
TITLE: Microsoft SQL Server Management Studio
Property QuotedIdentifierStatus is not available for UserDefinedFunction
'[dbo].[MSDNTest]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
BUTTONS:
OK
6.Selecting Script AS -> CREATE To or Script As -> ALTER to will cause
similar errors to occur.
Sophie Guo [MSFT] wrote:
> Hello,
> I have tested the issue on my side, but I didn't reproduce the issue. To
> help me troubleshoot the issue, please post here the detail steps to
> reproduce the issue. For your reference, I tested the issue by performing
> the following steps:
> 1. Create a function on a SQL server 2000 database using SQL server 2005
> management studio.
> create function myuf()
> returns table
> return (select * from authors)
> select * from test5.dbo.myuf()
> 2. Alter the function:
> alter function myuf()
> returns table
> return (select * from authors where au_id='172-32-1176')
> The following command works fine:
> select * from test5.dbo.myuf()
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I have been having a similar problem whereby I am trying to script a
bunch of table functions from a SQL Server 2000 database through SQL
Management Studio. I have fiddled around with DB Options but to no
avail. Is there any resolution on this?
Cheers
Dave Mc
david.mcmahon@.ridgian.co.uk
http://www.xmlexperience.com
|||Rich (scporich@.community.nospam) writes:
> If you create a table UDF in a SQL 8 database from Server Management
> Studio, and then attempt to edit it again, you get the following error:
> Property AnsiNullsStatus is not available for UserDefinedFunction
> '[dbo].[TF_TEST]'. This property may not exist for this object, or may
> not be retrievable due to insufficient access rights.
> I have reproduced this with a number of UDFs
> The UDF appears to be OK, but it is kind of irritating to have to open
> up a different tool to edit it again.
There is a very similar bug on
http://lab.msdn.microsoft.com/produc...5-62d1fb31d0a4
which is reported as fixed for SP1.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hello,
Based on my test, when connectting to a SQL2000 database using the SQL
Managemenet Studio, I can reproduce the issue. However, if I create a SQL
server 2000 database in SQL Managemenet Studio, which means the
compatibility level is "SQL server 2000(80)", everything works fine.
Therefore I think the workaround is to import the SQL server 2000 database
into SQL server 2005 instance. You can import the database using the Copy
database wizard.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Erland Sommarskog wrote:
> Rich (scporich@.community.nospam) writes:
>
> There is a very similar bug on
> http://lab.msdn.microsoft.com/produc...5-62d1fb31d0a4
> which is reported as fixed for SP1.
>
Yep - That looks like the one - thanks for the post.
Rich

Monday, March 26, 2012

Properties Expressions

I can create an expression for the visibility property as follows:
=iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
BUT when I try to do something similar for the fontweight property like
=iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error message
Name Heavy is not declared
'Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
>I can create an expression for the visibility property as follows:
> =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> BUT when I try to do something similar for the fontweight property like
> =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error
> message
> Name Heavy is not declared
> '|||Another gotcha is using font weight names in expression. When using a font
weight in an expression you have to use the serialized value.
In your case you will need to use =iif(Fields!Carryover.Value = 2, "900",
"Normal).
This chart shows how the named displyed in Report Designer's UI maps to the
serialized value.
UI Display Serialized
Lighter Lighter
Thin 100
Extra Light 200
Light Light
Normal Normal
Medium 500
Semi-Bold 600
Bold 700
Extra Bold 800
Heavy 900
Bolder Bolder
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:O86wRYyeEHA.384@.TK2MSFTNGP10.phx.gbl...
> Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
> >I can create an expression for the visibility property as follows:
> > =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> >
> > BUT when I try to do something similar for the fontweight property like
> > =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following
error
> > message
> >
> > Name Heavy is not declared
> >
> > '
>

Proper use of Event Notifications

Hi.

I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"

I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.

Thanks,

Chris

If all you need is a single instance of your service and don't mind it running all the time, you could implement this as a Windows Service that does a WAITFOR with no timeout. But if you want multiple instances of your service to be dynamically activated depending on the rate of incoming messages and how quickly your service is able to consume them, the external activator becomes useful. The main purpose of the external activator is to make services scalable.

The external activator is also capable of monitoring multiple queues, each configured with its own service program. So if you had 10 services, you do not need to have 10 windows services running even when queues are idle. You will have a single external activator running which will dynamically launch the service programs as messages arrive.

Hope that helps,

Rushi

|||

Rushi,

After doing some more digging into the External Activator, I understand more clearly now. It seems that the scalability benefits are the real key for us. That and doing a WAITFOR with an indefinite timeout isn't so easy in a Windows Service.

Thanks,

Chris

|||The external activator does some of the hard things, like maintaining a recovery log so that if the process was to terminate and it came back up, it would recover state and not miss any notifications thus ensuring that queued messages do not get orphaned.

Friday, March 23, 2012

Prompting connection str to access SQL server

Hi ,
my connection string is as per follows but how can i made it such that i
need not hard code the useid & pwd w/o having to create a user interface ?
tks & rdgs
Can you please send the connection string
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs
|||As far as I understand - you must use trusted connection option and depend
on account that your application work with.
Bojidar Alexandrov
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs
|||That depends just on your application you code, there are serveral ways for
doing this:
Storing the password in a external storage (evtl. with encryption)
-registry
-xml file
-active directory
...
Using Windows Authentification:
http://msdn.microsoft.com/library/de...urity_47u6.asp
Using a DSN to connect with, (Has already a GUI ;-) )
But all that depends ont eh app you are coding the enviroment and so on..
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"maxzsim" <maxzsim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs

Prompting connection str to access SQL server

Hi ,
my connection string is as per follows but how can i made it such that i
need not hard code the useid & pwd w/o having to create a user interface ?
tks & rdgsCan you please send the connection string
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs|||As far as I understand - you must use trusted connection option and depend
on account that your application work with.
Bojidar Alexandrov
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs|||That depends just on your application you code, there are serveral ways for
doing this:
Storing the password in a external storage (evtl. with encryption)
-registry
-xml file
-active directory
..
Using Windows Authentification:
http://msdn.microsoft.com/library/d...>
ity_47u6.asp
Using a DSN to connect with, (Has already a GUI ;-) )
But all that depends ont eh app you are coding the enviroment and so on..
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"maxzsim" <maxzsim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs

Prompting connection str to access SQL server

Hi ,
my connection string is as per follows but how can i made it such that i
need not hard code the useid & pwd w/o having to create a user interface ?
tks & rdgsCan you please send the connection string
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs|||As far as I understand - you must use trusted connection option and depend
on account that your application work with.
Bojidar Alexandrov
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgs|||That depends just on your application you code, there are serveral ways for
doing this:
Storing the password in a external storage (evtl. with encryption)
-registry
-xml file
-active directory
...
Using Windows Authentification:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp
Using a DSN to connect with, (Has already a GUI ;-) )
But all that depends ont eh app you are coding the enviroment and so on..
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"maxzsim" <maxzsim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DD415C15-32D4-43DC-B9AC-5C44D08DC738@.microsoft.com...
> Hi ,
> my connection string is as per follows but how can i made it such that i
> need not hard code the useid & pwd w/o having to create a user interface ?
> tks & rdgssql

Prompt page in SSRS

Hi,

I checked in SQL Server Reporting Services and it has the option to create only the reports. How do I create any prompt page from SSRS? Is it possible to do it? Thank You.

Regards,

Das.

What do you mean by prompt page ? Asking for some parameters before rendering the report ? That can be done by clicking in the background of the report and navigating through report parameters.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 21, 2012

prompt for parameter in a view

I would like to create a view that will prompt the user for a parameter.
Much the same way a user can provide the paramenter for a query in Access.
Seems like it should be simple, but I don't know how to do it.
Any ideas?
Thanks
Tyler
Hi,
SQL Server will not prompt for a user input. Access will allow because it
have both Front end and back end.
Thanks
Hari
SQL Server MVP
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CA2CA968-4B15-4ED6-AECF-FE3C17C119AB@.microsoft.com...
>I would like to create a view that will prompt the user for a parameter.
> Much the same way a user can provide the paramenter for a query in Access.
> Seems like it should be simple, but I don't know how to do it.
> Any ideas?
> Thanks
> --
> Tyler

prolem with "create login from asymmetric key"

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

USE master

CREATE CERTIFICATE UnsafeSample_Certificate

ENCRYPTION BY PASSWORD = 'All you need is love'

WITH SUBJECT = 'Certificate for example_sp',

START_DATE = '20070201', EXPIRY_DATE = '21000101'

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: USE master GO CREATE CERTIFICATE UnsafeSample_Certificate ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20070201', EXPIRY_DATE = '21000101' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with "create login from asymmetric key"

Greetings...

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

--Bonnie [MSFT]

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

|||

NNTP User, I'm trying such:

USE master

CREATE CERTIFICATE UnsafeSample_Certificate

ENCRYPTION BY PASSWORD = 'All you need is love'

WITH SUBJECT = 'Certificate for example_sp',

START_DATE = '20070201', EXPIRY_DATE = '21000101'

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with "create login from asymmetric key"

Greetings...

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

--Bonnie [MSFT]

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

|||

NNTP User, I'm trying such:

USE master

CREATE CERTIFICATE UnsafeSample_Certificate

ENCRYPTION BY PASSWORD = 'All you need is love'

WITH SUBJECT = 'Certificate for example_sp',

START_DATE = '20070201', EXPIRY_DATE = '21000101'

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with &quot;create login from asymmetric key&quot;

Greetings...

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

--Bonnie [MSFT]

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

|||

NNTP User, I'm trying such:

USE master

CREATE CERTIFICATE UnsafeSample_Certificate

ENCRYPTION BY PASSWORD = 'All you need is love'

WITH SUBJECT = 'Certificate for example_sp',

START_DATE = '20070201', EXPIRY_DATE = '21000101'

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

Tuesday, March 20, 2012

Project accounting report problems

I've been trying to create a project accounting report whereby for each
project, I can see the project broken down into cost category classes and
then cost categories. For each class (rollup of the categories) and category,
the report should display the budgeted hours, budgeted fees, actual hours and
actual fees. The report should be able to be constrained by a start and end
date for the actuals. I am using Dynamics GP as the data source. I was
wondering if anyone can describe the approach I should take as I've run into
numerous problems so far, primarily with trying to get a date filter working
with the timesheet information that comprises the actuals. Thanks for the
help.I have found it easiest to first create a stored procedure that returns the
data you want to display in the report. It can have a start date and end date
parameter in it that can constrain the result set with a simple "between" sql
statement in the query.
Once you have the sproc as you like it, creating a report around it is much
simpler.
"Zack" wrote:
> I've been trying to create a project accounting report whereby for each
> project, I can see the project broken down into cost category classes and
> then cost categories. For each class (rollup of the categories) and category,
> the report should display the budgeted hours, budgeted fees, actual hours and
> actual fees. The report should be able to be constrained by a start and end
> date for the actuals. I am using Dynamics GP as the data source. I was
> wondering if anyone can describe the approach I should take as I've run into
> numerous problems so far, primarily with trying to get a date filter working
> with the timesheet information that comprises the actuals. Thanks for the
> help.

Monday, March 12, 2012

Programming Subscription Options

Hello,
We are developing our own ASP.NET 2.0 (C#) front-end for Reporting Services. We are trying to create an interface for setting up standard subscriptions that is similar to Report Manager.

On Report Manager's Report Server File Share options page, there are two options Render Format and Overwrite options that we need help with. Can we query the ReportServer and get the available Render Formats to populate a dropdownlist? Or, do we have to hard-code the values?

Same for the Overwrite options, are these hard-coded? Or, can the valid values be retreived from ReportServer?

Finally, on the Report Server E-mail setup page, there is a Priority field that we need to replicate. Again, are the valid values hard-coded into the dropdownlist or retreived from the ReportServer?

Thanks.I answered this in the newsgroup but will post my answer here as well.

You can call GetExtensionSettings to get the settings that a delivery
extension accepts. The extensions will return a populated list of renders
that it supports as well as the full set of options that it supports.

Programming Subscription Options

Hello,
We are developing our own ASP.NET 2.0 (C#) front-end for Reporting Services.
We are trying to create an interface for setting up standard subscriptions
that is similar to Report Manager.
On Report Manager's Report Server File Share options page, there are two
options Render Format and Overwrite options that we need help with. Can we
query the ReportServer and get the available Render Formats to populate a
dropdownlist? Or, do we have to hard-code the values?
Same for the Overwrite options, are these hard-coded? Or, can the valid
values be retrieved from ReportServer?
Finally, on the Report Server E-mail setup page, there is a Priority field
that we need to replicate. Again, are the valid values hard-coded into the
dropdownlist or retrieved from the ReportServer?
Thanks.You can call GetExtensionSettings to get the settings that a delivery
extension accepts. The extensions will return a populated list of renders
that it supports as well as the full set of options that it supports.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"sam" <nospam@.company.com> wrote in message
news:uDhn8VD$FHA.140@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We are developing our own ASP.NET 2.0 (C#) front-end for Reporting
> Services.
> We are trying to create an interface for setting up standard subscriptions
> that is similar to Report Manager.
> On Report Manager's Report Server File Share options page, there are two
> options Render Format and Overwrite options that we need help with. Can
> we
> query the ReportServer and get the available Render Formats to populate a
> dropdownlist? Or, do we have to hard-code the values?
> Same for the Overwrite options, are these hard-coded? Or, can the valid
> values be retrieved from ReportServer?
> Finally, on the Report Server E-mail setup page, there is a Priority field
> that we need to replicate. Again, are the valid values hard-coded into
> the
> dropdownlist or retrieved from the ReportServer?
> Thanks.
>|||Thanks Daniel!
Another quick question for you...
How does Report Manager validate that the Path is in Uniform Naming
Convention (UNC) format? More than likely, our end users will enter
something like: D:\Product\Reports, and I guess we'll need to validate it.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23wXU5iE$FHA.216@.TK2MSFTNGP15.phx.gbl...
> You can call GetExtensionSettings to get the settings that a delivery
> extension accepts. The extensions will return a populated list of renders
> that it supports as well as the full set of options that it supports.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "sam" <nospam@.company.com> wrote in message
> news:uDhn8VD$FHA.140@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > We are developing our own ASP.NET 2.0 (C#) front-end for Reporting
> > Services.
> > We are trying to create an interface for setting up standard
subscriptions
> > that is similar to Report Manager.
> >
> > On Report Manager's Report Server File Share options page, there are two
> > options Render Format and Overwrite options that we need help with. Can
> > we
> > query the ReportServer and get the available Render Formats to populate
a
> > dropdownlist? Or, do we have to hard-code the values?
> >
> > Same for the Overwrite options, are these hard-coded? Or, can the valid
> > values be retrieved from ReportServer?
> >
> > Finally, on the Report Server E-mail setup page, there is a Priority
field
> > that we need to replicate. Again, are the valid values hard-coded into
> > the
> > dropdownlist or retrieved from the ReportServer?
> >
> > Thanks.
> >
> >
>|||We don't validate all UNC paths, I believe it just checks that it starts
with \\
No real magic. :)
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"sam" <nospam@.company.com> wrote in message
news:%236XyizE$FHA.1312@.TK2MSFTNGP09.phx.gbl...
> Thanks Daniel!
> Another quick question for you...
> How does Report Manager validate that the Path is in Uniform Naming
> Convention (UNC) format? More than likely, our end users will enter
> something like: D:\Product\Reports, and I guess we'll need to validate it.
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23wXU5iE$FHA.216@.TK2MSFTNGP15.phx.gbl...
>> You can call GetExtensionSettings to get the settings that a delivery
>> extension accepts. The extensions will return a populated list of
>> renders
>> that it supports as well as the full set of options that it supports.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "sam" <nospam@.company.com> wrote in message
>> news:uDhn8VD$FHA.140@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> > We are developing our own ASP.NET 2.0 (C#) front-end for Reporting
>> > Services.
>> > We are trying to create an interface for setting up standard
> subscriptions
>> > that is similar to Report Manager.
>> >
>> > On Report Manager's Report Server File Share options page, there are
>> > two
>> > options Render Format and Overwrite options that we need help with.
>> > Can
>> > we
>> > query the ReportServer and get the available Render Formats to populate
> a
>> > dropdownlist? Or, do we have to hard-code the values?
>> >
>> > Same for the Overwrite options, are these hard-coded? Or, can the
>> > valid
>> > values be retrieved from ReportServer?
>> >
>> > Finally, on the Report Server E-mail setup page, there is a Priority
> field
>> > that we need to replicate. Again, are the valid values hard-coded into
>> > the
>> > dropdownlist or retrieved from the ReportServer?
>> >
>> > Thanks.
>> >
>> >
>>
>

Programming SQL Express

Hey, i have an installer that places ms sql express onto a machine. thats is fine. i want to create a post setup application that runs after ms sql server is installed. what for example i want to do is be able to create a user and have the DB allow remote connections. i can provide instructions for a 'peon' to make the required post setup changes but i want to see if i can cover that with an application. thanks

Charles Rezsonya

Write this as T-SQl and then either write code that calls into the database and execute the script(call this code from your installer), or include a .sql file with the script as part of your installation and then call that through a call to sqlcmd to execute.

Either way I recomend having a copy of the script as part of your install so that if something goes wrong you can at least walk a user through running the script by hand.

|||Hi,

These things can be easily changed using the SMO namespace:

using Microsoft.SqlServer.Management.Smo.Wmi;

(....)

ManagedComputer comp = new ManagedComputer(".");

comp.ClientProtocols["tcp"].IsEnabled = true;
(...)

Remeber to restart the service after changing this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

amazing. i will certainly be trying that monday. Thanks. Keep an eye on this post incase i have add'l questions?

tia

Charles R

|||Sure, everyone who signed in for alerts (its by default in the forums will get an IM Alert and additional the email alert ( depending on the configuration)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

hey, anyway you can show me a script that would change the server to allow tcp remote connections? what application do i run to process the script?

|||ok. i'm not finding the dll to include the smo namespace using 'using' in the sql sdk directory. and when i go to the studio and go to projects -> add reference its not listed "microsoft.sqlserver.etc etc"....... where can i download this dll? any add'l source on connecting examples?|||actually i found it on one of my other servers i installed the sdk to. so i'm including it. next really is just to get it going now :)|||

damn, but i am having a problem. i am using visual studio 2003 and when i go to about it says my .net framework is 1.1. is that a problem for the following message?

a referance to 'c:\program file\microsoft sql server\90\sdk\microsoft.sqlserver.sqlenum.dll' could not be added. this is not a valid or COM component. only assemblies with extension 'dll' and com components can be referenced. please make sure that the file is accessible, and that it is a valid assembly or com component.

|||is it possible to do this with visual studio 2003?|||

I don't believe so.

SMO is a SQL 2005 and .NET Framework 2.0 technology. I think that VS 2003 is locked into NetFx1.1. (Any VS experts can feel free to correct me.) You have a couple alternatives:

Move up to VS 2005, you can get the VS Express Editions for free if you like. They have some reduced functionality.

Programming SQL Express

Charles Rezsonya

Either way I recomend having a copy of the script as part of your install so that if something goes wrong you can at least walk a user through running the script by hand.

|||Hi,

These things can be easily changed using the SMO namespace:

using Microsoft.SqlServer.Management.Smo.Wmi;

(....)

ManagedComputer comp = new ManagedComputer(".");

comp.ClientProtocols["tcp"].IsEnabled = true;
(...)

Remeber to restart the service after changing this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

amazing. i will certainly be trying that monday. Thanks. Keep an eye on this post incase i have add'l questions?

tia

Charles R

|||Sure, everyone who signed in for alerts (its by default in the forums will get an IM Alert and additional the email alert ( depending on the configuration)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

hey, anyway you can show me a script that would change the server to allow tcp remote connections? what application do i run to process the script?

damn, but i am having a problem. i am using visual studio 2003 and when i go to about it says my .net framework is 1.1. is that a problem for the following message?

|||is it possible to do this with visual studio 2003?|||

I don't believe so.

SMO is a SQL 2005 and .NET Framework 2.0 technology. I think that VS 2003 is locked into NetFx1.1. (Any VS experts can feel free to correct me.) You have a couple alternatives:

Move up to VS 2005, you can get the VS Express Editions for free if you like. They have some reduced functionality.

Programming databases

I need to create a database with one table and its column names.
I got this script from the query analyzer templates as a database basic template:
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'<database_name, sysname, test_db>')
DROP DATABASE <database_name, sysname, test_db>
GO

CREATE DATABASE <database_name, sysname, test_db>
GO

I am not sure what it all means, what I need to do it name the database a certain name each time. and then create one table with 6 column names.

can someone give me guidance please...the script is just trying to make sure there is no other db with that name and drop the db one if there is one..

hth|||Not really, heres what I got...


Declare @.name Char(10)
Set @.name = 'test'
CREATE DATABASE [@.name] ON (NAME = N'' & @.name & '_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'' & @.name & '_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'@.name', N'autoclose', N'false'
GOexec sp_dboption N'@.name', N'bulkcopy', N'false'
GO
and so on...

Getting the variable '@.name' to go where I want it is where my problem occurs|||its more like

CREATE DATABASE+ @.name+ ON (NAME = N'' ......

hth|||exec sp_dboption N'hol', N'read only', N'false'
GO

What I need to do is pass, from the web page, a variable called @.name
So where it shows N'hol', the variable passed from the web form textbox whould go where 'hol' is.
N'' + @.name + '', does not work. what is the proper sytax for this?

E|||I suspect you don't understand what the 'N' bit means? (sorry if I'm wrong) You could split the batch up and use proper params. Although I'm a tad concerned that a web users is allowed to issue such commands, but hey I'm sure you've taken care of the security aspect.|||absolutely, what I am trying to do is automate as much as possible. this will be a hidden command in which the user can maintain an image database just by naming it where the form pulls the name from a textbox. Administration only should have access to this sort of command, so its all good with security.
SO tell me more about this spliting the batch up and the use of proper param names you speak of.. No, I am not sure of what the 'N' bit means either. Can you help?|||Params are good because you can literally use
exec spbla @.myParam1, @.myParam2
and you won't have to worry about all the horrible string concat problems.

Lookup SQLParameter.
Plus if your command type is stored procedure you can call spblaa directly!

I think you'll have to split up the batch cause normally client exec'd code doesn't like the "GO"s, although I've not actually tried it with the ADO.net.

I'll whisper the following, you don't really need the 'N' bit. Its just saying that the text is going to be unicode. You rarely have to worry about that, certainly not for params (i.e you don't prefix a param with 'N'). I didn't say that ok ;)

Programmertically create and execute stored procedure in SMO

Hi all,

I need to programmertically create and execute stored procedure in SMO, without registering it on the database. I also need to be able to load a file containing a stored procedure and execute it, using SMO.

Can someone show me how? A C# sample would be greatly appreciated.

Thanks in advance.

Hi,

a simple sample would be:

StoredProcedure sp = new StoredProcedure("SomeDatabase","usp_Somesp","SomeSchema");

sp.TextBody = "SELECT 'SomeData'";

Server s = new Server("SomeServer");

s.Databases["SomeDatabase"].StoredProcedures.Add(sp);

s.ConnectionContext.ExecuteNonQuery("usp_Somesp");

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

I appreciate you help, but this is the error I get:

Error 1 'Microsoft.SqlServer.Management.Smo.StoredProcedureCollection' does not contain a definition for 'Add'

|||

Good morning ( for me 9:34 )

Look at this link http://msdn2.microsoft.com/en-us/library/ms162553.aspx

The Add is automatic when you use the Create method if you use the constructor

sp = new StoredProcedure(DataBaseName,StoredProcedureName)

Excuse me for my english

Have a nice day

|||Thank you very much.

Friday, March 9, 2012

Programmatically developing an entire package..

Hi all,

I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.

Thanks,

Prithvi.

This is the code for creating connections and adding dataflow task. I need to add a script component transformation, but initially I wanted to see if a one-to-one mapping works fine.

Public Sub CreateConnections()

'Add the OLE DB and Flat File Connection Managers

Console.WriteLine("Creating the MyOLEDBConnection")

Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")

cnOLEDB.Name = "MyOLEDBConnection"

cnOLEDB.ConnectionString = "<connection string>"

Console.WriteLine("Creating the MyFlatFileConnection")

Dim cnFile As ConnectionManager = myPackage.Connections.Add("FLATFILE")

cnFile.Name = "MyFlatFileConnection"

cnFile.Properties("ConnectionString").SetValue(cnFile, "C:\sample.csv")

cnFile.Properties("Format").SetValue(cnFile, "Delimited")

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)

cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("TextQualifier").SetValue(cnFile, """")

End Sub

Public Sub AddDataFlowTask()

'Add a Data Flow Task

Console.WriteLine("Adding a Data Flow Task")

Dim e As Executable = myPackage.Executables.Add("DTS.Pipeline")

Dim taskDF As TaskHost = CType(e, TaskHost)

taskDF.Name = "DataFlow"

Dim DTP As MainPipe

DTP = CType(taskDF.InnerObject, MainPipe)

' Add the FLAT FILE Source

Console.WriteLine("Adding the File Source")

Dim DFSource As IDTSComponentMetaData90

DFSource = DTP.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource.1"

DFSource.Name = "FlatFileSource"

' Connect, populate the Input collections and disconnect

Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()

SourceInst.ProvideComponentProperties()

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyFlatFileConnection").ID

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyFlatFileConnection"))

SourceInst.AcquireConnections(vbNull)

SourceInst.ReinitializeMetaData()

SourceInst.ReleaseConnections()

'checking If the external metadata columns are available as source output columns and the output is 0 which means no columns are being passed as output from source

Dim column As IDTSOutputColumn90

Try

Console.WriteLine("connection name: " & DFSource.RuntimeConnectionCollection(0).Name.ToString)

Console.WriteLine("output collection name: " & DFSource.OutputCollection(0).Name)

Console.WriteLine("output collection description :" & DFSource.OutputCollection(0).Description)

Console.WriteLine("source output columns count :" & DFSource.OutputCollection(0).OutputColumnCollection.Count.ToString

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message.ToString)

End Try

'tried to print col names but it does not print any

For Each column In DFSource.OutputCollection(0).OutputColumnCollection

Console.WriteLine(column.Name.ToString)

Console.WriteLine(column.DataType.ToString)

' Add the OLEDB Destination

Console.WriteLine("Adding OLEDB Destination")

Dim DFDestination As IDTSComponentMetaData90

DFDestination = DTP.ComponentMetaDataCollection.New()

DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFDestination.Name = "OLEDBDestination"

' Create an instance of the component

Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()

DestInst.ProvideComponentProperties()

If DFDestination.RuntimeConnectionCollection.Count > 0 Then

DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyOLEDBConnection").ID

DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyOLEDBConnection"))

End If

DestInst.SetComponentProperty("AccessMode", 0)

DestInst.SetComponentProperty("OpenRowset", "tempSSIS")

DestInst.SetComponentProperty("FastLoadKeepNulls", True)

' Map a connection between the source and destination

Dim path As IDTSPath90 = DTP.PathCollection.New()

path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))

Dim InColumns As IDTSVirtualInputColumnCollection90 = DFDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection()

' the number of input columns to destination is zero

Console.WriteLine("input columns : " & InColumns.Count.ToString)

Try

DestInst.AcquireConnections(vbNull)

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message)

End Try

DestInst.ReinitializeMetaData()

'Console.WriteLine("input columns : " & DFDestination.InputCollection(0).InputColumnCollection.Count.ToString)

For Each input As IDTSInput90 In DFDestination.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

Console.WriteLine(vColumn.Name.ToString)

Console.WriteLine(vColumn.DataType)

DestInst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In DFDestination.InputCollection(0).InputColumnCollection

exCol = DFDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

DestInst.MapInputColumn(DFDestination.InputCollection(0).ID, col.ID, exCol.ID)

DestInst.ReleaseConnections()

End Sub

Please see if i did wrong any where(which I always happen to do). But based on the msdn material, the above code should work, i guess.

Wednesday, March 28, 2012

Property AnsiNullsStatus is not available for UserDefinedFunction...

Monday, March 26, 2012

Properties Expressions

Proper use of Event Notifications

Friday, March 23, 2012

Prompting connection str to access SQL server

Prompting connection str to access SQL server

Prompting connection str to access SQL server

Prompt page in SSRS

Wednesday, March 21, 2012

prompt for parameter in a view

prolem with "create login from asymmetric key"

prolem with "create login from asymmetric key"

prolem with "create login from asymmetric key"

prolem with &quot;create login from asymmetric key&quot;

Tuesday, March 20, 2012

Project accounting report problems

Monday, March 12, 2012

Programming Subscription Options

Programming Subscription Options

Programming SQL Express

Programming SQL Express

Programming databases

Programmertically create and execute stored procedure in SMO

Friday, March 9, 2012

Programmatically developing an entire package..

Program Connection

Blog Archive

About Me