I'VE 3 QUESTIONS

FIRST, If I make a view like this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\";Extended properties=DBase III')...dav
So If it is called from Client, Does Data to be read from client's D:\ or
server's D:\?
If Data is still read from server, how can data be read from client?
Second, At Ms Access, I ever make query like this.
SELECT noid, FIRST(Fddate) AS fdate
from TB1
GROUP BY noid
I wanna make like it in SQL Server 2000. Can I do it?
Third, I've data like it
field1 field2
--
a1 3
a1 4
a1 23
b1 35
b1 30
b1 31
I wanna delete records, but first record of group (field1) is not deleted.
How syntax SQL to do it?> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?

> If Data is still read from server, how can data be read from client?
Its read from the server, if you want to read it from the client you have
to put the data on a network share that the server can reach and open it.

> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
With no background information thatll be just a guess to, but you can use
semething like MIN()

> How syntax SQL to do it?
Delete
From SomeTable ST
Where field2 NOT IN
(Select TOP 1 field2 From sometable Where ST2.field1 = ST.field1 order by
field2)
HTH, Jens Suessmeyer.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eH9MZy2jFHA.3448@.TK2MSFTNGP12.phx.gbl...
> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>|||> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
It is read from the server. To read it from the client, try using the UNC
name of the shared folder in the "data source".

> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
Use MIN or MAX aggregate functions.

> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
delete t1
where exists(select * from t1 as a where a.field1 = t1.field1 and a.field2 <
t1.field2)
--or
delete t1
where field2 > (select min(a.field2) from t1 as a where a.field1 = t1.field1
)
AMB
"Bpk. Adi Wira Kusuma" wrote:

> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>
>

Iterating Data Movement

Hi all

I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.

I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:

1) It runs in a tight loop and consequently degrades system performance enormously.

2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.

Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.

All help is appreciated.

Jays :-)

I think you want a trigger on the table so you know when the data in that table is changed, and copy it accordingly.

|||

Hi Christian

I would like to avoid that if I can.

My package has custom logging that writes a set of audit records each time it is run. This would result in a LOT of these records being written throughout the day when one such set would do.

Thanks

Jays :-)

Iterate through all Excel files and all their sheets

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.

IT WORKED!

Hello everyone,

i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.

Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?

thanks a lot.

Have you tried using Data Conversion transform...

you could use an expression like

IsNull(Column) ? ValueifNull : ValueifNotNull

See some examples here:

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

|||Thanks a lot, the code and the article helped a lot.

I used an Derived Column Transformation to parse null values to zeros with the expression. The good thing is that i don't loose the mapping to the old columns. that's great.

Additionally this article helps to understand Derived Column Transformation:
http://msdn2.microsoft.com/en-us/library/ms141069.aspx

regards amigo|||

So, you got it!

That's great.

Issues with SourceSafe integration with Sql Server Management Stud

We have a Sql Server Manageent Studio Solution, that on loading gives the
following error:
[ Source Control ]
There appears to be a discrepancy between the solution's source control
information about some project(s) and the information in the project file(s)
.
To resolve this discrepency it will be necessary to check out the project
file(s) and update them. If the check out fails, however, and the solution i
s
closed without saving, you will see this warning again the next time you ope
n
the solution.
[ OK ] [ Help ]
After which it sucessfully checks out one of the projects within the
solution, but makes no changes to it. Is the user (i.e. me) expected to do
something to resolve the issue?
I've tried unbinding source control fro the project that gets checked out,
saved all, rebound, saved all, to no avail.
None of the projects are shared with any other solution files nor are they
shared within VSS.
My normal avenues of finding the cause for these things is coming up blank
-- possibly because the Ctrl+C to copy the text of the error message does no
t
work (presumeably the error dialog is not created with the MessageBox API,
per http://blogs.msdn.com/oldnewthing/a.../29/412577.aspx )Hi Rowland,
Thank you for your posting!
To keep the integrity of newsgroup, we encourage you post only one post in
the newsgroup so that other communities may also benefit from this. We will
reply the post in microsoft.public.vstudio.sourcesafe and follow up your
any questions there.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
.... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-
H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>
|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>
|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
sql

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>>I recently migrated Database1, which contains Table1, from one SQL Server
>> 2000 box to another (Source and Dest, I'll call them). This is probably
>> the
>> 50th database migration I've performed within the past few months. My
>> general steps for migration are:
>> 1. Create and execute the sp_help_revlogin stored procedure on Source to
>> copy all the SQL Server logins from Source to Dest.
>> 2. Copy the sp_help_revlogin output and execute on Dest server.
>> 3. Backup Database1 on Source, and restore Database1 on Dest.
>> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
>> orphaned logins on Dest's Database1.
>> With Database1, there are many database objects, tables, created using
>> the
>> login "User1." Here's where I'm stumped.
>> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
>> I'm
>> logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get my resultset, no issues.
>> 2. After the migration, when I try to select from the User1-created
>> Table1
>> on *DEST*, while I'm logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line
>> 1.
>> Invalid object name 'TABLE1'" and *no resultset*.
>> At least I'm relieved then when I try fully qualifying the table
>> reference:
>> SELECT * FROM USER1.TABLE1
>> ... I get my resultset back with no error messages.
>> 3. Next, I try assigning User1 to the System Administrators server role
>> in
>> Database1 on *DEST* to try to find a way around the issue above. No
>> dice--
>> same error message. Also, viewing the Users choice under Database1 on
>> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
>> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
>> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
>> message,
>> "The number of orphaned users fixed by updating users was 0." Also,
>> trying
>> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab,
>> from
>> Database1, gives me "Error 15183: The user owns objects in the database
>> and
>> cannot be dropped." (I was experimenting to see if I can drop and re-add
>> user.)
>> The problem is-- application dependencies exist where the application
>> connects to the database server as User1, and appears to *not* fully
>> qualify
>> table access. This is a legacy application that's existed without
>> modification for years and I'm afraid can't be modified to have qualified
>> table access.
>> However, I'm stumped by the issue. I'm wondering what variables and
>> configuration settings I haven't considered yet that, on SOURCE, would
>> allow
>> me to *not* qualify table access and be OK with it, yet on DEST, seem to
>> require table qualification.
>> Any help and assistance would be greatly appreciated in advance.
>> Thanks,
>> - H-
>>
>>
>|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>>H Lee,
>> First, true confession: I did not read the whole thing and attempt to
>> understand it all. However...
>> Your process of backup and restore has a common flaw. This can result in
>> the restored database having a different owner of the database from the
>> internal user mapped as dbo. If this is your case, then you should do
>> the following:
>> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
>> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
>> If this works, then great.
>> RLF
>>
>> "H Lee" <anon@.anon.com> wrote in message
>> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>>I recently migrated Database1, which contains Table1, from one SQL Server
>> 2000 box to another (Source and Dest, I'll call them). This is probably
>> the
>> 50th database migration I've performed within the past few months. My
>> general steps for migration are:
>> 1. Create and execute the sp_help_revlogin stored procedure on Source to
>> copy all the SQL Server logins from Source to Dest.
>> 2. Copy the sp_help_revlogin output and execute on Dest server.
>> 3. Backup Database1 on Source, and restore Database1 on Dest.
>> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
>> orphaned logins on Dest's Database1.
>> With Database1, there are many database objects, tables, created using
>> the
>> login "User1." Here's where I'm stumped.
>> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
>> I'm
>> logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get my resultset, no issues.
>> 2. After the migration, when I try to select from the User1-created
>> Table1
>> on *DEST*, while I'm logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line
>> 1.
>> Invalid object name 'TABLE1'" and *no resultset*.
>> At least I'm relieved then when I try fully qualifying the table
>> reference:
>> SELECT * FROM USER1.TABLE1
>> ... I get my resultset back with no error messages.
>> 3. Next, I try assigning User1 to the System Administrators server role
>> in
>> Database1 on *DEST* to try to find a way around the issue above. No
>> dice--
>> same error message. Also, viewing the Users choice under Database1 on
>> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
>> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
>> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
>> message,
>> "The number of orphaned users fixed by updating users was 0." Also,
>> trying
>> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab,
>> from
>> Database1, gives me "Error 15183: The user owns objects in the database
>> and
>> cannot be dropped." (I was experimenting to see if I can drop and
>> re-add
>> user.)
>> The problem is-- application dependencies exist where the application
>> connects to the database server as User1, and appears to *not* fully
>> qualify
>> table access. This is a legacy application that's existed without
>> modification for years and I'm afraid can't be modified to have
>> qualified
>> table access.
>> However, I'm stumped by the issue. I'm wondering what variables and
>> configuration settings I haven't considered yet that, on SOURCE, would
>> allow
>> me to *not* qualify table access and be OK with it, yet on DEST, seem to
>> require table qualification.
>> Any help and assistance would be greatly appreciated in advance.
>> Thanks,
>> - H-
>>
>>
>>
>

Issues with Export to Excel file

I need to do a simple task. I have a oledb source which pulls data from sql server, creates an excel file abc.xls and dumps it with the Excel destination at remote location.

I need to create this file at runtime. However, when i give the location, the Excel Destination expects the file to be there for the Mapping of the source columns with destination ones.

My tasks are

1. Creating a table for the worksheet in Excel connection

2. Select from source table and write in Excel Destination

Will appreciate your response.

You can create the xls file at run time without problem; but the file need to exists at design time as SSIS needs to get the metedata from it.

I have a couple of posts that show some examples using excel files; I hope you find them helpful

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

issue with update

Hello,

I have the following PERFORMANCE issue:

I have created a job that fill and update a database from a source db with same structure.

The problem is that at the beginning performance was good, now the source db and destination db are very large and time to import/update is to big.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk.

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

How can I increase performance and reduce time of importing?

Thank

You can try the replication service instead of doing it manually, it will be faster than the DTS job.

If you want to it manually do the following operations,

1. Delete

2. Update

3. Insert

|||

Hello,

thank, but at the beginning we tried with replication but there are some problems, so we decides to use other methods such as the above one.

Now, I am thinking to create a trigger on each big tables to identify what record is inserted new or updated. The trigger populate a table that represent only these pk. In this way I can make a subset of record that are only new or updated.

Issue while using Transfer Sql Server Objects tasks

Hi
I am trying to use Transfer SQL Server Object task to transfer various
DB objects from a Source DB to a newly created Destination DB. The
Destination DB is blank before this attempted transfer.
Now our source Db contains various SPs and views which internally
reference other SPs/Views of the same DB (source DB). For example we
have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
transfer objects task, if we try to transfer all SPs only, we get an
error saying that SP2 does not exist while creating SP1. It seems SSIS
compiles objects while transfering them. We tried to set the
DelayValidation property of the entire package as well as the task to
True, but this didnt help either.
It would be great if someone could suggest some mechanism to prevent
compilation of objects, or any other method using which we can avoid
the problem that we are facing.
Thanks
Regards,
Piyush
I would redirect any failure to a logging table or other, or even ignore
errors.
On 1st pass sp1 will fail, but sp2 should still be created, correct? Then
perform a second pass which will cause sp2 to fail because already exists,
however sp1 will compile and create fine since dependency now exists...
You will get a quicker response if you post to dts group for ssis questions.
good luck.
ChrisB MCDBA
MSSQLConsulting.com
"whirlwnd" wrote:

> Hi
> I am trying to use Transfer SQL Server Object task to transfer various
> DB objects from a Source DB to a newly created Destination DB. The
> Destination DB is blank before this attempted transfer.
> Now our source Db contains various SPs and views which internally
> reference other SPs/Views of the same DB (source DB). For example we
> have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
> transfer objects task, if we try to transfer all SPs only, we get an
> error saying that SP2 does not exist while creating SP1. It seems SSIS
> compiles objects while transfering them. We tried to set the
> DelayValidation property of the entire package as well as the task to
> True, but this didnt help either.
> It would be great if someone could suggest some mechanism to prevent
> compilation of objects, or any other method using which we can avoid
> the problem that we are facing.
> Thanks
> Regards,
> Piyush
>

ISSUE WHILE CONNECTING TO ORACLE SOURCE WITH 64 BIT processor SQL SERVER SSIS

Hello All,

I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.

The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:

"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"

Regards,

Raju

Hello All,

The above package is running fine with sql server 32 bit development but not running in 64 bit production server.

The oracle client that has been installed on the production server is a 32 bit, because our source database is an oracle 32 bit not 64 bit which is residing in another server.

Hope this additional information will help you people get it resolved.

Regards,

Raju

|||

Hi Raju,

If you want to connect to Oracle using a 32bit driver, then you should execute the SSIS package using the 32bit version of DTEXEC.

Regards,

Christian

Issue Loading Data from Flat File

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

Philip Coupar wrote:

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

I'm working with Philip on this. Basically it looks as though there's problem in the way that Excel saves stuff to csv. Anyone know why?

-Jamie

|||

We are experiencing the same exact problem. The CSV file imports fine using our old DTS Packages just not with the new SSIS packages we have created. I can provide sample csv files if needed.

If you have an update on this, please let me know.

|||Did you see the workaround that Philip posted (above)?

-Jamie

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank

issue

i have a data source which is a weblogs in which data is stored in ncsa format using ssis i need to get that data to one of my database tables

can u guys plz help me out in this.

i.e to to build the ssis scenario

thanks

praneeth

sample weblog

10.196.2.236 - wromero@.kpmg.com [26/Jun/2007:12:04:44 -0400] "POST /krisk3/krisk3/(ea12r4f4nin0ta55h0suj445)/Evaluation/Evaluation.aspx HTTP/1.0" 200 42925

You could use a script source to read the file in line by line and parse it into columns. Are the lines formatted consistently?

|||

I would be inclined to import the whole lot into a single-column dataset and then parse it using derived column steps.

This would work fine if your line length is relatively predictable (by which I mean you have no long text blocks in the entries) and you have a consistant end-of-line (no CR/LF in the middle of a line.)

If your data is more complex, take a look at using a script step to parse each line.

|||

Check the Fixed Width text file option. You can retrieve the columns as required. You can even pictorially see the reperesentaion in SSIS of your data.

Virendra