Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

External Images on Network Share Driving Me Mad

Hi,
Using RS 2000 SP2, I'm trying to access external images that a stored
on another computer via a network share. However, I get nothing but
red x's in my report. I understand that in order for this to work, I
need to setup the UnattendedExecutionAccount. Here's the rsconfig call
I've used:
rsconfig -e -u DOMAIN\user -p password -a windows
However, the pictures still don't show. I've logged on as the user in
question and confirmed the user can see the folder and open the
images. I've also double checked that I have the correct case for the
user and the password. The image source is in the format:
file://\\Computer\Data\Images\xxxx.jpg
I've also tried:
\\Computer\Data\Images\xxxx.jpg
If I copy the images to a local drive everything works.
Is there a problem using UNC paths? Maybe the developer install has
trouble accessing shared network paths? This is driving me nuts, can
anybody shed any light?
Thanks,
CamelOn Mar 3, 11:20 am, Daniel wrote:
> Mine works with the following:
> file:\\server\share folder\xxxxxx.gif
> EggHeadCafe.com - .NET Developer Portal of Choicehttp://www.eggheadcafe.com
Unfortunately I was not able to resolve this. I suspect that it is a
permissions issue - and that it has something to do with the
UnattendedExecutionAccount not working the way I expect it to.
Regardless, I've given up and used an IIS virtual folder instead. At
least there I can control what credentials are used to access the
share.
Thanks,
Camel

ExtendedProperties Performance

Has anyone found a way to speed up the retrival of the "ExtendedProperties" on a column via SMO yet?

I had a search around but couldn't spot anything. I have an applicaiton that currently cyles through 180+ tables (with the number growing all the time) and the "ExtendedProperties" access is abosultly killing it ... taking it from seconds to minutes.

If anyone could provide any information (even if it's a "no it's not possible to speed up") or an alternative retrival method I'd greatly appreciate it.

Have you tried using the SetDefaultInitFields method to include the ExtendedProperties object? Insert this code after you connect to the server:

srv.SetDefaultInitFields(GetType(Table), "ExtendedProperties")

|||

I'm retriving the ExtendedProperties from Columns, not Tables :)

I've tried including the SetDefualtInitFields(typeof(Column), "ExtendedProperties") but it doesn't have much effect.

The people in this thread are having the same problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=571167&SiteID=1

|||

ExtendedProperties is a collection not a property therefore your solution will not work.

Try this:

Database db = ... // get your database root

ScriptingOptions so = new ScriptingOptions();

db.PrefetchObject(typeof(Table), so);

so.ExtendedProperties = true;

db.PrefetchObjects(typeof(Column), so);

Let me know if it works.

Ciprian Gerea, SqlServer SDE

|||

This,

"this.o_Database.PrefetchObjects(typeof(Column), scriptingOptions);"

doesn't work, I think this is because it is a child collection on Table rather than on Database.

But,

ScriptingOptions scriptingOptions = new ScriptingOptions();

scriptingOptions.ExtendedProperties = true;

this.o_Database.PrefetchObjects(typeof(Table), scriptingOptions);

works wonderfully :D It seems it prefetchs the ExtendedProperties on the Columns as well as the Tables!

Thanks for helping me optimize my 5 minute loop into a 10 second one :D

Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

Expression switch facility

Hello, I would ask about the possibility of implementing a switch algorithm via expressions in Reporting services to . For example, Can I test on the dataset field a

if a= null -->display none

if a= 1 -->display "string1"

if a= 2 -->display "string2"

some help about this topic

thanks in advance

= Switch(Fields!a.Value = 1, "string1", Fields!a.Value = 2, "string2", Not(Fields!a.Value), "", true, "else string here")

expression builder switch()

Via the Expression Builder, I'm using the switch() program flow function to handle my data. I can't seem to figure out how to do the "else" or "default" part of it. Anyone have a clue? Ex:

Switch(
Fields!test.value = 1, "One",
Fields!test.value = 2, "Two",
Fields!test.value = 3, "Three",
)

But what if Fields!test.value is not either 1,2, or 3, how would you set the "else" or "default" part within the switch statement?

Add a condition at the end which always evaluates to true.

Switch(
Fields!test.value = 1, "One",
Fields!test.value = 2, "Two",
Fields!test.value = 3, "Three",
1=1, "Default"
)

Exposing the port 1433

I have a machine installed with an MSDE 2000 and it have 3 connectios:
Internal lan
Internet via DSL
VPN
How can I restric that de port 1433 only hears on the Internet Lan, because
when I activate de others connections del service takes 100% of CPU and bloc
k
all connections.
Thanks in advance.By default we bind to all interfaces. You'll need to put your server
behind a firewall to limit the external IP only.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Expose SQL Meta Data via ASP

Hi,

Apologies if this is better posted in an ASP group, but here goes
anyway ...

Is it possible to work out what parameters a stored procedure expects,
using ASP?

I would like to take the name of a stored procedure, work out what
input parameters it has and build a form based on them in ASP.

Thanks,
MB.Tools (mbrocklehurst@.hotmail.com) writes:
> Apologies if this is better posted in an ASP group, but here goes
> anyway ...
> Is it possible to work out what parameters a stored procedure expects,
> using ASP?
> I would like to take the name of a stored procedure, work out what
> input parameters it has and build a form based on them in ASP.

You can use the .Refresh method for a command object. That will populate
a parameter collection for you.

Now, how you actually access that from ASP, you will have to ask someone
else. I don't know ASP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||or you can run a query against INFORMATION_SCHEMA.PARAMETERS

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959DF15265D4AYazorman@.127.0.0.1...
> Tools (mbrocklehurst@.hotmail.com) writes:
> > Apologies if this is better posted in an ASP group, but here goes
> > anyway ...
> > Is it possible to work out what parameters a stored procedure expects,
> > using ASP?
> > I would like to take the name of a stored procedure, work out what
> > input parameters it has and build a form based on them in ASP.
> You can use the .Refresh method for a command object. That will populate
> a parameter collection for you.
> Now, how you actually access that from ASP, you will have to ask someone
> else. I don't know ASP.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Exporting to Plain Text

Has anyone found a way to export a report to plain text? I need to be able to
do this automatically when the report is run via a schedule on the server.
Help!
RickYou need to have a custom rendering solution. This link is quite interesting
about custom rendering.
http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/
Amarnath
"RLS" wrote:
> Has anyone found a way to export a report to plain text? I need to be able to
> do this automatically when the report is run via a schedule on the server.
> Help!
> Rick

Exporting stored procedures

Hello!
I look for a way to export the stored procedures from a database
automatically. The GUI way is "generate SQL script", but I want to do it via
script, or an API.
Thanks!
RoeeIt is all in the SQL-DMO API.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it via
> script, or an API.
> Thanks!
> Roee
>|||Hi,
You can use DTS packages to copy objects to destination.
The task name is "Copy SQL Server Objects Task" , in the copy tab you can
select all stored procedures.
Thanks
Hari
MCDBA
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eOMJaLSnDHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You can use DTS packages to copy objects to destination.
> The task name is "Copy SQL Server Objects Task" , in the copy tab you can
> select all stored procedures.
Although this will only work if the source and destination are both SQL
Servers
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||Here's an example of scripting all the stored procedures in a database with
Perl and SQL-DMO. Save the following code in a file, say, named scriptSP.pl:
################## Being script
# tested with ActivePerl 5.6.1, Binaray Build 631
use strict;
use Getopt::Std;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO'; # import the SQL-DMO symbolic
constants
my %opts;
getopts('S:d:o:', \%opts); # Get the command line arguments
my ($serverName, $dbName, $output) = ($opts{S}, $opts{d}, $opts{o});
# Check whether the required command line arguments are specified
(defined $serverName && defined $dbName && defined $output)
or printUsage();
# Create a SQLDMO SQLServer object
my $server = Win32::OLE->new('SQLDMO.SQLServer') or
die "***Err: could not create SQLDMO object.\n";
$server->{LoginSecure} = 1; # trusted connection
# Connect to the SQL instance via trusted connection
$server->connect($serverName);
! Win32::OLE->LastError() or
die "***Err: SQLDMO could not connect to $serverName.\n";
# Get the SQLDMO Database object
my $db = $server->Databases($dbName);
# Loop through the StoredProcedures collection to script each stored
procedure
foreach my $obj (in($server->Databases($dbName)->StoredProcedures())) {
$obj->Script(SQLDMOScript_Default | SQLDMOScript_AppendToFile |
SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$output); # the SQL script goes to this
file
}
# Clean up
$server->disconnect();
$server->DESTROY();
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl scriptSP.pl -S <SQL instance> -d <database name> -o <output
file>
--Usage--
exit;
} # printUsage
################# End script
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>

Exporting Stored Procedures

I have to retrieve all the stored procedures in a certain database via a aspx page. My problem is that I have basically no idea how to do it. I did manage to get all the data as XML and reconstruct the database (pain). All I need now (I hope) is the sprocs. I do not have any other access to the database. And I'm a novice with SQL Server. Any guidance is appreciated.

SteveSomething like:

select text
from syscomments
where text like 'create procedure%'

|||Thank you very much.

I also have to restore the sprocs. Should I select all the columns in syscomments and sysobjects for the relevent rows and insert them into the new db? Will that do it?

Thanks again,

Steve|||NO, I do not recommend this approach.. Why are you looking to perform these activities through an asp.net application?|||I have a client with a developer problem. They asked me to coordinate with the developer to facilitate moving the site. Despite the developer's statements that I could access the SQL Server, he only gave me FTP access to the site. Eventually, I find out that he is intentionally being difficult, refusing to assist unless the client paid him more money. Apparently a _lot_ more money. I found in his connection code where he recently changed the pw to the database, to something other than what he told me. I assume the developer is being unreasonable to the point where it is cheaper to have me do it the "hard" way. Oh yeah, this is a classic ASP app, which I have no experience with either.

So... I had to use an aspx page to get the data via XML. Easy enough, but restoring it was a bit more challenging. I finally have the tables restored properly; now all I need is the stored procedures (I hope).

I did get the procedures (thanks again), however I had to get all the rows because none of the sprocs begin with the string you suggested, and I'm wanderin' around in the dark.

I'm amazed I've accomplished what I have. It's a good thing .NET is so smart. :)

Steve|||Thanks to your help, I was able to completely reconstruct the database!

I had to manually re-add the stored procedures, views & triggers I got from your suggestion. In the end, it was the easiest way.

Thanks again,

Steve