How do I access the value of a stored proc return param in c# using executeNonQuery?

I've got a stored proc to insert a record and return the id of the record inserted in an output param.
How do I access this value in my code after the proc is executed?

param = comm.CreateParameter();
param.ParameterName ="@.MemberID";
param.Direction =ParameterDirection.Output;
param.DbType =DbType.Int32;
comm.Parameters.Add(param);

try
{
rowsAffected =GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
rowsAffected = -1;
}

you should simply be able to access the parameters Value property.

param.Value

How do i access the SQL server from a website.

How do I access the SQL server from a website, using my Machine Account, not the ASP.NET account?

thanks.

.intrino.On www.connectionstrings.com you can see all of the possible types of connection strings.

When you say "my machine account" do you mean a SQL Server Login, or using impersonation and your Windows account. If you want to use your Windows account, are both machines in the same Windows domain, and do you have a domain account?|||I want to use my account that is like so:

myDomain/Intrino

Instead the site connects as myDomain/ASPNET to the SQL server...and the connection string that i have set up is the SSPI and my IIS is setup as Windows Authentication and it setup as well in the web.config to use the Windows login.

I would like to connect as myDomain/Intrino...how do I transfer the login into the connection string?|||Anyone have any information on this?...|||You need to have ASP.NET run under a different account (your account).

In the Web.Config:

<identity impersonate="true" userName="domain\Jane" password="pass"/>|||So no way to the Web.Config dynammically to impersonate the user logged on?

Thanks for the answer.

.intrino.sql

How do I access the lowest level data?

OK, this feels like a dumb question... but how do I access the

lowest-level data in the cube? I.E., before it is aggregated?

What I'm trying to do is find all of the rows where an amount is >

1000. However, when I create a calculated member or constrain in

any way, it compares to the aggregated amount. I have tried a few

ways, but they all seem to act only on the aggregated data.

Thoughts?

BTW, the SQL would be:

Select sum(measure), count(measure)

from fact_table

where measure > 1000

Thanks,

Doug

Just to understand your issue:

For example, you have sales at a daily level, some of which are <1000 some over 1000. When you roll these up to the week, month, etc , they aggregate as expected.For a given higher-level time period, do you want to sum the sales of all the days with sales<1000, and also count the number of these days? (eg in a given week where only two days are <1000 the sum might return 750 and the count 2)

It's not actually trivial:
The way I would do it would be to use DESCENDANTS to return a set of the lowest level members that are underneath your current member, then FILTER that set to pick out those where [measures].[sales] <1000 then finally SUM the values from that set.

This is a bit cumbersome to write, but will work. Check out BOL for the syntax, (or I'll post a code sample when I can check BOL to make sure I get the syntax right!)

Anyone knows a more efficient way of doing this I would appreciate it.

Richard R.

|||Thanks Richard. Your suggestion was definitely helpful, but

ultimately it ended up being way too slow because I need to span

hundreds of thousands of records, and to do that it essentially meant

creating the cartesian that described them! Anyway, what I've

done for now is to create another dimension that has ranges what the

sum of the measures are. I.E. sales range (0-49, 50-99, 100-499,

etc.). and amm using those to constrain. I guess this makes

sense, since it is a constraint, not a measure, but it is definitely

frustrating to have to do it this way. The major drawback is that

I have to now create a dimension attribute for every type of sale, and

combination of sales types, to be able to properly analyze the

data.

If anyone finds an easier way to do this, please let me know!

Thanks,

Doug|||

Hi again Doug,

I don't know if this is will be useful or not in real life, but I've found a feature in AS2005 which automatically clusters continuous variables into groups for use as dimensions - which is pretty much exactly what you want.

Create a new dimension using your fact table as the source, and your sales value as the dimension attribute. On the properties box of the attribute is a wonderfully-named property "DiscretizationBucketCount", and underneath is is "DiscretizationMethod".

The first determines the number of buckets the values will be separated into (eg 7 groups), the second the method (Automatic, Even-spaced, Clustered)

I tried it on a 14 million fact table row, asking it to cluster the value spend attribute into 7 buckets automatically, and the dimension processed in a few minutes (Fast P4 laptop 1GB RAM). The resulting clusters were not that elegant, as they had the decimal points on the range names (eg 0-15.79, 15.89 - 99.34), but they are good enough for the evaluation cubes I'm delivering to the customer tomorrow :-D

I'll dig a bit more and see if the names or clusters can be frigged manually. It's all XML behind the scenes, so I'm sure it can.

Anyone out there want to REALLY show off their expertise?

Regards,

Richard

How do I access sqldatasource data values in code behind?

How do I accesssqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?

I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control

Many thanks,

Here is one way. It simply creates a table and adds the rows and columns from the data source

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protectedvoid Page_Load(object sender,EventArgs e)

{

Table t =newTable();

PanelData.Controls.Add(t);

System.Data.DataView dv = (System.Data.DataView)ds.Select(newDataSourceSelectArguments());

for (int rowIndex = 0; rowIndex < dv.Count; rowIndex++)

{

TableRow tr =newTableRow();

t.Rows.Add(tr);

System.Data.DataRow dr = dv[rowIndex].Row;

for (int colIndex = 0; colIndex < dr.Table.Columns.Count; colIndex++)

{

TableCell tc =newTableCell();

tr.Cells.Add(tc);

tc.Text = dr[colIndex].ToString();

}

}

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<asp:PanelID="PanelData"runat="server"/>

<asp:SqlDataSourceID="ds"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT ProductID, ProductName FROM Products"></asp:SqlDataSource>

</form>

</body>

</html>

How do I access sql system stored procs from code

Hello:

Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!

ThanksOkay i am stupid again and did not do a google search. So after do this I found an excellent resource for this particular problem. I found it at:

http://www.ftponline.com/vsm/2003_01/magazine/columns/databasedesign/default.aspx

Shows how to access Table Metadata programmatically using C# and VB.Net AWESOME! Working on my own code generator and now I am finally getting to a point where I can start to finish it. All I needed was a way to generate stored procs from table metadata. Now I have the table metadata and a way to generate stored procs I am all set. AWESOME.

Thanks for those that read this post hope it helps you out in the future. Remeber a real programmer does not write code but writes code that generates code for him/her.|||Just the same as any other stored procedure...you need permissions to run the proc and the path to the proc, e.g master

HOW DO I ACCESS SQL SERVER TO USE NORTHWIND DB

I am attempting to access the SQL server in order to use the Northwind database.

Typing in via the command prompt the following:

osql –E -1 “EXEC sp_attach_db N’Northwind’,N’c:\SQL Server 2000 Sample Databases\northwind.mdf’”

The command prompt response:

[SQL Native Client] "An error has occurred while establishing a connection to the server when connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connects."

Also, I loaded SQL Server Management Studio Express and not sure if I even need this. I click on the Icon but I’m not sure what I need to do to establish a logon and connect.

Any help will be appreciated.

Thanks!!

configure sql server express to

accept remote connection by

runing the sqlserver surface are configuration

Start>programs>sqlserver2005>configuration tools>surface area configuration

>configuration for service and connection>remote connection>local and REMOTE connection

|||

Make sure that you follow all of the instructions in the following KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277.

How do I access MSDE?

Hi,
I don't have Visual Studio, so I'm using web matrix and MSDE. My question is, how do I get to MSDE? I don't have the enterprise manager that comes with SQL Server. Thanks
amitDownload the SQL Server Web Data Administrator
Easily manage your SQL Server and MSDE data, wherever you are. Use this tool to create and edit databases, manage users and roles, export and import database schema and data, and more—all from your favorite Web browser. Available from the Microsoft Download Center

http://www.microsoft.com/sql

I have not tried it yet, but it might be what you need.

KM|||Check outthis link which has a free Query Analyzer tool. Also here is aweb admin tool from microsoft. HTH|||That's just what I need, thanks to both!sql

how do I access an error cause inside an exec statement

Hello:
how do I access an error code when
SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
fails because the database..table does not exists ?
@.RESULTS comes back with nothing but
I get
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'Internet_Forms.test'. Check
sysobjects.
(Problem since @.NAME is populated from sysobjects.....)
Thanks
TThe batch is aborted after encountering this specific error, so you cannot
followup and catch the error. One thing you could do is check if the table
exists like so:
if object_id('mytable') is not null
begin
<o.k. do something>
end
else
begin
<table does not exist>
end
The following is a good tutorial on the subject:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23VCo9XZoFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>|||begin transaction
run querry
queryy @.@.error
commit when no error
rollback if erro
"Support" wrote:

> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>
>|||@.@.error returns noting becuase of JT's reason...
That's the problem
Thanks
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:B6A8654B-67F3-47F0-A93E-24D7E77F02F9@.microsoft.com...
> begin transaction
> run querry
> queryy @.@.error
> commit when no error
> rollback if erro
>
>
> "Support" wrote:
>

How do I access a SQL view in VB.Net?

I'm able to access my stored procedures just fine, but I can't query/show a view that I'd built in SQL Server. Any suggestions on how would be welcome.

Sample Stored Procedure reference, and I just want to reference a view now:

 ' Connect to the Database
Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("myConnectionString"))
' Establish the set of data commands and a database connection that are used to fill the dataset
Dim MyCommand As New SqlCommand("vw_Mast_PW_Accounts", MyConn)
MyCommand.CommandType = CommandType.StoredProcedure
MyConn.Open()
dgMyGrid.DataSource = MyCommand.ExecuteReader
dgMyGrid.DataBind()
MyConn.Close()

I might be mistaken here, but a view is treated pretty much like a table. So if you want to return data from a view, query against it as if it were a table: SELECT * FROM vw_myView WHERE ...

You'll need to make sure your web app has permissions to query the view and, I think, any underlying tables (though I might be wrong on that last point).|||That's great, but do is there a way to do it without using SQL but simply referencing it as I would a stored procedure (i.e., just the name)?

Thank you,
Brent|||I don't believe so, for that same reason that you couldn't do that with a table.

Youcould create a stored procedure that returned data from your view, if you really needed that functionality...|||I gave up and built the stored procedure to access that view. Thank you for the suggestion!

Brent

How do I access a mdf-file directly?

Hello!
I want to access a sql server file without having sql server installed on my laptop. How can I do this? Can you give me an example please because I'm new with it.
Thx a lot!
TomI don't think this can be done. The closes you can get is to do a minimial installation, use the Personal Desktop edition. I don't think there is any tool our COM object that will allow one to open the file and read throuigh it.

If you need to analyse some data or tables you could export the data to MS Access, Excel or flat files.|||You will need to install either msde (I think) or sql server then you can do a sp_attach_single_file_db to access the database.|||I dont think that you would be able to do it.
But as suggested previously, you can try minimal installation and then get the data out in the format that you want.

Thanks|||hii
i think there is a microsoft desktop engine which minimally installs the components required by SQL. Try that that. i dont have info on that at the moment . will try to get u|||hii
Install MSDE which comes in the office 2000 Cd and Use SQL DMO object and attach it to the instance of MSDE. Try , it should work

:confused:

How do i "Join" back to the same table and fields ? Please help !

Hi and thanx for reading my post..

Underneat is the current SQL i'm trying to get to work (in Access 2000)

Cant get it to work, cause i'm getting Join errors..

----CODE-----
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----END CODE----

----DESCRIPTION-------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.

----END DESCRIPTION------

Result should look like this :

Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
------------------
1 Doe 2 John

Will be enormously happy if anyone could help me out with this code...

/(_Mirador:(I'm not 100% on what you want. Is this your table structure?

Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry

PersonPerson
PID1 PID2
-- --
1 2
4 3

If so - this should help:

strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
FROM Person p, Person p1, PersonPerson pp
WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"

It will generate the following output:

PID Lastname PID Lastname
---- ---- ---- ----
1 Dixon 2 Crawford
4 Potter 3 Shark

(Yes, Person 4's name is Harry Potter - but no - I haven't read the books :) )|||Great ! :)

I will try the code right away..

I'll send you the results i got :)

Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)

It worked like a dream..

You have no clue how much you've helped me !

Owe u a digital-beer.

Mirador.|||lol - no worries mate.

But I've gotta drive tonight - so how about a digital soda?|||/Me hands over a digital master-soda :)

hehe..

btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?

----CODE-----
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---- END CODE -----

---- DESCRIPTION-------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...

If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..

I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..

Got any clues on this one ?

Thanx for your help mate..

Mirador.|||So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost :) ) - then this should help (take note of the brackets):

strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...

Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ;) ):

If txtSearch1 is "big" and txtSearch2 is "round"

SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')

So you will get products with big AND round in either the Name, Description or Comment column.

Is this what you were after - or have I just spoken pure gibberish?|||Hay mate :) thanx for your reply :)

did u enjoy your digital soda ? hehe.. :)

Well... gotta admit that it's a bit confusion for myself too :)

I'll try to explain a bit better :

---------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.

A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
----------

If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."

I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.

Example :
-------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
-------

Yea.. that's about it :) dunno if it's even possible but i surely hope so : )heeh..

Hope that made things a littlebit more clear :)

Thanx for your help btw !!!.. most appreciated..

want another digital soda ? or.. maybe a digital beer this time :)

Best regards
Mirador.|||Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.

For each search criteria you have to check if it's in any of the columns. Again - using my Product table:

SELECT blah
FROM Product
WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)

Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.

Is that what you're after?

And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :D|||Tjohooo !!..

yea.. that's JUST what i was after..

I had thoughts in this track, but wasn't certain because it would be so extremely long:) hehe.. Didn't know quite how to write it either..

but.. THANK YOU AGAIN !! :)

Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:

Mirador..|||And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :DThese last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?

-PatP

How define users of another domain.

Hi everybody, i should have a little problem.
I must grant the access (using odbc) to users of domain "A" to a sql server
2000 instance running in another domain (domain "B").
Well, if the users read the database like user "sa" (defined in the odbc
connection) there aren't problems but if in the odbc connection is specified
the "trust connection" flag nobody can read data.
In sql server the users are defined both username and domain\username and
permissions are defined correctly.
What could i do?
Thanks in avance for your answers.
RobyHi
If your SQL Server is in Domain A, you need to setup a trust relationship
with Domain B at Active Directory level.
Then, when use "B\user1" presents it's credentials to the SQL Server, SQL
Server passes the request to the OS, which in turn passes it to Domain A's
domain controller, who in turn passes the request to Domain B's domain
controller.
The trusting is done at OS level. If you can not setup a trust, then you
need to keep using SQL Server Security.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"robyemme" <marautor@.tiscali.it> wrote in message
news:F1657283-F17A-40FC-9061-C0BFBA7DC569@.microsoft.com...
> Hi everybody, i should have a little problem.
> I must grant the access (using odbc) to users of domain "A" to a sql
> server
> 2000 instance running in another domain (domain "B").
> Well, if the users read the database like user "sa" (defined in the odbc
> connection) there aren't problems but if in the odbc connection is
> specified
> the "trust connection" flag nobody can read data.
> In sql server the users are defined both username and domain\username and
> permissions are defined correctly.
> What could i do?
> Thanks in avance for your answers.
> Roby
>

How come my database do not have a record id?

this is my first time used M.Access, so i not very sure is it Access do not have a record id that it will auto generate itself one when i insert a record? if i cannot find.. what is the way that i can enable it?How do i extract the record id of my access records in java?

How check user name?

I have COM object working with database via connection with administrative
rights.
I need method for checking user name: can this user has db access or no.
I want to check:
1. SQL Server users
2. Windows users which added as a SQL Server logins/db users
3. Windows users which can connect to db as a members of windows groups
added to SQL Server.
Thnx.have you looked into using SQL-DMO. The object model has collections and
methods to do all you ask.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Oleg Cherkasenko" wrote:

> I have COM object working with database via connection with administrative
> rights.
> I need method for checking user name: can this user has db access or no.
> I want to check:
> 1. SQL Server users
> 2. Windows users which added as a SQL Server logins/db users
> 3. Windows users which can connect to db as a members of windows groups
> added to SQL Server.
> Thnx.
>
>sql

How can you prevent the data changes message during editing a row

I just upsized my Access application (mail-order processing system) and
linked to SQL server 2000 via ODBC tables links from an Access 2000 front
end.
By doing so the standard optimistic locking of SQL server 2000 comes into
force and the option "lock edited record" on the Access Client gets ignored.
I cannot work with optimistic locking because if e.g. an order gets entered
in a table and e.g. a stock allocation job runs in the background that
updates the order header,
all the entered data will be lost (or must be copied to the clipboard and
edited ...).
Do you know how to overcome this problem - problems like this should
actually be quite common ...
Several years ago I used to work with a database called DataFlex.
This database had the best locking mechanism I every have experienced.
If one enters dat in a form the pulled record does not get locked (like
optimistic locking).
It only gets locked shortly before the update - it workes like this:
read a record from the table and display in the form
user modifies data in form
user clicks update button
record gets locked and reread into the record buffer
client program compares every form field with the record buffer if any data
was changed
if a field was changed the record buffer was overwritten with the data the
user entered
the record gets saved and unlocked
This way only the changed data fields get updated and changes of other users
do not get overwritten!
Is there a way on SQL server to do something similar or how do you deal with
this problem?
I also thought of writting an INSTEAD OF UPDATE trigger and check if any
column is different from the Inserted to the "REREAD" record buffer and appl
y
the above mentioned logic of DataFlex but this streches my SQL knowledge jus
t
a bit too far ...
May be you can help?
Any comments are appreciated!
Thanks in advance.
OliverBracket your user actions between BEGIN TRANS...COMMIT TRANS and to an
UPDATE WITH(ROWLOCK) setting a dateModified field to getDate()
immediately after BEGIN. All subsequent modifications will be queued
until you fire your update and commit. You could also do a check on the
dateModified field and simply warn the user if it has incremented since
they read the record, but doing a field-by-field comparison is more
work than is necessary.
The warning is almost always the preferable solution, since it is
dangerous to assume that such a collision should proceed in whichever
more or less arbitrary order when human intervention is not only
possible, but desirable.|||Oliver wrote:
> I just upsized my Access application (mail-order processing system)
> and linked to SQL server 2000 via ODBC tables links from an Access
> 2000 front end.
> By doing so the standard optimistic locking of SQL server 2000 comes
> into force and the option "lock edited record" on the Access Client
> gets ignored. I cannot work with optimistic locking because if e.g.
> an order gets entered in a table and e.g. a stock allocation job runs
> in the background that updates the order header,
> all the entered data will be lost (or must be copied to the clipboard
> and edited ...).
> Do you know how to overcome this problem - problems like this should
> actually be quite common ...
> Several years ago I used to work with a database called DataFlex.
> This database had the best locking mechanism I every have experienced.
> If one enters dat in a form the pulled record does not get locked
> (like optimistic locking).
> It only gets locked shortly before the update - it workes like this:
> read a record from the table and display in the form
> user modifies data in form
> user clicks update button
> record gets locked and reread into the record buffer
> client program compares every form field with the record buffer if
> any data was changed
> if a field was changed the record buffer was overwritten with the
> data the user entered
> the record gets saved and unlocked
> This way only the changed data fields get updated and changes of
> other users do not get overwritten!
> Is there a way on SQL server to do something similar or how do you
> deal with this problem?
> I also thought of writting an INSTEAD OF UPDATE trigger and check if
> any column is different from the Inserted to the "REREAD" record
> buffer and apply the above mentioned logic of DataFlex but this
> streches my SQL knowledge just a bit too far ...
> May be you can help?
> Any comments are appreciated!
> Thanks in advance.
> Oliver
Yes. This is easy. Add a TIMESTAMP column to each table where you need
this support. A timestamp column is not a date, but a column that SQL
Server automatically changes each time a row is updated. When you
initially query the row data, select the TIMESTAMP as well. When you
save the data from your stored procedure (ideally, you 'll be using
stored procedures), compare the TIMESTAMP you selected with the
timestamp currently in the row. If they are different, then you know the
data was changed in the interim and can raise an error and have the
client application automatically re-query the data. Your update
statement can look something like this:
Update dbo.MyTable
Set
Col1 = @.Col1,
Col2 = @.Col2
Where
ColPK = @.ColPK
and
timestamp = @.timestamp
If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
RAISERROR ...
Else
-- Everything is good to go
David Gugick - SQL Server MVP
Quest Software|||Hi David,
Thanks for your tips but this sounds like a lot of programming to overcome
a problem that actually is a server's job.
Raising an error message when data changed in the background can only be
useful if there is a user at the other end.
What happens if a program gets caught out by a user e.g. the user changes da
ta
during the time the program read the row to update one column?
You will have to program some code to get arround the problem for every
transaction you are trying to do in a job like stock allocation, release
orders for delivery... - that would be too much work for me as my applicatio
n
is big!
I think it would be much better if the server could check in an update
trigger if there was a concurrent update of columns and just updates the
column(s) that were changed by the current transaction. This way all changes
other transactions did will be kept and nobody has to decide which
data/changes to keep.
I am quite new to SQL server programming and do not know all the ins and
outs of trigger transaction programming.
Could you or somebody else suggest some code how to achieve this?
Thanking you in advance.
Oliver
"David Gugick" wrote:

> Oliver wrote:
> Yes. This is easy. Add a TIMESTAMP column to each table where you need
> this support. A timestamp column is not a date, but a column that SQL
> Server automatically changes each time a row is updated. When you
> initially query the row data, select the TIMESTAMP as well. When you
> save the data from your stored procedure (ideally, you 'll be using
> stored procedures), compare the TIMESTAMP you selected with the
> timestamp currently in the row. If they are different, then you know the
> data was changed in the interim and can raise an error and have the
> client application automatically re-query the data. Your update
> statement can look something like this:
> Update dbo.MyTable
> Set
> Col1 = @.Col1,
> Col2 = @.Col2
> Where
> ColPK = @.ColPK
> and
> timestamp = @.timestamp
> If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
> RAISERROR ...
> Else
> -- Everything is good to go
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Oliver wrote:
> Hi David,
> Thanks for your tips but this sounds like a lot of programming to
> overcome
> a problem that actually is a server's job.
> Raising an error message when data changed in the background can only
> be useful if there is a user at the other end.
> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
> You will have to program some code to get arround the problem for
> every transaction you are trying to do in a job like stock
> allocation, release orders for delivery... - that would be too much
> work for me as my application is big!
> I think it would be much better if the server could check in an update
> trigger if there was a concurrent update of columns and just updates
> the column(s) that were changed by the current transaction. This way
> all changes other transactions did will be kept and nobody has to
> decide which data/changes to keep.
> I am quite new to SQL server programming and do not know all the ins
> and
> outs of trigger transaction programming.
> Could you or somebody else suggest some code how to achieve this?
> Thanking you in advance.
> Oliver
I don't agree with your assessment. This is an application programming
issue and not one for the database to manage on its own. You're
suggesting that the server update a row that has been updated in the
interim by another process just because the columns being updated are
not the same. I would argue that there's no way for SQL Server to know
if changing a single column value in a row would somehow affect business
rules and know whether or not the proposed row changes are valid.
Many applications can deal with this scenario by assuming the last
update should be the most current. In that scenario, there is no
additional programming required. If you need to manage concurrency and
changes to a row by another session to avoid overwriting those changes,
you should use a timestamp. You pass the timestamp value with the other
column values to your stored procedure and the procedure does the work.
The application should be able to handle the condition where the row was
not updated because an error was raised and then requery the data and
inform the end-user. All your DML should be done in stored procedures.
In the case where there is no end-user, your application code should log
an error condition that can be managed manually in an interactive
fashion or it could requery the data, assuming this is something it can
work around.
For a nightly batch process, you could lock the entire table within your
transaction.

> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
I'm not sure what you are describing here. A change is a change.
Presumably, you have a stored procedure to manage each type of change to
your data. Maybe you can elaborate on this part a little more.
David Gugick - SQL Server MVP
Quest Software

How can we avoid somebody to access the MDF data by doing User Instance connection?

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

Hi,

AFAIK their is no way to restrict access to MDF/LDF files physically... refer below thread which has discussion the same issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

Hemantgiri S. Goswami

|||

Alright, but what about the User Instance? Is there a way to prevent somebody of getting connected on a server using our MDF file and the User Instance option into his connection string?

|||No, you wil lhave to do this via NTFS permissions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can I use SQL with Access

How can I use SQL as a server for my Access Database app?
Chumpie999You could LINK SQL tables into Access then use them as regular Access tables. You can use ADO to connect to SQL and write a frontend application.

How can i use embbed sql(e.g. SQC) program to access MSSQL from linux or unix?

hello all!
Help!
I've a lot of programs written in sqlc to mssql on the windows platform.Now the boss wants to use linux server and I've to tune all my programs in sqlc to linux.But how to do it?Does anyone help me?Don't have any experience in linux, thought this link (http://www.linux.org/apps/AppId_8044.html) may give you required information.

HTH|||Thanks, but this link was on MySQL, not MSSQL. I also have some information on this through using freeTDS or DBI, but on embedded sql, I found nothing!:(|||Then may be you need to do other way round, how about this link? (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=921&lngWId=8)

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?

Thanks for answer again!

|||

This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.

|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.

In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?

Thanks for answer again!

|||

This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.

|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.

In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!