SQL,access denied: accessing

Showing posts with label accessing. Show all posts

Saturday, February 25, 2012

Account to access SQL

What is the best way of accessing a sql server on a live server? shall I use Integrated windows or use a special user account? If I use a user account, what are the needed priviledges to give it?

thank you

Well, the real answers to those questions are: "It depends." Froma practical standpoint, choosing Windows authentication vs. SQLauthentication is going to come down to what your needs are and what'sgoing to provide the smallest attack surface whilst meeting thoseneeds.
The needed privileges are also going to vary from application toapplication. Personally, I shoot for not allowing anything butexecute permissions on the stored procedures that the app needs tofunction, and nothing else. That gives me a stong, well-definedAPI for the database that allows me to control (to an extent) how thedatabase is used. Again, YMMV depending on the needs of yourapplication.

Friday, February 24, 2012

Accessing WMI using sp_OACreate

Does nayone have any insight into how to access WMI using sp_OACreate?You might want to look at Dan's reply to your original post.
Andrew J. Kelly SQL MVP
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:72B28D56-DB4D-451E-921C-E0F8FF1144BA@.microsoft.com...
> Does nayone have any insight into how to access WMI using sp_OACreate?

Accessing Windows Users assigned to a database role

Is there a way to find a list of Windows User accounts that are directly or indirectly (through Windows Group membership) assigned to a database role?
I could put work in to CLR programming or using a Linked Server to Active Directory, but if there is a sys.* view available that can provide me this information directly it would be much easier.
I'll be looking into this further myself anyway and posting an answer if I can find one, but if anyone has suggestions... well, thanks!

Hi,

guess not, but if you have one ready, feel free to post it to me, I will bring it online on my soon launched site www.sqlserver2005.de

HTH, jens Suessmeyer.

Accessing which data caused a trigger to fire

Hi.

I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?

Best Regards
Jens Christian Andersen.There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:

INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted

In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"jcandersen" <jca@.dse.dkwrote in message
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...

Quote:

Originally Posted by

Hi.
>
I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?
>
Best Regards
Jens Christian Andersen.
>

|||On 28 Feb., 14:07, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:
>
INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted
>
In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"jcandersen" <j...@.dse.dkwrote in message
>
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...
>

Quote:

Originally Posted by

Hi.

Quote:

Originally Posted by

Quote:

Originally Posted by

Best Regards
Jens Christian Andersen.

Thanks, this is just what I needed.

Accessing webservice w/ custom dll - WebPermission request fails

I'm running rs2005 sp2, and the dll was made with .net 1.1. Before, there were no issues with this version crossing of .net 1.1 and 2.0.

In the RS IDE, the webservice call works fine. I've read that this execution is done with full trust - so I've tried to tackle the CAS issue.

When I try to call the webservice from the dll, I catch the exception, and it's:
Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Now what confuses me is, why is the version of WebPermission 2.0 when the dll is 1.1? Maybe there is translation done and the dll is really run w/ the 2.0 framework?

Anyways, this I've done are:

* Added this right before my webservice call. Is something missing?
// We need to ensure that this library is permitted to access the webservice url
// http://support.microsoft.com/default.aspx?scid=kb;en-us;842419
System.Text.RegularExpressions.Regex urlRegEx = new System.Text.RegularExpressions.Regex(@."http://server/.*");
System.Net.WebPermission p = new System.Net.WebPermission(NetworkAccess.Connect, urlRegEx);
p.Assert();

* Gave the 'All_Code' group permission of full trust in both .net 1.1 and 2.0 configurations to see if it helped - it did not, and I checked the report after I did an iisreset. This should have done it!!

* I also tried changing: rsmgrpolicy.config and rssrvpolicy.config

and updated these lines to have version 2.0.0.0:
<SecurityClass Name="SecurityPermission" Description="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<SecurityClass Name="WebPermission" Description="System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

iisreset was done, and the code executed was still complaining about the permission request failing for WebPermission.

* These blocks were added to rsmgrpolicy.config and rssrvpolicy.config, in respective sections:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="CompanyPermissionSet"
Name="CompanyRS"
Description="">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program Files/Microsoft SQL Server/MSSQL.3/Reporting Services/ReportServer/bin/Company.RS.dll"/>
</CodeGroup>

The only way I got a webservice call to work was with these settings:

rssrvpolicy.config:

and

And in the assembly (VB.net) code:

<Assembly: AllowPartiallyTrustedCallers()>

and right before the actual webservice call:

p = New System.Net.WebPermission(Permissions.PermissionState.Unrestricted)

p.Assert()

Took me just over half a day to get it right..

Perhaps the code could be a bit more secure by specifying a RegEx for the WebPermission, but I'll settle for this.

-Ruizzie

Accessing webservice w/ custom dll - WebPermission request fails

I'm running rs2005 sp2, and the dll was made with .net 1.1. Before, there were no issues with this version crossing of .net 1.1 and 2.0.

In the RS IDE, the webservice call works fine. I've read that this execution is done with full trust - so I've tried to tackle the CAS issue.

Now what confuses me is, why is the version of WebPermission 2.0 when the dll is 1.1? Maybe there is translation done and the dll is really run w/ the 2.0 framework?

Anyways, this I've done are:

* I also tried changing: rsmgrpolicy.config and rssrvpolicy.config

iisreset was done, and the code executed was still complaining about the permission request failing for WebPermission.

The only way I got a webservice call to work was with these settings:

rssrvpolicy.config:

and

And in the assembly (VB.net) code:

<Assembly: AllowPartiallyTrustedCallers()>

and right before the actual webservice call:

p = New System.Net.WebPermission(Permissions.PermissionState.Unrestricted)

p.Assert()

Took me just over half a day to get it right..

Perhaps the code could be a bit more secure by specifying a RegEx for the WebPermission, but I'll settle for this.

-Ruizzie

Accessing web.config from stored procedure

I want to access a key from appSettings section of web.config.

I have the number of days allowed for a user to activate his/her account as a key in appSettings.

I have a maintenance procedure to delete all accounts that are not activated before that many days.

In this context, i have to access web.config from stored procedure. The procedure will be scheduled as a JOB in sql server.

Thanks.

Hi,

Are you having problem to access the appSettings secion in the web.config file? If so, you can use ConfigurationManager.AppSettings property to achieve that.

Here is a link for your reference.

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.appsettings.aspx

If your stored procedure, that is running as a job, is trying to access the value, you might need to save the value to a database table or somewhere. Then your stored procedure can get that. It cannot get the web.config value directly.

|||

Thanks Kevin.

My requirement is to access appSettings from a stored procedure of SQL Server. So that whenever I change web.config, the procedure must

automatically access the new value.

Is there any way to access any XML file from a stored procedure. May be that could solve my problem. I heard about XML support in SQL Server. What does that do?

Thanks again.

Srikanth.

|||

Hi Srikanth,

Teh SQL Server 2005 support for Xml is for Xml column type and Xml manipulation. It stays in the database level, but not for reading an external file.

A traditional stored procedure does not read from a file. In this case, I think you have 2 options.

1. Make your app write that appSetting to a certain place in the database timely. The stored procedure can get that as a parameter.

2. Write a CLR stored procedure. Since SQL Server 2005 supports running .NET code, you can write a method and put it in assembly. Each time, you can have the assembly read from certain file, parse the xml and get the setting value.

There are many articles talking about how to create a CLR stored procedure. Here are some of them.

http://msdn2.microsoft.com/en-us/library/ms131094.aspx
http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx

Accessing Virtual Server Names

Hello,
We are moving from a 2 node, Acitive-Passive cluster to a 4 node cluster; 3
Active 1 passive.
We have 3 Virtual Servers. We have defaulted one virtual server per active
node.
I want to be able to run a different version of our software on each virtual
server; thus three folders one for each for the install. I would like to
update the path variable to point to a different folder based on the virtual
server name.
Can't seem to find out how to get the name of the virtual server the user
logged into.
Or, am I taking the completely wrong approach?
Thanks for any help.
Evan R.
You seem to have your terms and definitions mixed up.
Active-Active-Passive-etc nomenclature is misleading and does not reflect
how clustering in SQL 2000 works.
First, instances are not associated with particular nodes as was the case in
SQL 7.0. The only exception is certain tasks (Service packs, hotfixes, disk
changes) must be done from the node currently hosting the virtual server.
You can have sixteen instances per cluster, regardless of the number of host
nodes in the cluster. SQL 2000 supports up to 4 nodes per cluster. You can
have only one default instance per cluster. I suggest using only named
instances on a cluster for consistancy. Since each instance appears as a
separate server on the network, a query of the @.@.servername global variable
will easily distinguish which server a user is connected to.
Geoff N. Hiten
Microsoft SQL Server MVP
"BeenThereGotLost" <BeenThereGotLost@.discussions.microsoft.com> wrote in
message news:54DDC256-9949-421F-8240-82C54A882B1C@.microsoft.com...
> Hello,
> We are moving from a 2 node, Acitive-Passive cluster to a 4 node cluster;
> 3
> Active 1 passive.
> We have 3 Virtual Servers. We have defaulted one virtual server per active
> node.
> I want to be able to run a different version of our software on each
> virtual
> server; thus three folders one for each for the install. I would like to
> update the path variable to point to a different folder based on the
> virtual
> server name.
> Can't seem to find out how to get the name of the virtual server the user
> logged into.
> Or, am I taking the completely wrong approach?
> Thanks for any help.
> Evan R.
|||Thanks Geoff. Hopefully this will clear up my question.
We stage equipment & databases for our customers. The customer's database
replicates to a database on a named SQL instance running on a virtual server
in the cluster.
To stage the equipment our manufacturing folks have to log into the virtual
server and run some command line utilities. The utilities build the
replication, accounts, etc. I would like to modify the path in the
environment based on the name of the virtual server. This would allow me to
have different versions of the software for different virtual servers. I am
constraining the problem by putting only one instance of SQL on each virtual
server.
From a cmd shell, I need the name of the virtual server the manufacturing
person has logged into. I'm willing to use command line utility, Script, or
API to access the virtual server name.
"Geoff N. Hiten" wrote:

> You seem to have your terms and definitions mixed up.
> Active-Active-Passive-etc nomenclature is misleading and does not reflect
> how clustering in SQL 2000 works.
> First, instances are not associated with particular nodes as was the case in
> SQL 7.0. The only exception is certain tasks (Service packs, hotfixes, disk
> changes) must be done from the node currently hosting the virtual server.
> You can have sixteen instances per cluster, regardless of the number of host
> nodes in the cluster. SQL 2000 supports up to 4 nodes per cluster. You can
> have only one default instance per cluster. I suggest using only named
> instances on a cluster for consistancy. Since each instance appears as a
> separate server on the network, a query of the @.@.servername global variable
> will easily distinguish which server a user is connected to.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "BeenThereGotLost" <BeenThereGotLost@.discussions.microsoft.com> wrote in
> message news:54DDC256-9949-421F-8240-82C54A882B1C@.microsoft.com...
>
>

Accessing Views via ODBC from MSAccess

Hello,

This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.

The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.

I then find out there is no data in the views and so the user would not have been able view anything anyway.

So what I want to do is remove "public" from the views permissions, is this possible?

Thanks for any help,

Tyrone

Hi,

naviagte in SSMS to the database open > Security > Roles > Database Roles > public > Right click Properties , Remove the permission from the public group to view the schema / Object.

HTH, Jens Suessmeyer.

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

Hi,

Thanks for the reply... now the strnage big... when I go to the public properties I cannot really find anything to remove. Nothing is ticked, any ideas?

Tyrone

Accessing Views from Stored Procedures

Hi,

I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?

Thanks.

CREATEPROCEDURE [dbo].[usp_From_A_View]

BEGIN

SETNOCOUNTON;

SELECT*FROM dbo.View_1

--retrieve from a view

END

|||

Thanks for the code.

I know we can't use parameters with Views, so in that case how would I go about implementing the following stored procedure if I had to split it up into a view and a sproc?

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[GetProjectInfo]
( @.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL, @.ProjTypevarchar(20) =NULL,
 @.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)AS
BEGIN
SET NOCOUNT ON
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjIDEND )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID
END

TIA.

|||

Create 3 views as following:

Create View v_Projectasselect *from tbl_Project GO-----------Create View v_ProjectManagerasselect *from tbl_ProjectManagerGO-----------create view v_ProjectStatusasselect *from tbl_ProjectStatus GO-----------

and here is the stored procedure that used those created views:

ALTER PROCEDURE [dbo].[GetProjectInfo]
@.ProjectTitlevarchar(300) ,
@.ProjectManagerIDint ,
@.DeptCodevarchar(20) ,
@.ProjTypevarchar(20) ,
@.ProjIDvarchar(50) ,
@.DateRequesteddatetime ,
@.DueDatedatetime ,
@.ProjectStatusIDint

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM vProject P, vProjectManager M, vProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjID END )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID

Hope this will help you.

Good luck.

|||

That's it!? That's pretty simple.

Ok. Great.

Thank you (both responses).

Accessing varibles inside the ScriptTask

Hi All,

In one of my ScriptTasks, I instantiate FileSystemWatcher class and set events for it. This has been done inside the Main() method. I can access all the varibles declared in the SSIS package inside the Main() method (by using Dts.Variables("").Value) but none can be accessed inside the event methods. One possible reason for this can be, events might be running in different threads.

Now my question is, How the varibles can be accessed inside the event methods.

Hope someone can give me a solution. Appricate all your solutions.
Thanks

Hi,

You might want to try using Daniel Read's method of accessing variables here:

http://www.developerdotstar.com/community/node/512

If that doesn't work, then it must be a thread-locking problem, although I can't see how that could really happen.

HTH|||

hi,

How odd, I've tried this snippet of code and it works fine:

Public Sub Main()

uno()

Dts.TaskResult = Dts.Results.Success

End Sub

Public Function uno()

MsgBox(Dts.Variables("RutaFicheroCarga").Value)

End Function

|||

Hi,

I tried the Daniel's method but it didn't work for me. The actual code looks like below.... to show you the exact problem.

Public Sub Main()
Dim watcher As New FileSystemWatcher()
......
......
AddHandler watcher.Created, AddressOf OnChanged
......
......
Dts.TaskResult = Dts.Results.Success
End Sub

Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
Try
MsgBox(Dts.Variables("MyVariable").Value.ToString()) ' this gives the error "Object reference not set to an instance of an object"
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub

Looking forward to seeeing a solution for this.

Thanks

|||

SL Coder wrote:

Hi,
I tried the Daniel's method but it didn't work for me. The actual code looks like below.... to show you the exact problem.
Public Sub Main()
Dim watcher As New FileSystemWatcher()
......
......
AddHandler watcher.Created, AddressOf OnChanged
......
......
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
Try
MsgBox(Dts.Variables("MyVariable").Value.ToString()) ' this gives the error "Object reference not set to an instance of an object"
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub
Looking forward to seeeing a solution for this.
Thanks

What is the data type of MyVariable? You may need to convert it to the Object data type.|||Have you tried changing the Private modifier on your sub to Public?

Also, you have to make sure that your variable is indeed instantiated. What is the type of your variable? If it is a reference type, it has to be somehow somewhere declared as New.|||

It is a string type variable and it can be accessed in Main() method without any problem. I made the event method as public but no luck. Also note that variable has been added to SSIS with scope of the "project". This problem not just for this variable, No variables can be accessed through the event method.

Any suggestions?

Thanks

|||Hmmm...

Dim watcher As New FileSystemWatcher()

Shouldn't it be:

Dim WithEvents watcher as New FileSystemWatcher()|||

Not sure why it does not work, but I have found writing custom tasks is often easier when trying anything other than simple stuff, if only because I can choose my language, and the IDE is much better. Not much use, but perhaps this would be -

File Watcher Task (http://www.sqlis.com/23.aspx)

|||

Hi Jon, Darren

Thanks for reply. I tried with WithEvents but it didn't work again. I have tried FileWatcherTask (sqlis) before but one biggest problem with it is, it stops listening once the first file is detected.

l'll try to make a custom task and see.

Thanks

Accessing Variables in SSIS code

I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?

Please let me know if theres a way to access SSIS variables.

Thanks,

Vipul

To access the variables in code u need to use the Dispenser class given in Dts

Ex.

Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("<SSISVariable name>", vars)
then use the value like this

vars("<SSISVariable name>").Value

if you want to modify the variable then lock the variable for Write like this

Dts.VariableDispenser.LockOneForWrite("<SSISVariable name>", vars)

Cheers

Atul

|||Custom data flow components, also known as pipeline components, inherit from the PipelineComponent class. This exposes a public read-only property called VariableDispenser, so you can access this in your own methiods, or those you override from the base class, PipelineComponent. This gives you access to the variable dispenser to read or write variables as you desire.|||

Hi Atul

Thanks for the reply.

After making use of VaribleDispenser i am able to get System variables. But when i am trying to access User variables, i m getting this error:

Error: 0xC0047062 at Data Flow Task, i2ADI [91672]: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to lock variable "User::sourceId" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

> System.Runtime.InteropServices.COMException (0xC0010001): Failed to lock variable "User::sourceId" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

The only difference which i could figure out was that System vars are defined at Package level scope, while the user variables are defined at Data Task Flow level. Can this be the issue? Please let me know if you have some other pointers..

Thanks,

Vipul

|||

Hi Vipul:

A couple of things:

My experience has been that variables are scoped to the object that is selected when you add the variable to the project. If you happened to have a Data Task selected when you add a variable, it's scope will be that Data Task. You can most definately create variables that are scoped to the entire package -- just make sure to click anywhere on the white surface in the Control Flow outside of any task object (this action should de-select any selected object) before adding a variable.

You can verify a variable's scope in the Variables dialog. The Scope column contains either "Package" or the name of a task object (if the variable is scoped to a single object).

You can scope variables to a container, such as a ForEach Loop or Sequence Container. Then, all the tasks within the container "see" the variable.

Variable names are case-sensitive. In your example, you refer to "User::sourceId" -- you must have a variable named "sourceId", and not "SourceID" or any of a zillion different ways to case the name.

The code suggested by Atul should work for you.

I always use the "ReadVariable" and "WriteVariable" functions presented by Daniel Read in his excellent article:

http://www.developerdotstar.com/community/node/512

Just some thoughts.

|||

Hi Mike:

Thanks for the reply. This is the my part of code which i m using.

Microsoft.SqlServer.Dts.Runtime.Package pkg;

Variables sourceIdVar = null;

Microsoft.SqlServer.Dts.Runtime.VariableDispenser vd;

pkg = new Microsoft.SqlServer.Dts.Runtime.Package();

vd = pkg.VariableDispenser;

vd.LockForRead("System::PackageName");

vd.LockForRead("User::sourceId"); // <- accessing this variable is throwing exception

vd.GetVariables(ref sourceIdVar);

foreach (Microsoft.SqlServer.Dts.Runtime.Variable myVar in sourceIdVar)

{

Console.WriteLine("Name : " + myVar.Name);

Console.WriteLine("Description : " + myVar.Description);

}

Let me know your views on this part of code.

Thanks,

Vipul

|||

Hi Mike:

I was able to solve it. Code correction:

IDTSVariables90 variables = null;

this.VariableDispenser.LockForRead("User::dimSrcId");

this.VariableDispenser.GetVariables(out variables);

dimSrcId = variables["User::dimSrcId"].Value.ToString();

variables.Unlock();

Thanks for your help.

Vipul

|||

Hello

if i need to show all system variable in me Custom component what should i do

the code :

Dim Var As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForWrite("System::StartTime")

Me.VariableDispenser.GetVariables(Var)

Var.Unlock()

Accessing Variables in SSIS code

I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?

Please let me know if theres a way to access SSIS variables.

Thanks,

Vipul

To access the variables in code u need to use the Dispenser class given in Dts

Ex.

Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("<SSISVariable name>", vars)
then use the value like this

vars("<SSISVariable name>").Value

if you want to modify the variable then lock the variable for Write like this

Dts.VariableDispenser.LockOneForWrite("<SSISVariable name>", vars)

Cheers

Atul

Hi Atul

Thanks for the reply.

After making use of VaribleDispenser i am able to get System variables. But when i am trying to access User variables, i m getting this error:

Thanks,

Vipul

|||

Hi Vipul:

A couple of things:

You can verify a variable's scope in the Variables dialog. The Scope column contains either "Package" or the name of a task object (if the variable is scoped to a single object).

You can scope variables to a container, such as a ForEach Loop or Sequence Container. Then, all the tasks within the container "see" the variable.

Variable names are case-sensitive. In your example, you refer to "User::sourceId" -- you must have a variable named "sourceId", and not "SourceID" or any of a zillion different ways to case the name.

The code suggested by Atul should work for you.

I always use the "ReadVariable" and "WriteVariable" functions presented by Daniel Read in his excellent article:

http://www.developerdotstar.com/community/node/512

Just some thoughts.

|||

Hi Mike:

Thanks for the reply. This is the my part of code which i m using.

Microsoft.SqlServer.Dts.Runtime.Package pkg;

Variables sourceIdVar = null;

Microsoft.SqlServer.Dts.Runtime.VariableDispenser vd;

pkg = new Microsoft.SqlServer.Dts.Runtime.Package();

vd = pkg.VariableDispenser;

vd.LockForRead("System::PackageName");

vd.LockForRead("User::sourceId"); // <- accessing this variable is throwing exception

vd.GetVariables(ref sourceIdVar);

foreach (Microsoft.SqlServer.Dts.Runtime.Variable myVar in sourceIdVar)

{

Console.WriteLine("Name : " + myVar.Name);

Console.WriteLine("Description : " + myVar.Description);

}

Let me know your views on this part of code.

Thanks,

Vipul

|||

Hi Mike:

I was able to solve it. Code correction:

IDTSVariables90 variables = null;

this.VariableDispenser.LockForRead("User::dimSrcId");

this.VariableDispenser.GetVariables(out variables);

dimSrcId = variables["User::dimSrcId"].Value.ToString();

variables.Unlock();

Thanks for your help.

Vipul

|||

Hello

if i need to show all system variable in me Custom component what should i do

the code :

Dim Var As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForWrite("System::StartTime")

Me.VariableDispenser.GetVariables(Var)

Var.Unlock()

Accessing variable inside Script Task

Hi,

I have a variable in SSIS that I want to access inside the Script Task. I assigned the variable in the ReadOnlyVariables in the Script Task property. How do I access it?

cherriesh

Try

Dim Avariable As Variables
Dts.VariableDispenser.LockForRead("YourPackageVariable")
Dts.VariableDispenser.GetVariables(Avariable)

see

Accessing Values Parameter Values from another report

Hi, How can I display a value of a report parameter from one report into a textbox on another report?

If you are calling the 2nd report from the first, setup a parameter in the 2nd report, and pass that value to it from the first report. You can set this up in the navigation window.

BobP

Accessing value from SqlDataSource

I have a SqlDataSource that returns a list of companies and their details by ProductID. It also returns the name of the product associated with the ProductID as the final column (which means it appears for every record returned). I already have a way of determining how many rows were returned, and use that information in a label to say "Your search has returnedx records".

protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
if (RecordCount == 0)
{ lblRecordCount.Text = "No Records found"; }
else
{
if (RecordCount == 1)
{ lblRecordCount.Text = "Your search returned 1 record"; }
else
{ lblRecordCount.Text = "Your search returned " + RecordCount + " records"; }
}
string ProductName;
}

How can I access the ProductName value so that I can extend the label text to say "Your search has returnedx records for <ProductName>" ?

I found a way to do what I wanted. I changed the label to a literal control (for display purposes) and accessed the ProductName value in the RowDataBound event. I then applied it to another literal. My first go resulted in the ProductName appearing as many times as there were rows, so I checked to see if the Literal.Text had already been assigned. So now my code looks like this:

protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
if (RecordCount == 0)
{ ltRecordCount.Text = "No Records found"; }
else
{
if (RecordCount == 1)
{ ltRecordCount.Text = "1 supplier"; }
else
{ ltRecordCount.Text = "" + RecordCount + " suppliers"; }

}

}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string pName = DataBinder.Eval(e.Row.DataItem, "ProductName").ToString();
if (ltProductName.Text == "")
{
ltProductName.Text = " of " + pName + "";
}
}
}

Accessing Users & Members in Active Directory via Linked Server

Hi,

I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.

I can get the users. I can get the groups.... individually.

I can't get the info of what user is a member of or who are members of a group.

Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)

I want to load this data into tables for reporting in my Data Warehouse.

Cheers

Chris

Take a look at the following API:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netgroupgetusers.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netlocalgroupgetmembers.asp

Follow the links from the bottom of the articles for other related API.

Hope this helps.

Laurentiu

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Saturday, February 25, 2012

Account to access SQL

Friday, February 24, 2012

Accessing WMI using sp_OACreate

Accessing Windows Users assigned to a database role

Accessing which data caused a trigger to fire

Accessing webservice w/ custom dll - WebPermission request fails

Accessing webservice w/ custom dll - WebPermission request fails

Accessing web.config from stored procedure

Accessing Virtual Server Names

Accessing Views via ODBC from MSAccess

Accessing Views from Stored Procedures

Accessing varibles inside the ScriptTask

Accessing Variables in SSIS code

Accessing Variables in SSIS code

Accessing variable inside Script Task

Accessing Values Parameter Values from another report

Accessing value from SqlDataSource

Accessing Users & Members in Active Directory via Linked Server

Accessing URL

Accessing URL

Accessing URL

SQL,access denied

Blog Archive

About Me