Downloading data from our Informix server

Our entire sales database sits on an Informix server and users run their reports directly to this server.

Because of the volume of data - upwards of 7 million rows in some tables we are finding performance is becoming an issue. We realise that Informix is not the best for running queries and so are looking to replicate to a SQL server and use that as a reporting server.

My chosen method is DTS using ODBC which works fine. However due to the sheer volume of data to be downloaded each night (over a 512K wan link) we only want to download changed data.

Is there any way we can get SQL2000 to download only change data - i.e a differential download or something? We don't have a unique field in the table (for whatever reason) such as sales date or transaction ID (shops might upload old / back-dated transactions for example due to a systems failure).

If I do have to download all the data every night is my method the most efficient or is there some other approach I could use?

Ultimatly the aim here is to relieve the Informix server of the reporting overhead so any alternative ideas are welcome!

Many thanksHi
The normal way of doing this sort of thing would be replication - this would give you your "differential downlaod".
I am not sure what one can do repl wise between informix & mssql but would be worth checking it out...
Des
(otherwise can use tracking tables which record max id last copied & copy only from there each day - use vars in DTS package, seeded by values in the tracking tables)|||Which version of informix are you running - believe it or not informix is very good at running queries, so I would first check to see if either the queries are not optimized correctly, check for poor database design and also look at the onconfig file to make sure the server itself has the appropriate settings.

To solve the transfer issue - probably you best method is to dump the data and use sql server to bulk copy (bcp) or bulk insert the data. You can use some identifier within Informix such as a date/time stamp to find the changed records - however, I go back to there is probably something either designed poorly or misconfigured on your informix box that is causing this poor performance.

double loop in stored procedure

Dear All,

Im working on a stored procedure that meant to mail out users some of their action items daily.

The procedure has a double loop, first the user ids and user email addresses are selected into a table, then the outer loop cycles through the user ids and selects relevant action items to another table. The inner loop then cycles through these action items and at the end of each outer loop a string is mailed out.

Problem is that as the outer loop selects the relevant items for a user, the table holding the action items basically gets filled with more and more records and the inner loop then adds every item in the table to the string that gets mailed out, ending up with more and more items going to all the users.

I have tried to delete all records from the actionItems table at the end of each outer loop after the content of the action Items are mailed out, however this seems to keep the actionItems table empty at all times.

Not sure if this description is clear enough but I cant see where Im going wrong in terms of approach.

Any ideas?I would suggest posting your SP here, so that we can check out what is going on in the T-SQL code.|||Any TSQL post that uses the word "loop" that frequently can't be good.|||Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. Cheers|||Whatever. Continue merrilly down your path to the dark side...|||Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. CheersHave you tried dropping charcoal briquettes or real cotton into fuming nitric acid?

-PatP

Don't want Trusted Connection option...

Hi,

Weird question, but hey!!

We have mixed-mode authentication on one of our databases.

Is there any way of avoiding the users seeing the "Use trusted connection" option when in the connection dialog (eg from Excel Import Data)

I would like to enforce use of the Username/Password option if possible.

Otherwise, I'm faced with implementing user-level SQL Server security & Windows authentication mode for an application which has, up until now, run as the only available app in a very restricted desktop environment.

All suggestions gratefully received.

TIA


SteveYou can provide a little form and ask for UID and PWD, use them in connect string instead of doing nothing and letting system decide what to do, in this case it provides generic login dialog box.

Don't want Trusted Connection option...

Hi,
Weird question, but hey!!
We have mixed-mode authentication on one of our databases.
Is there any way of avoiding the users seeing the "Use trusted connection"
option when in the connection dialog (eg from Excel Import Data)
I would like to enforce use of the Username/Password option if possible.
Otherwise, I'm faced with implementing user-level SQL Server security &
Windows authentication mode for an application which has, up until now, run
as the only available app in a very restricted desktop environment.
All suggestions gratefully received.
TIA
SteveSteve,
First of all security level is set at server level not per database. If I
understood you correctly, you want to use SQL Authentication for one
database and keep mixed mode for all other database
To stop user connecting to database using Trusted connection.
Deny BULITIN\Administrator account
make sure no windows user has permission on that database
create sql user account in that database
create dsn to use the new user name/password and use the new dsn to connect
to sql server
VT
"CyberDwarf" <steve.ingle@.furlongsolutions.com> wrote in message
news:Ory%23$IDIHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Weird question, but hey!!
> We have mixed-mode authentication on one of our databases.
> Is there any way of avoiding the users seeing the "Use trusted connection"
> option when in the connection dialog (eg from Excel Import Data)
> I would like to enforce use of the Username/Password option if possible.
> Otherwise, I'm faced with implementing user-level SQL Server security &
> Windows authentication mode for an application which has, up until now,
> run as the only available app in a very restricted desktop environment.
> All suggestions gratefully received.
> TIA
>
> Steve
>|||Thanks VT.
I was already working down that route, but it's nice to have my reasoning
confirmed
Steve|||You may be interested in the route we have taken...
a.. Create a project custom property to hold the (encrypted) connection
string;
b.. In VBA code, ensure that Persist Security info is turned OFF;
c.. Make sure sure that all Windows users have no rights to access SQL
Server (ie, they won't be able to use a Trusted Connection);
d.. When the ADE starts, we look up the required connection info from the
custom property, connect to SQL Server using the required access
permissions, then remove the Persist Security setting (the project loses the
password);
e.. At this point we have our required connection and no-one else can
access the backend via our ADE, unless they know the admin logon &
password).
That's basically it!
We've done basic testing & it seems to work...

Dont display address if its not there!

hi there, i have a invoice template that when printed has a box for the users address (which there always will be) and a box for their delivery address if they are having the items delivered. However sometimes they dont have things delivered and so the record in the delivery table does not exist. In this case it will throw an error, how can i avoid this. the code im using i have posted below

string sql ="SELECT [del_address], [del_post_code], [del_date], [del_time] From tbl_del WHERE order_ID = " + intOrderID;

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmd =newSqlCommand(sql, myConn);

myConn.Open();

SqlDataReader dr = sqlCmd.ExecuteReader();

//This reads the first result from the sqlReader

dr.Read();

try

{

//string strDel_Address = dr["del_address"].ToString();

if (Convert.ToString(dr["del_charge"].ToString()) !=null)

{

//delivery items

lblDelAddy.Text = dr["del_address"].ToString();

lblDelPCode.Text = dr["del_post_code"].ToString();

Instead of "dr.Read();" use "while (dr.Read())" If there is no record it wont even try to fill the items

while (dr.Read()){//Do something}

|||

ah thank you, the problem though is that there is this line

if (Convert.ToString(dr["del_charge"].ToString()) !=null)

and several lines like it below which rely on it bringing something back, how could i change it so if it dosnt exist almost to not do anything?

|||

When you usewhile (dr.Read()) if there are no records it will not go into that block of code. This will solve your problem, give it a try.

Don't Auto Run with default parameters

Is there a way to not have a report run when all of the default parameters
are set?
We would like to give the users some defaults so they don't have to type as
much when they run a report.Unfortunately there is not. There have been many request for this feature.
If you can wait for Yukon then you could use the viewer control which you
could give this behavior.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:B3C08BBF-2D07-4941-A986-BA89E122E346@.microsoft.com...
> Is there a way to not have a report run when all of the default parameters
> are set?
> We would like to give the users some defaults so they don't have to type
> as
> much when they run a report.

Domain Users Rights

I have a problem that im using Active Directory in Win2k Server, when domain users logon to workstations they can't open sql server databases, SQL server is local installed on Workstation and operating system is XP.

When i give administrator rights to users the sql server works fine.

Tell me is there is a way to use sql server without giving to the user administrative rights?

thanksYou need to login as the account that the SQL Server is running in on their boxes (set up a SQL Server "account". You then need to go into SQL Server while logged in as this account and add Domain Users as a login in SQL Server and grant permissions. They can then login and do what they need to do.

Domain User privilege for connecting SQL server 2000

Hi,

My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and

with Domain Admin privilege, my app is running ok.

I asked admin person to upgrade domain user privilege to connect SQL server,

but he couldn't do this for security reason, and told it should work with domain user.

Is it true for domain user to connect SQL server without any problem?

Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?

Or inside users of my database section of SQL server EManager?

Thanks,

dixon

You have to create a login in SQL Server 2000 that corresponds to the domain user. Look at sp_grantlogin:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp

Thanks
Laurentiu

Domain Migration

I am trying to migrate users accounts from a 2000 Server to 2003 server I am changing domain names and the new domain a is a child domain off a parent domain. I have created a trust on both domain servers and the parent domain and I have created administrative users on each domain I am using the ADMIT migration tool and can get all the way through it then get an access denied when it trys to create the accounts. The knowledge bases on this say I need a Domain Admin user on each domains for the other domain. Being in a child domain it does not let me create this I have created users and added the admistrative group for each user this should give the rights to create the users on the new domain, but still am getting the access denied.

Does anyone know what I am missing on this? Any help would be greatly appreciated.

thanks,

Shawn

I would suggest posting this question on a Windows forum, given that you are doing Windows operations, not SQL Server operations.

Thanks
Laurentiu

|||

Is your login is a part of domain and local admin on both of those domains?

As suggested above its better to search through the Windows related forums, if this is not a SQL related issue.

Domain Groups and Windows Authentication

Hi

We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.

So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.

The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.

For creating the groups I have the following options:

Create a domain group and put all the usergroups from the other domains in this group

Add user groups from all other domains directly into the SQL Server.

Any recommendations here?

YOu don′t need to add the single users to the database. If you put the domain groups (which contain the users) to the server and the database principles, SQL Server will take care of doing the authentication.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

HI,

If you are adding your domain groups to your SQL Server and grant them to allow access of DB/objects then Windows Server will take care of logins to SQL Server tooo do not need to do anything separately.

Domain group server access?

Hi,
If a user needs access to a databases and I carry out the following steps:
1) Create a domain group
2) Put the users login details in this group
3) Give the domain group access to SQL Server via Enterprise Manager
4) Give the domain group database access via Enterprise Manager
Do I also need to give this domain group direct server access by adding it
to a group in:
Computer Management\Local Users and Groups\Groups ?
Ta
wendyalso, we are using active directory.
wendy
"Woo" wrote:

> Hi,
> If a user needs access to a databases and I carry out the following steps:
> 1) Create a domain group
> 2) Put the users login details in this group
> 3) Give the domain group access to SQL Server via Enterprise Manager
> 4) Give the domain group database access via Enterprise Manager
> Do I also need to give this domain group direct server access by adding it
> to a group in:
> Computer Management\Local Users and Groups\Groups ?
> Ta
> wendy|||No you don't need to give OS level permissions on the server
that is running SQL Server for the group to access SQL
Server. You only need to add the windows group to the SQL
Server logins (and whatever databases).
-Sue
On Fri, 3 Nov 2006 04:14:02 -0800, Woo
<Woo@.discussions.microsoft.com> wrote:

>Hi,
>If a user needs access to a databases and I carry out the following steps:
>1) Create a domain group
>2) Put the users login details in this group
>3) Give the domain group access to SQL Server via Enterprise Manager
>4) Give the domain group database access via Enterprise Manager
>Do I also need to give this domain group direct server access by adding it
>to a group in:
>Computer Management\Local Users and Groups\Groups ?
>Ta
>wendy|||And you will have to give the windows group permissions to access tables,
views, stored procedures, etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bcjmk2h4eoundvep74genb5d43ldjveog2@.
4ax.com...
> No you don't need to give OS level permissions on the server
> that is running SQL Server for the group to access SQL
> Server. You only need to add the windows group to the SQL
> Server logins (and whatever databases).
> -Sue
> On Fri, 3 Nov 2006 04:14:02 -0800, Woo
> <Woo@.discussions.microsoft.com> wrote:
>
>

Domain Collapse. Sql permission question.

Internally sql server uses SID of NT users to link them
with sql logins. I don't know if NT will preserve SIDs
when users are migrated to another domain. If it does then
users can login with Domain2\user1 and still have same
permissions in sql server based on their SID stored in
sysxlogins table, though their username will be displayed
as Domain1\user1. You can update sysxlogins table and
change users display name.
If users are assigned new SID by NT then I am afraid you
have to add them again and assign permisstions.
hth.

>--Original Message--
>We are in the process of collapsing a domain. How do i
change the database permissions to reflect the new domain
names. Example Domain1\user1 is migrated to Domain2. user1
is now Domain2\user1. In SQL, the user is still displayed
as Domain1\User. Any idea. Thanks in advance.
>.
>When they are migrated to nt the sid stays the same. so the permission shoul
d be ok from your info. Thanks for the help.

Domain change for logins & users

Does anyone have script(s) to change logins & users from 1 domain to another
?
Or know of a Microsoft paper on how to do this programatically? We're
merging 2 domains in our company and I have several SQL2000 servers (each
with multiple databases) in the domain that's going to be dropped.
I think the script would be something like:
update sysxlogins set name = 'OLDDOMAIN\someuserid'
where name like 'NEWDOMAIN\domuserid'
but how do I dynamically read & replace the id after the \ ? And what about
users? And are there other tables I need to update?
Any help would be appreciate and thanks in advance!Missy,
Please do not directly update sysxlogins. What will happen to your SIDs?
Do something like this:
sp_helplogins -- returns all the logins and database roles to which they
belong. (But not individual object permissions.)
If necessary, run sp_helprotect for each old login to get further rights for
the new logins. Script those as well.
From this information, create a script that creates new logins with the
proper domain.
exec sp_grantlogin 'Newdomain' + substring(Loginame, CHARINDEX('',
Loginame), 50)
Run appropriate scripts in each database to assign the new logins to the
proper roles and to add the other rights that sp_helprotect might indicate.
Once the New Logins are set up, remove the old logins or do an sp_denylogin
for each until your are sure that your definitions are fine.
RLF
"Missy" <Missy@.discussions.microsoft.com> wrote in message
news:94F749B6-FB39-4789-9F64-F43433C4611E@.microsoft.com...
> Does anyone have script(s) to change logins & users from 1 domain to
> another?
> Or know of a Microsoft paper on how to do this programatically? We're
> merging 2 domains in our company and I have several SQL2000 servers (each
> with multiple databases) in the domain that's going to be dropped.
> I think the script would be something like:
> update sysxlogins set name = 'OLDDOMAIN\someuserid'
> where name like 'NEWDOMAIN\domuserid'
> but how do I dynamically read & replace the id after the \ ? And what
> about
> users? And are there other tables I need to update?
> Any help would be appreciate and thanks in advance!|||Russell,
I was hoping the SIDS would remain the same if I simply changed the
Domain\User in sysxlogins and then also in sysusers.
I've looked at article Q240872, but I'm not sure if the sp_sidmap will help
or not as it only alters sysusers.
Do you know anyone who has done this? There's got to be sites that have
changed Domains and needed to update their sql servers programattically...
Thanks,
Missy
"Russell Fields" wrote:

> Missy,
> Please do not directly update sysxlogins. What will happen to your SIDs?
> Do something like this:
> sp_helplogins -- returns all the logins and database roles to which they
> belong. (But not individual object permissions.)
> If necessary, run sp_helprotect for each old login to get further rights f
or
> the new logins. Script those as well.
> From this information, create a script that creates new logins with the
> proper domain.
> exec sp_grantlogin 'Newdomain' + substring(Loginame, CHARINDEX('',
> Loginame), 50)
> Run appropriate scripts in each database to assign the new logins to the
> proper roles and to add the other rights that sp_helprotect might indicate
.
> Once the New Logins are set up, remove the old logins or do an sp_denylogi
n
> for each until your are sure that your definitions are fine.
> RLF
> "Missy" <Missy@.discussions.microsoft.com> wrote in message
> news:94F749B6-FB39-4789-9F64-F43433C4611E@.microsoft.com...
>
>

domain accounts & SQL logons ...

we added a user's PC to the local domain which also has SQL Server 2K sp3
running on it. prior to adding the user's PC to the domain the user would
logon to his local machine which had the same username & password combo for
the domain. his sqlserver account name was domainname/username which worked
okay. now that his PC has been added to the domain, however, he can access
all resources on the domain except for the SQL server to which he is an
admin. he receives the "Cannot generate SSPI context" error message. as
soon as his PC is removed from the domain he can connect fine. tia for any
help on this one ...
Had a similar issue once, I think I managed to "fix" it by going into the
client network utilty and forcing the protocol being used to TCP/IP for that
server, or was it to Named Pipes ? Can't rememer, but worth a try =)
Good luck,
Roby
"E-Double" <EDouble@.discussions.microsoft.com> wrote in message
news:8E5D9C1F-D630-4202-98FA-AB614E4ECCDF@.microsoft.com...
> we added a user's PC to the local domain which also has SQL Server 2K sp3
> running on it. prior to adding the user's PC to the domain the user would
> logon to his local machine which had the same username & password combo
> for
> the domain. his sqlserver account name was domainname/username which
> worked
> okay. now that his PC has been added to the domain, however, he can
> access
> all resources on the domain except for the SQL server to which he is an
> admin. he receives the "Cannot generate SSPI context" error message. as
> soon as his PC is removed from the domain he can connect fine. tia for
> any
> help on this one ...

Domain Accounts

Is it possible to assign users to Active Directory groups
and have this one group account granted access in the
database with the appropriate permissions. As oppose to
having many individual Windows domain accounts.
Thanks,
Angelina
Hi angelina
You can grant a login to a group as well as individuals see the
documentation for sp_grantlogin in Books online.
If you allow access to the group all member will have that permission and
therefore it is easier to administer.
John
"angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:43f901c42b7f$249f7f10$a101280a@.phx.gbl...
> Is it possible to assign users to Active Directory groups
> and have this one group account granted access in the
> database with the appropriate permissions. As oppose to
> having many individual Windows domain accounts.
> Thanks,
> Angelina

Domain Accounts

Is it possible to assign users to Active Directory groups
and have this one group account granted access in the
database with the appropriate permissions. As oppose to
having many individual Windows domain accounts.
Thanks,
AngelinaHi angelina
You can grant a login to a group as well as individuals see the
documentation for sp_grantlogin in Books online.
If you allow access to the group all member will have that permission and
therefore it is easier to administer.
John
"angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:43f901c42b7f$249f7f10$a101280a@.phx.gbl...
> Is it possible to assign users to Active Directory groups
> and have this one group account granted access in the
> database with the appropriate permissions. As oppose to
> having many individual Windows domain accounts.
> Thanks,
> Angelina

Domain Accounts

Is it possible to assign users to Active Directory groups
and have this one group account granted access in the
database with the appropriate permissions. As oppose to
having many individual Windows domain accounts.
Thanks,
AngelinaHi angelina
You can grant a login to a group as well as individuals see the
documentation for sp_grantlogin in Books online.
If you allow access to the group all member will have that permission and
therefore it is easier to administer.
John
"angelina" <anonymous@.discussions.microsoft.com> wrote in message
news:43f901c42b7f$249f7f10$a101280a@.phx.gbl...
> Is it possible to assign users to Active Directory groups
> and have this one group account granted access in the
> database with the appropriate permissions. As oppose to
> having many individual Windows domain accounts.
> Thanks,
> Angelina