Finding Errors on 64bit Itanium

I have an SSIS job that is exiting with an error on a 64 bit Itanium SQL 2005 sp1. It has three parallel streams of execution. I've been running my tests using dtexec with verbose output being sent to a file.

2 of the streams execute several truncates and then are followed by 4 steps that load data from a SQL 2000 server to a SQL 2005 server. One of the job streams sources it's data using a SQL Server username/password while the other sources it's data using integrated security. Both streams use integrated security for the destinations.

The third job truncates 2 tables and then loads those two tables from an Oracle database. The oracle connections use a username/password in a connection string in a configuration database while the SQL side of the connection uses integrated security.

I've validated security is working, I've validated the drivers for Oracle are working in 64 bit mode and 32 bit mode for the connection strings I'm using, and security for the package. To top it off, this package runs to completion in 32bit mode under the same credentials.

The only warning messages I get running in verbose mode are the following warnings from the Oracle connection:

OnWarning,311-SQL-01,CHARLOTTE\!lcharlton,Import Employee Locator,{EE2C1DB7-A767-4AE6-81F4-6FAE6BFBD2CF},{DF323107-1ABD-4790-A318-B046862C6F58},10/19/2006 2:24:55 PM,10/19/2006 2:24:55 PM,-2145378202,0x,Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Any insights into how to troubleshoot this or possible additional steps I could take are warmly welcomed.

LarryC

The warning you see is due to the OLE DB adapter not being able to retrieve code page information from Oracle. It's a warning and does not usually terminate execution. What error message do you see when execution terminates? How did you validate that the 64-bit Oracle provider is working correctly? Did you do this by executing your package in 64-bit?

Bob

|||

There are no error messages. I've both turned on every event type and every column of information and run the dtexec in verbose mode.

To validate the connection would work in 64 bit I created a VB script using the same connection string and had it display a value from a table. While the script was running I checked the processes to validate whether that was running in *32 mode or native mode.

sql

finding average earning over period of employment

It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

find the average monthly earning while you have been an affiliate

affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

spavgmonthlyearning affiliate_id

any help would be greatly appreciate even a point in the right direction would be fantastic.so this will do it.
is this pure sql or will it only run on sqlserver
thank you for your help

DECLARE @.maxdate as datetime
DECLARE @.mindate as datetime
set @.maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
set @.mindate = (select min(trans_dt) from dbo.affiliate_log)
select sum(earning)/datediff(M,@.mindate,@.maxdate)
from dbo.affiliate_log where affiliate_id = 1|||select DATEPART(m,date_field), avg(sales)
from table
group by DATEPART(m, date_field)|||It depends on what you mean by "average".

The simple average monthly earning would just be the total earnings divided by the total months.

select affiliate_id,
sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
from yourtable
group by affiliate_id

Or if they skip some months:

select affiliate_id,
sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
from yourtable
group by affiliate_id

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr


But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output

find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez

Find Fileds Type

How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?You could start by look at syscolumns|||Here is what I do in the EntityBroker O/R mapper (in the part about syncrhonizing database schemata) fto find out how the db does look at the moment.

Field types:

::SELECT *
::FROM information_schema.columns
::INNER JOIN information_schema.tables
::ON information_schema.columns.table_catalog = information_schema.tables.table_catalog
::AND information_schema.columns.table_Name = information_schema.tables.table_name
::ORDER BY information_schema.columns.table_catalog,
::information_schema.columns.table_schema,
::information_schema.columns.table_name, information_schema.columns.ordinal_position