Losing Oracle user name and password

I have written a simple SQL Server 2005 package to pull some data from Oracle (using ODBC) and pumping it into SQL Server. When I run it from the server in debug mode in VS it works fine. When I schedule the job it errors out with "ora-01005: null password given; logon denied." The password is there. Has anyone experienced this? Is there a security setting somewhere preventing me from saving passwords? Is there a work around? Thanks.

Passwords are not saved in a package unless in an encrypted format. Check the ProtectionLevel property of your package to set this up.

-Jamie

|||Thanks, that was it.sql

looping thru a table and updating the contents

Hello, Its hard trying to explain this.

I have 3 tables

Table 1 is where the users are stored, each user has a username and a userrank

Table 2 is where the points that decides the userrank are stored

Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:
username nvarchar(20),
userrank int, -- Reference to Table3 id
... alot more fields

Table 2 (settings_profile) looks like this:
username nvarchar(20),
total_active_points int,
... some more fields

Table 3 (data_ranks) looks like this:
id int primary key auto inc,
rankname nvarchar(20),
min_pts int,
max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:

WHILE(SELECT @.username= username, @.id= id, @.temp1= ap_sentmails, @.temp2= ap_createdthreads, @.temp3= ap_createdanswers, @.temp4= ap_signguestbook, @.temp5= ap_blogcomment, @.temp6= ap_createblogentry, @.temp7= ap_profilefirsttime, @.temp8= ap_profilephoto, @.temp9= ap_activateguestbook, @.temp10= ap_addnewfriend, @.temp11= ap_superguruvote, @.temp12= ap_forumtopicvote, @.temp13= ap_labervote, @.temp14= ap_funstuffitemvote, @.temp15= ap_movievote, @.temp16= ap_actorvote, @.temp17= ap_money_newWHERE(created<Dateadd(dd,-7, @.todaysdate))
BEGIN
SET @.sum= 0
SET @.sum= @.temp1+ @.temp2+ @.temp3+ @.temp4+ @.temp5+ @.temp6+ @.temp7+ @.temp8+ @.temp9+ @.temp10+ @.temp11+ @.temp12+ @.temp13+ @.temp14+ @.temp15+ @.temp16+ @.temp17
UPDATE settings_profileSET total_active_points= total_active_points- @.sumWHERE(username= @.username)
DELETEFROM konto_daylistWHERE(id= @.id)
END

Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.
Something like this...

DECLARE @.username nvarchar(20)
DECLARE @.pts int, @.rank int

...something that starts a loop thru table A (user_list) and get the username into @.username...

SELECT @.pts =total_active_points FROM settings_profile WHERE (username = @.username)
-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranks
SELECT @.rank = id FROM data_ranks WHERE (pts_min => @.pts AND pts_max < @.pts)
UPDATE user_list SET rank = @.rank WHERE (username = @.username)

...next persion in the loop...

This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...

But how do i loop thru all the users? with a cursor?

I don't have time to write this for you, but here's the line of attack to use:

Create and test a query that computes the the correct score for each user.

then, issue an update in this format:

update settings_profile

set total_active_points = ( the query from above where settings_profile.username = other_table.username)

|||

Hi, i dont truly understand what you meant. I can compute the score for each user If i have their username, thats why i need to create a loop that loops thru every post in table A, so that i can update every single post with the rank they earned from the points. I need to find someway to loop thru all posts.

|||

No, you do not need to write a loop thru the users.

This is because the SQL Update statement provides an implied loop for you.

Example:

createtable xxx(idint, textvaluevarchar(20))

insertinto xxxvalues(1,'Nikki')

insertinto xxxvalues(2,'John')

update xxxset textvalue='Ginger'

select*from xxx

You will note that every xxx record now has Ginger in the textvalue column.

The update statement IS a loop!

And, while it is processing any given xxx record, it knows the value in each of that record's columns.

I do not have to hard-code the value that will be used to set textvalue with.

I can issue a query instead.

So, let's create a new table called nametable.

createtable nametable(idint,namevarchar(20))

insertinto nametablevalues(1,'Amber')

insertinto nametablevalues(2,'Tracy')

update xxx set textvalue = (select name from nametable where nametable.id = xxx.id)

select * from xxx

Note that the name for record 1 is now Amber and the name for record 2 is now Tracy

Hope that helps clarify things.

|||

Hi,

Its working like a clock now, thanks for the explanation.

I build this line with the example code you sent, and its brilliant.

UPDATE profile_publicinfoSET userrank=(SELECT data_userrank.idFROM settings_profileINNERJOIN data_userrankON(data_userrank.pointsmin<= settings_profile.total_active_pointsAND data_userrank.pointsmax> settings_profile.total_active_points)WHERE(settings_profile.username= profile_publicinfo.username))

|||

Great!

Now all you have to do is mark the thread as answered, so other folks don't spend their time trying to help you only to find out you don't need help anymore! :)

The key to being ultra-productive with SQL is to think in terms of "sets of data" instead of looping thru records of data one at a time.

|||

Then i just have to ask, is it possible to update several records with a IF statement?

Like this,

UPDATE clubsSET isactive=(IF(clubs_account.money-clubs_account.cost< 0SELECT 1) ELSE (SELECT 0)FROM clubs_account)

Thats money - expenses, if its less then 0, return a 1 otherwise return a 0

|||

The SQL equivalent of an IF statement is a CASE statement.

But don't try to write it the way your if statement was coded.

Try this instead:

update clubs
set isactive = (select case clubs_account.money - clubs_account.cost < 0
when true then 1
else 0
end case
from clubs_account
where clubs_account.? = clubs.?
)

|||

Looks very good, but i just keeps getting an error when i tried it (i also tried to put () around the math calculations, but i still get the same result),

The clubs.inactive is a bit field if that is of any importance.

UPDATE clubsSET isactive=(SELECTCASE club_konto.money- club_konto.cost< 0WHEN trueTHEN 1ELSE 0ENDCASEFROM club_kontoWHERE club_konto.clubid= clubs.id)

Incorrect syntax near '<'.

|||

bit fields are 1s and 0s, not true and false.

Also, I work in too many different languages. :( It's END, not END CASE.

createtable clubs(idint, isactivebit)

createtable club_konto( clubidint,moneyint, costint)

insertinto clubsvalues(1,1)

insertinto clubsvalues(2,1)

insertinto clubsvalues(3,0)

insertinto clubsvalues(4,0)

insertinto club_kontovalues(1,50,10)

insertinto club_kontovalues(2,10,50)

insertinto club_kontovalues(3,50,10)

insertinto club_kontovalues(4,10,50)

select*from clubs

select*from club_konto

UPDATE clubs

SET isactive=(SELECTCASEWHEN club_konto.money- club_konto.cost< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

select*from clubs

I have to caution you about this - the select statement used to populate the isactive column must NOT return more than one row of data.

If it can return more than one row, you need to change it so it only returns one row. So, if there can be two or more club_konto records per club, the query above will not work. Instead you would need it to be something like this:

UPDATE clubs

SET isactive=(SELECTCASEWHEN sum(club_konto.money)- sum(club_konto.cost)< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

|||

I see that i need to learn alot more about how data is handeled inside MS SQL 2005, its working perfect with your example,

Thanks alot

(I have afew more SPs with While loops that i have to go thru now and see if i can upgrade them according to the new update way.)

Loop within a stored procedure

Hi,

Can I have a FOR loop within a stored procedure. Basically I want 2 loop through each user and send them an automatic email using Server Agent.

thanksThere's no FOR loop, but there is a WHILE loop. Look in BOL for full documentation on its use (I don't think I've ever actually used it, except in one or two rare cases where I broke down and used a cursor. Ah, foolish youth!)|||If you want to loop though a dataset, use a cursor:

declare NextRow cursor local for
select EmailAddress
from Table
where ...

open NextRow

and then you can get the rows one by one using fetch:

fetch next from NextRow into @.EmailAddress

Remember to check @.@.fetch_status to see if you're done.

if @.@.fetch_status = -1 -- you're done!
if @.@.fetch_status = -2 -- This row has been deleted.

Does this help?

Loop through all User Tables

Hi,
I would like to execute a sql statement on all user tables of my db. Do you
know how to script that this statement loops through all user tables?
Thanks in advance
Graham SmithMay this *undocumented* proc will help
EXEC sp_MSforeachtable 'SELECT TOP 1 * FROM ?'
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>|||You can generate one this way:
SELECT 'SELECT TOP 1 * FROM ['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID('['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'), 'IsMsShipped') = 0
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>

Looking for SQL 2000 Fulltext user in Wash DC metro area

We are just starting to use sql 2000 Fulltext functions and would like
a local technical person to discuss concepts with.
Thanks,
John B
I'm in NJ.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John B" <uofs76@.yahoo.com> wrote in message
news:49b5119.0409080316.2808c5ef@.posting.google.co m...
> We are just starting to use sql 2000 Fulltext functions and would like
> a local technical person to discuss concepts with.
> Thanks,
> John B
|||John,
I have worked for Microsoft for over 6 years and left Microsoft in 2002 in
order to pursue full time the research and writing of this book on Full-Text
Search (FTS). I have been involved with FTS in SQL Server 7.0 Beta 3 since
1998. I also was involved in getting the Microsoft SQL Server fulltext
newsgroup established and I've been an active poster ever since.
I'd be glad to help you with any technical questions regarding SQL Server
2000 Full-text Search (FTS) functions.
Regards,
John
"John B" <uofs76@.yahoo.com> wrote in message
news:49b5119.0409080316.2808c5ef@.posting.google.co m...
> We are just starting to use sql 2000 Fulltext functions and would like
> a local technical person to discuss concepts with.
> Thanks,
> John B

looking for security guidelines to avoid sql injection risks with xquery

Hi, i hope this forum can help me clarify my security concerns for my scenario:

I want a user to be able to filter sql rows by using xquery on an untyped XML that is stored in a XMLDataType column. My table will have multiple rows/columns, with one column being XML.

I would like to get some guidance on how a user can specify their "filter" condition string using the xquery syntax without harding coding any dependency on the server side (client decides which items they are interested in), and I would like to know the guidelines on how to translate the xquery on the server to avoid any sql injection risks. For instance, perhaps i should double all single quotes in the given xquery string. Also, i am concerned about sql:variables that may be able to access private variables inside a stored proc, has this been an issue?

Thanks!!

All of the issues you bring up are concerns:

-SQL injection
-sql:column
-sql:variable
-very expensive queries can lead to Denial of Service attacks

XQuery is code, so it should be treated similarly to SELECT statements, in general it is difficult to safely execute untrusted queries.

Ways around this would be to:

-Use set of parameterized stored procedures that reasonably cover the likely set of queries users are going to execute.
-Give each user their own database and assume that they have full SELECT permissions on it. (This wont prevent Denial of Service type attacks, but could prevent users' from access other users' data in the event of SQL injection.)
-Use full-text search. The queries wont be as expressive, but it might be good enough to filter rows.

Looking for help with a stored procedure!

Please, please help me !!!!

I have a stored procedure that I need to modify. The stored procedure
is used in an Access DB program where user selects a ProjectID and
views/or can email data for different Units.
I have to make sure that if a projectid is for Fire' Unit report goes
to EVERYONE in a select list and one more user
(v_ddcemployee.employee_id = 2234). If it's not Fire' Unit then
report goes to EVERYONE on select list excluding this specific user.
I have tried case statement and If Else but nothing works I don't
know how to do this

select ProgUnitName from v_progunit where ProgUnitID = 9 <=== will
return Fire'

Here is my Stored procedure:

CREATE PROCEDURE sp_GetFullReportCCList
@.ProjectID varchar (11) AS
SELECT v_progunit.ProgUnitName, tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@.ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@.ProjectID
GO

I will be forever grateful to everyone that helps me :)
SonyaTry this, add the employee_ID to the select, make sure you always get
the employee_id = 2234 and use a derived table.

Select DISTINCT ProgUnitName,SafetyTitleDesc,Name
from ( SELECT v_progunit.ProgUnitName,
tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@.ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@.ProjectID your union selects here )
where (progUnitName = 'Fire'
UNION
*** select as above where employee_id = 2234, in
other words - always select this employee ***
)
Where ( ProgUnitName = 'Fire') or ( ProgUnitName <> 'Fire' and
employee_id <> 2234)

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.
> Jake
>

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake
"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.

> Jake
>