Atomicity

...if there is such a word. Here is the question: I inherited a rather
long sproc that does a lot of things to a lot of different tables.
Let's say it fails 2/3 of the way through. Does the database roll back
all the changes that were made along the way because of, say, a deadlock
or some other problem? Note that the sproc itself has no explicit
transaction declarations.
Thanks.
Atomicity is a word.
Wrap the whole sproc in a transaction. If an error occurs (Check for
@.@.error), then rollback.
Remember that @.@.error resets after each statement.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.
|||Unless you are using Implicit Transactions you don't have an ATOMIC sp. Is
there a Commit at the end of the proc? If not then probably not.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.

Atomicity

...if there is such a word. Here is the question: I inherited a rather
long sproc that does a lot of things to a lot of different tables.
Let's say it fails 2/3 of the way through. Does the database roll back
all the changes that were made along the way because of, say, a deadlock
or some other problem? Note that the sproc itself has no explicit
transaction declarations.
Thanks.Atomicity is a word.
Wrap the whole sproc in a transaction. If an error occurs (Check for
@.@.error), then rollback.
Remember that @.@.error resets after each statement.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.|||Unless you are using Implicit Transactions you don't have an ATOMIC sp. Is
there a Commit at the end of the proc? If not then probably not.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.

Atomicity

...if there is such a word. Here is the question: I inherited a rather
long sproc that does a lot of things to a lot of different tables.
Let's say it fails 2/3 of the way through. Does the database roll back
all the changes that were made along the way because of, say, a deadlock
or some other problem? Note that the sproc itself has no explicit
transaction declarations.
Thanks.Atomicity is a word.
Wrap the whole sproc in a transaction. If an error occurs (Check for
@.@.error), then rollback.
Remember that @.@.error resets after each statement.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.|||Unless you are using Implicit Transactions you don't have an ATOMIC sp. Is
there a Commit at the end of the proc? If not then probably not.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23Vxlp2z3FHA.2816@.tk2msftngp13.phx.gbl...
> ...if there is such a word. Here is the question: I inherited a rather
> long sproc that does a lot of things to a lot of different tables. Let's
> say it fails 2/3 of the way through. Does the database roll back all the
> changes that were made along the way because of, say, a deadlock or some
> other problem? Note that the sproc itself has no explicit transaction
> declarations.
> Thanks.sql

asymmetric encryption

Is there a step by step cookbook on setting up asymmetric encryption. My
goal is to encrypt a couple of tables from one of my dataabses. Thanks!Do not use asymmetric key encryption to protect data; it is too slow for
that purpose, plus there are limitations on how large a piece of data you
can encrypt. For symmetric key encryption examples, see my blog:
http://blogs.msdn.com/lcris/ .
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Keith" <Keith @.discussions.microsoft.com> wrote in message
news:511B39A3-071D-45D7-8C7B-C33C5E744385@.microsoft.com...
> Is there a step by step cookbook on setting up asymmetric encryption. My
> goal is to encrypt a couple of tables from one of my dataabses. Thanks!|||It's an awesome encryption blog. If anyone needs to learn
more on encryption in 2005, it's *the* resource. Just
checked your blog today - thanks for the posting the
proposed updated sp_helprevlogin. Awesome!
Thanks for all time you've put into your blog.
-Sue
SQL Server MVP
On Wed, 5 Apr 2006 10:50:02 -0700, "Laurentiu Cristofor
[MSFT]" <laur@.nospam.com> wrote:

>Do not use asymmetric key encryption to protect data; it is too slow for
>that purpose, plus there are limitations on how large a piece of data you
>can encrypt. For symmetric key encryption examples, see my blog:
>http://blogs.msdn.com/lcris/ .
>Thanks|||Awesome... Thanks for the help. Will this method encrypt data already in m
y
database?
"Laurentiu Cristofor [MSFT]" wrote:

> Do not use asymmetric key encryption to protect data; it is too slow for
> that purpose, plus there are limitations on how large a piece of data you
> can encrypt. For symmetric key encryption examples, see my blog:
> http://blogs.msdn.com/lcris/ .
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Keith" <Keith @.discussions.microsoft.com> wrote in message
> news:511B39A3-071D-45D7-8C7B-C33C5E744385@.microsoft.com...
>
>|||Yes, you can encrypt existing data. Here are some caveats: The output of
encryption is varbinary, so if you're trying to encrypt a table column that
is not varbinary, you'll need to change the table definition. The ciphertext
is also larger than the cleartext, so you may need to do this even if your
data is already varbinary; if the column is too small.
One other caveat is that we only allow encrypting values less than 8k.
See this blog entry for additional details:
http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx
Note that the YukonDoIt blog is not active anymore. Raul, who wrote this
post, now has a blog at:
http://blogs.msdn.com/raulga/
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:2654724A-2B10-4F94-B51E-C1A2B2FD7A30@.microsoft.com...[vbcol=seagreen]
> Awesome... Thanks for the help. Will this method encrypt data already in
> my
> database?
> "Laurentiu Cristofor [MSFT]" wrote:
>|||Thank you. I'm glad you found my blog posts useful.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q0o832t10pvevq2a0v0pl3der2q3vmfau6@.
4ax.com...
> It's an awesome encryption blog. If anyone needs to learn
> more on encryption in 2005, it's *the* resource. Just
> checked your blog today - thanks for the posting the
> proposed updated sp_helprevlogin. Awesome!
> Thanks for all time you've put into your blog.
> -Sue
> SQL Server MVP
> On Wed, 5 Apr 2006 10:50:02 -0700, "Laurentiu Cristofor
> [MSFT]" <laur@.nospam.com> wrote:
>
>|||If you use symmetric encryption do the end users, connecting with a
front-end, need to input a password to acess the encrypted data? Or will
their logging into the system grant them access to the encrypted data?
"Laurentiu Cristofor [MSFT]" wrote:

> Yes, you can encrypt existing data. Here are some caveats: The output of
> encryption is varbinary, so if you're trying to encrypt a table column tha
t
> is not varbinary, you'll need to change the table definition. The cipherte
xt
> is also larger than the cleartext, so you may need to do this even if your
> data is already varbinary; if the column is too small.
> One other caveat is that we only allow encrypting values less than 8k.
> See this blog entry for additional details:
> http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx
> Note that the YukonDoIt blog is not active anymore. Raul, who wrote this
> post, now has a blog at:
> http://blogs.msdn.com/raulga/
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Keith" <Keith@.discussions.microsoft.com> wrote in message
> news:2654724A-2B10-4F94-B51E-C1A2B2FD7A30@.microsoft.com...
>
>|||It depends on how you design your database application. Both of these
scenarios are possible.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:D8AE6555-A2C3-499D-838D-546F8D13092A@.microsoft.com...[vbcol=seagreen]
> If you use symmetric encryption do the end users, connecting with a
> front-end, need to input a password to acess the encrypted data? Or will
> their logging into the system grant them access to the encrypted data?
> "Laurentiu Cristofor [MSFT]" wrote:
>

Asymmetric data syncronization: possible?

I have two sql server instances on two different machines with the same db;
on the first machine some tables need to be copied on the second machine,
and on the second machine some other tables need to be copied on the first;
all the tables are related with the others in some way.
It is possible to make that replication?
Thanks
Mik
I'm wondering if the same data can be changed on each node? If so, then it
sounds like you need either merge or updating transactional subscribers
(queued or immediate). I'd control this from one server designated as the
publisher and synchronize this way. If this is not the case, then both nodes
could be set up as transactional publishers.
Rgds,
Paul Ibison

Associating Data from Other Tables

Morning, all.

I have an input record that contains the following fields:

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

And I have a table, that contains the following fields

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

Okay. The kicker here is that in the table, the header_id's are already defined, but the details are blank. I need to insert the details where the header_id's of the input equal the header_id's of the table.

Is there a loop or something? I can *almost* see it in my head if I was just writing a SQL Query, but it involves a cursor, and I'm hoping SSIS will save me some of the aggravation...

Any ideas?
Isn't this just an update?

UPDATE TABLE
SET header_detail_1 = input_header_detail1,
header_detail_2 = input_header_detail2
WHERE header_id_1 = input_header_id_1
AND header_id_2 = input_header_id_2
AND header_id_3 = input_header_id_3

You could achieve this with an OLE DB Command transformation.|||Oh, hey! Yeah! Thanks!

Jim Work
|||You could also load the flat file (or whatever source you may have) to a staging table and then issue a batch update -- which would be faster than using the OLE DB Command to perform your updates...

To perform the batch update inside SSIS, you'd use an Execute SQL task in the control flow after you've loaded the staging table with a data flow.

Inside that Execute SQL task, your query would be something like:

UPDATE destination_table a, staging_table b
SET a.column1 = b.column1, a.column2 = b.column2
WHERE a.key1 = b.key1
AND a.key2 = b.key2
AND a.key3 = b.key3

It's real similar to the above query, except that you're using native SQL to perform the update rather than SSIS.

Assistance with selecting first visit record of patients

Hi all

I saw a similar query on another thread but was unable to use the answers to resolve my problem.

I have an access database with two tables - the first contains demographic data for patients (Initial visit table) and the second (followup visit) contains all visit records for these patients linked by a PID. Some of the data was entered retrospectively so the record number is not a reflection of date of visit. Have 25000 visits captured.

I need to devise a query to extract the first CD4 count (a blood result) available for each patient - cd4 not done on every visit. Have tried using:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit]b

where a.InitialVisitID = b.InitialVisitID

order by VisitDatetime desc)));

This initially generates a table with duplicates followed shortly thereafter by a warning that only one record can be returned by the subquery and then blanking out of all records in the query output.

Please help!

I believe you want something like this:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM FollowUpVisit a

inner join

(select Min(InitialVisitID) as InitialVisitID, VisitDatetime, CD4CountPercentage

from FollowUpVisit

group by InitialVisitID

) as b

on a.InitialVisitID = b.InitialVisitID

order by a.VisitDatetime desc

|||

Try:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit] b

where b.PID = a.PID

order by VisitDatetime desc)));

AMB

|||

Thanks for the quick response AMB

Your suggestion has the same result - except asks for the PID paramter to be entered.

Just for clarification InitialVisitID in this database is in fact the PID. So i tried it with changing PID to InitialVisitID but same outcome.

h

|||

Thanks for the response

For clarification the "InitialVisitId" is the PID - so selecting for min InitialVisitID does not work as every patient only has one InitialVisitID (is unique identifier and primary key). Your solution also gives a circular reference problem within the Selection list. Tried making it:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage
FROM FollowUpVisit a
inner join
(select Min(VisitDateTime) as VisitDateTime, InitialVisitID, CD4CountPercentage
from FollowUpVisit
group by InitialVisitID
) as b
on a.InitialVisitID = b.InitialVisitID
order by a.VisitDatetime desc

but got same problem

h

|||

Please, do not make us to guess your enviroment. Post some DDL, including constraints and indexes, sample data and expected result.

Can you post the error msg you are getting?. I do not think that (select top 1 c1, ..., cn from ... order by) can bring more than one row, without using keywords WITH TIES.

AMB

|||

I am confused by this table FollowUpVisit. If this was a Visit table, I might understand, but is the InitialVisit stored in the FollowUpVisit table?

And this seems to imply that you can have >1 InitialVisit?

In your query, be sure and use aliases for every column, just to be careful with the output:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 b.InitialVisitID from [FollowUpVisit] b

where a.InitialVisitID = b.InitialVisitID

order by b.VisitDatetime desc)));

I agree with Hunchback, in that I don't know how that top 1 query can return > 1 row.

Assistance with a query

I have 2 tables POheader and POline. I am running the following SQL
statement but am getting 2 records where as I should only be getting one.
Could someone please assist.
s_commar@.hotmail.com
select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
POline.Ponbr and POline.ponbr
= 16916;
I should just be getting one record but I get 2 records. Could someone
advise as to what I am doing wrong.
Thanks
s_commar@.hotmail.comIts hard to comment without seeing DDL+Sample data. My best
guess is that your POline table has 2 matching rows. If you dont want to
reference any columns from the POnline table in the final output,
you can probably rewrite your query like
Select POheader.ponbr from POheader
WHERE EXISTS(
SELECT 1 FROM POline WHERE POheader.Ponbr = POline.Ponbr
AND POline.ponbr = 16916)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"s commar" <scommar@.verizon.net> wrote in message
news:36iJe.1262$D4.785@.trndny07...
>I have 2 tables POheader and POline. I am running the following SQL
>statement but am getting 2 records where as I should only be getting one.
>Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>|||"s commar" schrieb:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
select POheader.ponbr
from POheader
inner join POline
on POheader.Ponbr = POline.Ponbr
WHERE POline.ponbr = 16916|||Since u hv 2 matching rows in the POLine table against the row in the
POHeader table, u r getting 2 records. Try below query
select DISTINCT POheader.ponbr from POheader inner join POline on
POheader.Ponbr = POline.Ponbr and POline.ponbr= 16916;
Rakesh
"s commar" wrote:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>
>

Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

Assigning PK and FK

Hi,

I have three tables -

Table A
Col1...Col2
1....A
2...B

Table B
Col3...Col4
1....X
2...Y

TableC
Col1...Col3...Col5...Coln
1......1.........H...Y.

A&B serve as the parent table for C. A&B are the dimension tables and C is the fact table. How do I assign the PK and FK in EM?

ThanksBOL:

Referential Integrity

Referential integrity must be maintained between all dimension tables and the fact table. Each fact record contains foreign keys that relate to primary keys in the dimension tables. Every fact record must have a related record in every dimension table used with that fact table. Missing records in a dimension table can cause facts to be ignored when the dimension table is joined to the fact table to respond to queries or for the population of OLAP cubes. Queries can return inconsistent results if records are missing in one or more dimension tables. Queries that join a defective dimension table to the fact table will exclude facts whereas queries that do not join the defective dimension table will include those facts.|||Any adivise on how to index the dimension tables and fact tables based on PK and FK?|||Originally posted by vivek_vdc
Any adivise on how to index the dimension tables and fact tables based on PK and FK?

PK for every dimension table (PK creates own index). Index for every foreign key in fact table if fact table is going to be big.|||So if I have 5 FK's on the Fact table then -

1. I should have a clustered index on those 5 keys together OR
2. Have 5 different indexes for each of the 5 keys.|||I'm sorry....since a warehouse is built with code (I don't have Enterprise, and have never built one with it) and is wrapped by the contraints of that code, why do we need RI?

Just curious...especially since warehouses for the most part are read only...(except for the batch builds)

I know I must be missing something here...

Assigning Foreign Key To New SQL Server Table

Hi

I am creating new SQL Server Tables using SQL Server 2005. I have set
primary key to the tables .But I do not know how to assign Foreign key
to the tables .I need to do some joins later and that is why I have to
put Foreign key to the table . The Primary key is visible and can be
assigned easily .But How do I assign foreign key .

Thanks

*** Sent via Developersdex http://www.developersdex.com ***.. . (kmandal@.sark.com) writes:
> I am creating new SQL Server Tables using SQL Server 2005. I have set
> primary key to the tables .But I do not know how to assign Foreign key
> to the tables .I need to do some joins later and that is why I have to
> put Foreign key to the table . The Primary key is visible and can be
> assigned easily .But How do I assign foreign key .

First of all, questions about SQL 2005 are best asked in the SQL 2005
newsgroups, as these are monitored by the SQL Server deverlopers. Access
info here: http://go.microsoft.com/fwlink/?linkid=31765

As for your question, the syntax is as in this example:

ALTER TABLE tbl ADD CONSTRAINT fk_myforeignley (col1, col2)
REFERENCES othertbl (col1, col2)

Or were you using the table designer? I recommend that you learn the
syntax to create table from SQL statements. In the long run that will
make you more effective and productive, than clicking around in the
table designer. Also, there are several *serious* bugs in the table
designer when it comes to modify existing tables, so the less you use
it, the better.

(If you are dead set on it, I believe that if you right-click there
are foreign keys in the context menu.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Hi

Thanks for your reply . It worked and I was able to assign Foreign key
to the tables . Thanks again .

*** Sent via Developersdex http://www.developersdex.com ***|||Foreign keys will definitely help with relational integrity. However,
for the purposes of joining the foreign key need not be pre-defined.
That is what the join syntax in the select statement is for.

assign the Null value to a variable that is not a Variant data type.

Hello Everyone,

I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

Paul,

I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.

If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?

Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.

Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..

ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.

Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks

ASPNETDB to another database

Hi there readers of this post,

Do you know how to transfer the database (ASPNETDB.mdf) tables made by the ASP.NET admin tool into another database?

I want to run everything from 1 database.

regards

Sat

Hi

What you can do is

Go to folder

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

in the above folder there is a file named "aspnet_regsql" run this file

it will ask you whether a default database or a partucular database in which you would like

to keep this. if you set it as default then it creates a new database"aspnetDB"

if you point to another databse[ say northwind] then all the tables necessary for member ship

do come in to northwind.

Try this if you still find issues let me know

With regards

Sridhar

aspnet_regsqlcache tool not found in my system

hi i am going to install the aspnet_regsqlcache tool in my system to create the notification tables for my database. while i am istalling in the .net framework command prompt, it tells like this,

"'aspnet_regsqlcache' is not recognized as an internal or external command"

what is the problem in my system...please any one help to solve this issues..

thanks in advance

The utility is now called aspnet_regsql.exe. It can be found here C:\WINDOWS\Microsoft.Net\Framework\v2.0.50727.

This was also discussed on the Wrox forum (in case you read about this in a book). http://p2p.wrox.com/TopicIndex/21996.htm

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
Keith
Are they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:

>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
KeithAre they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:
>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>

asp.net configuration tool

I deployed a web site, and converted SQL Server Express to a single SQL Server 2005 database. I created all of the tables required for memberships and roles with aspnet_regsql.exe.

Real simple question - how can I populate the membership and role tables out on the server? When I attach VWD to it, the ASP.NET Configuration tool is gone. How do you maintain memberships and roles without the tool?

Thanks for any help you will share :)

It's been a long journey but I am finally here!

Hi CK,

I'm going to punt on this one.

The ASP.net forums is a better place to ask questions about ASP.net and Visual Web Developer. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

I can't redirect outside of MSDN, so you'll need to repost the question in the new forum.

Mike

|||

Hi mike,

You helped me SO MUCH already on other questions -- xoxo

I'll buy you a beer if you are in redmond and when I get this one figured out.

Are you in?

ck

|||

It's beer time Mike.

VWD asp.net configuration tool successfully allowed me to update roles and memberships.

My web site is up and running.

For future reference, this article was of help: 4guysfromrolla.com article 040506-1

thanks again for helping me - let's go!

ASP.NET 1.1 Component for creating pivote tables with MS SQL Server and with MS SQL Server

Превед!

Prompt me please some asp.net 1.1 pivote table component that can use both SQL Server database (SQL queries) and Analyse Services database (MDX queries).

Thanks.

I have two links one is for 1.1 or 2.0 and the other is 2.0 and 3.0. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en
http://sqljunkies.com/WebLog/mosha/archive/2006/10/08/xaml_pivottable.aspx

|||

Thanks, but this links about Windows components. And I need asp.net component.

|||

Try these.

http://www.microsoft.com/downloads/details.aspx?familyid=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en

http://www.mosha.com/msolap/util.htm#ExcelAddIns