Newbie question activex transform

Hi,

This may be incredibly stupid question but how do you execute a sql
statement from within a dts transform data task.

I'm transfering data from one table to another one on the same
database on sqlserver 2000. Most of the rows are copy columns but some
require some manipulations done on them so I'm using activex tasks to
do it.

I have to filter on a part number. There can be more than one row in
the source table with the same part number and I need to select the
earliest date field from the table for that part number.

"select min(first_date_time) from table_name where [part number] = '"
& DTSSource("part Number") & "'"

This is the sql statement i use. It gets parsed correctly and the part
number gets correctly assigned into the statement.

However I have no idea how to execute the statement inside the activex
script and assign the value returned to a variable name to be used
later in the script.

Can someone enlighten me on if it can be done and if not how I amy
achieve a result by other means if it can't

Thanks.It's ok, I discovered what lookup queries are :)

On Sat, 06 Sep 2003 14:54:04 +0100, Mirth1314 <not@.ahope.net> wrote:

>Hi,
>This may be incredibly stupid question but how do you execute a sql
>statement from within a dts transform data task.
>I'm transfering data from one table to another one on the same
>database on sqlserver 2000. Most of the rows are copy columns but some
>require some manipulations done on them so I'm using activex tasks to
>do it.
>I have to filter on a part number. There can be more than one row in
>the source table with the same part number and I need to select the
>earliest date field from the table for that part number.
>"select min(first_date_time) from table_name where [part number] = '"
>& DTSSource("part Number") & "'"
>This is the sql statement i use. It gets parsed correctly and the part
>number gets correctly assigned into the statement.
>However I have no idea how to execute the statement inside the activex
>script and assign the value returned to a variable name to be used
>later in the script.
>Can someone enlighten me on if it can be done and if not how I amy
>achieve a result by other means if it can't
>Thanks.

Newbie Question

Hi

I'm working with SQL Express, and this is my first experience with DB, so I'm still learning. I made a rather stupid mistake, and I'm not quite sure how to get out of it. In the process of trying to fix a problem, I changed the default DB in my account to one that it can no longer connect to - for a reason unknown to me. (once I get this cleared up, I'll ask about that.) Now I can't login properly to SQL Express, b/c it can't connect properly.

please help

thanx

Jeffery

hi,

yiu should be able to connect via a system administrator login, bot WinNT trusted authenticated (login in in Windows as a local administrator) or standard SQL Server authenticated (if you enabled mixed security) providing sa login's credentials (userid and password)...

once you connect to SQLExpress, you can modify (via SSMSX) the default database property of the faling login object in the general tab or, executing the

ALTER LOGIN login_name
WITH DEFAULT_DATABASE = new_default_db;

Transact-SQL statement..

regards

|||Thanx...

Duh! - to me - I should have thought of creating another local Admin account.

Thanx - it worked.

Newbie question

Hi, I am new to reporting services, and I wonder if I am limited to sql queries or can I program reports? This might sound stupid but I have never had to write reports so I am not sure what to look for.

I've been using RS for about 3 months now. You can use SQL queries to populate your datasets, but you can write code to "dynamically" generate those queries.

I'd suggest picking up one of the few RS books out there, like the "Reporting Services in Action" or "Hitchhikers Guide.." books.

|||but does it let you use dataset created at runtime as a source of report?

newbie help: failover server

Hi all, Firstly let me apologize if any of my questions sound stupid. I am a
newbie when it comes to SQL Server. We currently have 1 server running SQL
with a couple of databases collectively approaching 2GB. The HDD's are
configured on a RAID 5 and everything has been running OK. We just recently
experienced downtime with one of our other mail servers and there is a
sudden focus on backup failover servers. Unfortunately we do not have the
budget to invest in Windows Advanced Server and SQL Enterprise for
clustering etc. It is my understanding that I can have a standby cold fail
over server with SQL installed that I can switch on, incase the primary goes
down. Is that true? If yes, what is the best way to set this up? Are there
any best practices?
In our recent server failure, the server would not even bootup. As a result
we could not even get access to the data on the hard drives. We had to end
up biting the bullet by being down the the most part of the day, while the
server OS was re-built. Assuming something similar happens on the SQL
Server, how can I move the latest copy of the database over to the failover
server? Any suggestions? I know there are some options like having an
external rackmount storage system, but to my knowledge those are pretty
expensive and provided by EMC etc.
thanks a bunch!
Without Enterprise Edition and its associated clustering feature, your next
best availability technologies are replication and log shipping. I strongly
discourage replication as an availability option since many database
elements are not replicated. Log shipping is included with Enterprise
Edition, but you can 'roll your own' without too much difficulty. The SQL
Server 2000 Resource Kit includes a simple log shipping example that you can
adapt for your site. This will allow you to keep the data fairly current
with your production server, but will require a fair amount of manual
intervention to 'go live'.
My suggestion is to examine your disaster recovery policy now and offer
alternatives to your management based on expected cost and expected system
availability. SQL Clustering is one option and log shipping is another.
(You can combine them by log shipping to another site and really be
prepared.) Offer them a choice and show what the different levels of
spending actually buy in terms of system availability. You may be surprised
what becomes affordable when the choice is presented as a business decision,
not a technology decision.
No matter which technology path you choose, documentation and training will
be the keys to meeting your availability targets. You must have a good plan
and the personnel to implement it. Otherwise the technology doesn't get you
squat.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:uxFMQTzQEHA.2404@.TK2MSFTNGP11.phx.gbl...
> Hi all, Firstly let me apologize if any of my questions sound stupid. I am
a
> newbie when it comes to SQL Server. We currently have 1 server running SQL
> with a couple of databases collectively approaching 2GB. The HDD's are
> configured on a RAID 5 and everything has been running OK. We just
recently
> experienced downtime with one of our other mail servers and there is a
> sudden focus on backup failover servers. Unfortunately we do not have the
> budget to invest in Windows Advanced Server and SQL Enterprise for
> clustering etc. It is my understanding that I can have a standby cold fail
> over server with SQL installed that I can switch on, incase the primary
goes
> down. Is that true? If yes, what is the best way to set this up? Are there
> any best practices?
> In our recent server failure, the server would not even bootup. As a
result
> we could not even get access to the data on the hard drives. We had to end
> up biting the bullet by being down the the most part of the day, while the
> server OS was re-built. Assuming something similar happens on the SQL
> Server, how can I move the latest copy of the database over to the
failover
> server? Any suggestions? I know there are some options like having an
> external rackmount storage system, but to my knowledge those are pretty
> expensive and provided by EMC etc.
> thanks a bunch!
>
|||Thanks for the options. I will look into Log Shipping & the SQL Resource
Kit.
As far as business decision vs technology decision, easier said than done
when working for a company of your size. When it comes to small business, a
whole different set of rules come into play.
thanks again!
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:Ot1BHW0QEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Without Enterprise Edition and its associated clustering feature, your
next
> best availability technologies are replication and log shipping. I
strongly
> discourage replication as an availability option since many database
> elements are not replicated. Log shipping is included with Enterprise
> Edition, but you can 'roll your own' without too much difficulty. The SQL
> Server 2000 Resource Kit includes a simple log shipping example that you
can
> adapt for your site. This will allow you to keep the data fairly current
> with your production server, but will require a fair amount of manual
> intervention to 'go live'.
> My suggestion is to examine your disaster recovery policy now and offer
> alternatives to your management based on expected cost and expected system
> availability. SQL Clustering is one option and log shipping is another.
> (You can combine them by log shipping to another site and really be
> prepared.) Offer them a choice and show what the different levels of
> spending actually buy in terms of system availability. You may be
surprised
> what becomes affordable when the choice is presented as a business
decision,
> not a technology decision.
> No matter which technology path you choose, documentation and training
will
> be the keys to meeting your availability targets. You must have a good
plan
> and the personnel to implement it. Otherwise the technology doesn't get
you[vbcol=seagreen]
> squat.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:uxFMQTzQEHA.2404@.TK2MSFTNGP11.phx.gbl...
am[vbcol=seagreen]
> a
SQL[vbcol=seagreen]
> recently
the[vbcol=seagreen]
fail[vbcol=seagreen]
> goes
there[vbcol=seagreen]
> result
end[vbcol=seagreen]
the
> failover
>
|||Actually, it is the same rules. Size and scale may determine which solution
is best, but the inputs of cost and availability don't change. The only
difference is a business decision of whether the additional uptime is an
effective way to spend the company's money. Of course everyone has budget
limits. Sometimes major availability enhancements take a while to get
approved and implemented.
No matter what the decision is, you are fine since management will now be
aware of the risks and has active input in deciding how to mitigate them.
This works the same no matter how big or small your business.
Again, you can work on procedures, documentation, and training even without
a huge budget. I guarantee you that will improve system availability and
recoverability.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:eoURYM1QEHA.2468@.tk2msftngp13.phx.gbl...
> Thanks for the options. I will look into Log Shipping & the SQL Resource
> Kit.
> As far as business decision vs technology decision, easier said than done
> when working for a company of your size. When it comes to small business,
a[vbcol=seagreen]
> whole different set of rules come into play.
> thanks again!
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:Ot1BHW0QEHA.2704@.TK2MSFTNGP10.phx.gbl...
> next
> strongly
SQL[vbcol=seagreen]
> can
current[vbcol=seagreen]
system[vbcol=seagreen]
> surprised
> decision,
> will
> plan
> you
I[vbcol=seagreen]
> am
> SQL
> the
> fail
primary[vbcol=seagreen]
> there
> end
> the
pretty
>
|||Geoff, after doing some reading it appears that log shipping is only
available in Enterprise Edition. We are running Standard Edition. What
options am I left with to have a cold standby server with upto date data
incase the primary goes down? You had some reservations against replication
since all database elements are not replicated. What other options do I
have, other than restoring the last known good backup on the secondary
server?
thanks!
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O4KuwU1QEHA.2132@.TK2MSFTNGP11.phx.gbl...
> Actually, it is the same rules. Size and scale may determine which
solution
> is best, but the inputs of cost and availability don't change. The only
> difference is a business decision of whether the additional uptime is an
> effective way to spend the company's money. Of course everyone has budget
> limits. Sometimes major availability enhancements take a while to get
> approved and implemented.
> No matter what the decision is, you are fine since management will now be
> aware of the risks and has active input in deciding how to mitigate them.
> This works the same no matter how big or small your business.
> Again, you can work on procedures, documentation, and training even
without[vbcol=seagreen]
> a huge budget. I guarantee you that will improve system availability and
> recoverability.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:eoURYM1QEHA.2468@.tk2msftngp13.phx.gbl...
done[vbcol=seagreen]
business,[vbcol=seagreen]
> a
> SQL
you[vbcol=seagreen]
> current
offer[vbcol=seagreen]
> system
another.[vbcol=seagreen]
good[vbcol=seagreen]
get[vbcol=seagreen]
stupid.[vbcol=seagreen]
> I
running[vbcol=seagreen]
are[vbcol=seagreen]
a[vbcol=seagreen]
have[vbcol=seagreen]
cold[vbcol=seagreen]
> primary
to[vbcol=seagreen]
while[vbcol=seagreen]
SQL[vbcol=seagreen]
an
> pretty
>
|||Log shipping is included with Enterprise Edition. You can 'roll your own'
with any edition. Before you ask, yes, it is legal provided all servers
involved are properly licensed. The SQL Server Resource Kit has an example
that will work with standard edition of SQL Server. You will probably need
to tweak it for your specific needs, but it is a good starting point. I
have Enterprise Edition, primarily for the scalability and clustering
features, but I wrote my own log shipping because the included product
didn't meet all of my needs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:OLF0ZRBSEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Geoff, after doing some reading it appears that log shipping is only
> available in Enterprise Edition. We are running Standard Edition. What
> options am I left with to have a cold standby server with upto date data
> incase the primary goes down? You had some reservations against
replication[vbcol=seagreen]
> since all database elements are not replicated. What other options do I
> have, other than restoring the last known good backup on the secondary
> server?
> thanks!
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O4KuwU1QEHA.2132@.TK2MSFTNGP11.phx.gbl...
> solution
budget[vbcol=seagreen]
be[vbcol=seagreen]
them.[vbcol=seagreen]
> without
and[vbcol=seagreen]
Resource[vbcol=seagreen]
> done
> business,
your[vbcol=seagreen]
Enterprise[vbcol=seagreen]
The[vbcol=seagreen]
> you
manual[vbcol=seagreen]
> offer
> another.
of[vbcol=seagreen]
training[vbcol=seagreen]
> good
> get
> stupid.
> running
> are
is[vbcol=seagreen]
> a
> have
> cold
Are[vbcol=seagreen]
a[vbcol=seagreen]
had
> to
> while
> SQL
> an
>
|||Geoff, is the Resource Kit available for download? Or do I need to purchase
the book from MS Press? Also talking about licensing issues, if the cold
standby is only used in the event of failure of the primary server, do I
still need a licenses for the standby server?
thanks
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:urxatWBSEHA.3168@.tk2msftngp13.phx.gbl...
> Log shipping is included with Enterprise Edition. You can 'roll your own'
> with any edition. Before you ask, yes, it is legal provided all servers
> involved are properly licensed. The SQL Server Resource Kit has an
example
> that will work with standard edition of SQL Server. You will probably
need[vbcol=seagreen]
> to tweak it for your specific needs, but it is a good starting point. I
> have Enterprise Edition, primarily for the scalability and clustering
> features, but I wrote my own log shipping because the included product
> didn't meet all of my needs.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "RP" <rp@.nospam.com> wrote in message
> news:OLF0ZRBSEHA.1312@.TK2MSFTNGP12.phx.gbl...
> replication
only[vbcol=seagreen]
an[vbcol=seagreen]
> budget
> be
> them.
> and
> Resource
> your
I[vbcol=seagreen]
database[vbcol=seagreen]
> Enterprise
> The
that[vbcol=seagreen]
> manual
expected[vbcol=seagreen]
be[vbcol=seagreen]
> of
> training
doesn't[vbcol=seagreen]
HDD's[vbcol=seagreen]
just[vbcol=seagreen]
there[vbcol=seagreen]
> is
for[vbcol=seagreen]
> Are
As[vbcol=seagreen]
> a
> had
the[vbcol=seagreen]
the[vbcol=seagreen]
having
>
|||"RP" <rp@.nospam.com> wrote in message
news:%23WtvuZBSEHA.568@.TK2MSFTNGP12.phx.gbl...
> Geoff, is the Resource Kit available for download? Or do I need to
purchase
> the book from MS Press? Also talking about licensing issues, if the cold
> standby is only used in the event of failure of the primary server, do I
> still need a licenses for the standby server?
Can't answer all that, but there are "roll-your own" logshipping routines
out there if you google for them.
To be honest, I sometimes forget ours is running it's so transparent. (I'd
give you the URL but I've honestly forgotten it.)
|||The Resource Kit is not available for download. As Greg noted, there are
several available for download. It isn't too difficult to build or modify
your own. It will take some time and a good understanding of the backup
tables in the msdb database.
As for the licensing, I am not a legal expert, but I suspect you will have
to fully license all servers. The only exception I can find to licensing a
server is with failover clustering and a normally inactive host node.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"RP" <rp@.nospam.com> wrote in message
news:%23WtvuZBSEHA.568@.TK2MSFTNGP12.phx.gbl...
> Geoff, is the Resource Kit available for download? Or do I need to
purchase[vbcol=seagreen]
> the book from MS Press? Also talking about licensing issues, if the cold
> standby is only used in the event of failure of the primary server, do I
> still need a licenses for the standby server?
> thanks
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:urxatWBSEHA.3168@.tk2msftngp13.phx.gbl...
own'[vbcol=seagreen]
> example
> need
data[vbcol=seagreen]
I[vbcol=seagreen]
> only
is[vbcol=seagreen]
> an
get[vbcol=seagreen]
now[vbcol=seagreen]
availability[vbcol=seagreen]
than[vbcol=seagreen]
feature,[vbcol=seagreen]
> I
> database
difficulty.[vbcol=seagreen]
> that
fairly[vbcol=seagreen]
and[vbcol=seagreen]
> expected
> be
levels[vbcol=seagreen]
be[vbcol=seagreen]
business[vbcol=seagreen]
a[vbcol=seagreen]
> doesn't
> HDD's
> just
> there
not[vbcol=seagreen]
> for
standby[vbcol=seagreen]
the[vbcol=seagreen]
up?[vbcol=seagreen]
bootup.[vbcol=seagreen]
> As
We[vbcol=seagreen]
day,[vbcol=seagreen]
> the
> the
> having
are
>

newbie confusion: file vs server

caution: this is not doubt a stupid newbie question...

In creating vs.net 2005 website, I can add a sql database to my project and a mdf file is created. I can create data providers against this file, etc, just as though it were a database in a sql server instance. I can deploy this dbf file to my finished web site.

Also, I can attach to a running instance of sql server 2005 express, and do exactly the same thing.

I can also take my mdf file created in step 1 above, and attach it to a running instance of sql server express.

Now, I have delt with access databases, and sql server 2000 databases, so this dual nature of sql server 2005 express confuses me a little.

Why would I ever need to use a server instance of sql server 2005 when I can use a file based data file in my web apps? Is there an advantage to one or the other?

I had a thought that when using the file based method, I was actually still using the server based stuff, which would explain why the sql server express notification bubble pops up when I debug on the dev machine.

In any case could someone explain the difference and should I install sql server 2005 express on my deployment server?

thanks.

Access is a database SQL Server Express is a SQL Server 2005 edition which is RDBMS(relational database management systems) without a SQL Server instance you don't have a database engine to run your MDF(Microsoft data file) it is just one of at least two files you need to run your database. Run a search for file groups in SQL Server BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

|||

In order to use a *.mdf (SQL Server database file), you must have a SQL Server installed and running.

That could be SQL Server 2005 Express.

|||

Thanks for the replies. I have a clearer picture but have some additional questions...

I add a mdf file to my asp.net website. This file requires some version of SQL server, in this case

Express, to provide the RDMS system. Great. So how is it that I can just deploy the app to the web server

and it works without installing SQL server Express on the web server?

Is SQL Server Express built into the ASP.Net 2.0 runtime?

If so, the file must somehow be dynamically attached to this instance of SQL Server Express?

How will this type of deployment be upgraded to a higher version of SQL Server? If I use the "file" based

technique, will my app always use the embeded SQL Server Express and not any fuller version of SQL Server

I may install?

So to migrate to a higher version of SQL Server, will I need to attach the mdf file to SQL Server and change my

connection string to point to the new version of SQL Server?

This is my last little stumbling block to grasping the SQL Server Express concept, thanks for your replies!

|||

Perhaps this will clear some things up for you.

http://msdn2.microsoft.com/en-us/library/bb264564.aspx

|||

yes, perfect. Thanks.

No doubt I over think these things.

While I appreciate the ease of use, I also like knowing what's going on, so the documentation is there, of course, but in such great volume one questions the point of reading massive amounts of documentation on an easy to use feature. ;-) I digress...thanks again.

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs database:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.
Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:

> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs database:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity of
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs databas
e:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:

> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs datab
ase:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity o
f
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs database:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:
> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs database:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity of
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie - Querying SQL Server Express 2005 database from Excel

Sorry if this is a stupid question, but I created a database using SQl Server Express 2005 and I would like to query one of its tables from an Excel spreadsheet.

Here's what I want to do in pseudo code.

cell A2.value = select OLIGO_ID from table OLIGO where SEQUENCE = 'content of cell D2, a string'

In other words, I want to search the database for a string that is in a cell and retrieve its associated ID number into another cell. I need to do this on many cells.

Any help is appreciated. Thanks.

I guess you can do that with VBA.

As far as SQL is concerned I do something like that to extract data from a cube down to Excel. You will just have to build the connection string for SQL Express (get it from the macro recorder) and adjust the query to a more TSQL like query rather than OLAP. You can build any string you want.

Function to query SQL with a query string

-

Private Sub ado(Connection As String, Query As String, destination As String)
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnnConnect = New ADODB.Connection
cnnConnect.Open Connection

Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:=Query, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
destination:=Range(destination))
.FieldNames = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
cnnConnect.Close
Set cnnConnect = Nothing
Set rstRecordset = Nothing
End Sub

-

-- OLAPMENU

OlapMenu = _
"Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Location=analysis.onsemi.com;Initial Catalog=" & InitialCatalog & ""

--

call to the function

Query = "select" & _
"[data Switch].[Switch].members on axis(0)," & _
" Filter( [Region].[Rep Sales Region Desc].members, [Data Switch].[On] >0 ) on axis(1)" & _
"from [ST_Crawl]"
Application.StatusBar = "Now Populating Pull-down Region, please wait..."
Application.Cursor = xlWait
Call ado(OlapMenu, Query, "A3")

|||Yes, I would go as far as say write a function in Excel that accepts the input parameter, then uses ADO to fetch the data. Then you can pass in the value from the spreadsheet into the function, the function takes the parameter and uses an ADODB.Command with params to execute the SQL statement, then the function returns the output as a string.