Can we include User Defined Functions in the Report Model

I have certain user defined functions in my database.

Can we include User Defined Functions in the Report Model.

Thank you,

Hi-

You can create custom expressions within a model using any combination of the supported semantic query functions in Model Designer. For example, you could create Employee.BirthWeek in a model by using the semantic query function Week(Employee.Birthday). The Employee.BirthWeek could then be consumed by reports through Report Builder.

However, you cannot create custom functions in code and reference them in a model. The underlying query translator would not be able to translate arbitrary code into T-SQL.

Hope that helps.

Thanks, Jon

Can we implement Pivot Tables using MSRS?

Can we implement Pivot Tables using Reporting Services 2005? I need to
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>

Can we hide some parameters

There will be a pararmeters panels when we browse reports.
The user can modify the parameters there.
But there are some parameters I do not want the usrs to modify.
How can I hide these parameters of restrict user to modify?In the report manager go to the report properties and then to the Parameters
section. Specify a default value for the parameter and then delete the prompt
string (DO NOT uncheck the "Prompt user" check box)
Of course, the parameter value will always be the same, no matter which user
runs the report or what he/she does.
I hope this helps.
--
Please mark the correct/helpful answers!

Can we access Active Directory Group in a report?

Is there a way that we can tell what active directory group the person belongs to that is running the report? I know that you can detect a user id, but I need to access the Active Directory Group that they belong to.

I'm not sure if there is another method, but this is the method I used for checking if a user belongs to a particular group. It can be altered to return the group:

Added to the code window.

Code Snippet

Function IsMemberOfGroup(ByVal UserName As String, ByVal GroupName As String) As Boolean
Try
Dim ent As System.DirectoryServices.DirectoryEntry = New System.DirectoryServices.DirectoryEntry("LDAP://DC=domainname,DC=com")

Dim srch As System.DirectoryServices.DirectorySearcher = New System.DirectoryServices.DirectorySearcher("(CN=" + GroupName + ")")
Dim coll As System.DirectoryServices.SearchResultCollection = srch.FindAll()
For Each rs As System.DirectoryServices.SearchResult In coll
Dim resultPropColl As System.DirectoryServices.ResultPropertyCollection = rs.Properties
For Each memberColl As Object In resultPropColl("member")
Dim gpMemberEntry As System.DirectoryServices.DirectoryEntry = New System.DirectoryServices.DirectoryEntry("LDAP://" + memberColl.ToString)
Dim userProps As System.DirectoryServices.PropertyCollection = gpMemberEntry.Properties
Dim obVal As Object = userProps("sAMAccountName").Value
If obVal.ToString = UserName Then Return True
Next
Next
Catch ex As Exception
'Trace.Write(ex.Message)
End Try
Return False
End Function

Can we able to create a database user name as ‘sa’ in sql server?

Can we able to create a database user name as ‘sa’ in sql server 2k5?

sa is reserved for the sysadmin account.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

No, database principals cannopt have the names of predefined server principals.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Can view accept parameters

Hi All ,
i wonder if a view can accept parameters becoz i only
want to show certain columns based on user's input.
if it's not possible, could i know is there other
alternative as to accept both the user's input as well as
restrict the column to be displayed as readonly
thks & rdgsWhat version are you running? Please always state that information.
If you are using SQL Server 2000, you can create a table valued function
that can be used EXACTLY like a view which accepts parameters. Please read
about CREATE FUNCTION in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
> Hi All ,
> i wonder if a view can accept parameters becoz i only
> want to show certain columns based on user's input.
> if it's not possible, could i know is there other
> alternative as to accept both the user's input as well as
> restrict the column to be displayed as readonly
> thks & rdgs|||Hi Kalen ,
my appologies ..
btw i am using SQL 7.0 , is this possible then ?
thks
>--Original Message--
>What version are you running? Please always state that
information.
>If you are using SQL Server 2000, you can create a table
valued function
>that can be used EXACTLY like a view which accepts
parameters. Please read
>about CREATE FUNCTION in the Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
as[vbcol=seagreen]
>
>.
>|||No. In the relational model, a view is just like a table and a table doesn't
accept parameters.
In-line table values functions is what some call "a view that accepts parame
ters" but that feature was added
in 2000. Can you use a stored procedure instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:042d01c47ab0$eb6bcd80$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Kalen ,
> my appologies ..
> btw i am using SQL 7.0 , is this possible then ?
> thks
> information.
> valued function
> parameters. Please read
> message
> assql

Can view accept parameters

Hi All ,
i wonder if a view can accept parameters becoz i only
want to show certain columns based on user's input.
if it's not possible, could i know is there other
alternative as to accept both the user's input as well as
restrict the column to be displayed as readonly
thks & rdgsWhat version are you running? Please always state that information.
If you are using SQL Server 2000, you can create a table valued function
that can be used EXACTLY like a view which accepts parameters. Please read
about CREATE FUNCTION in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
> Hi All ,
> i wonder if a view can accept parameters becoz i only
> want to show certain columns based on user's input.
> if it's not possible, could i know is there other
> alternative as to accept both the user's input as well as
> restrict the column to be displayed as readonly
> thks & rdgs|||Hi Kalen ,
my appologies ..
btw i am using SQL 7.0 , is this possible then ?
thks
>--Original Message--
>What version are you running? Please always state that
information.
>If you are using SQL Server 2000, you can create a table
valued function
>that can be used EXACTLY like a view which accepts
parameters. Please read
>about CREATE FUNCTION in the Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
>> Hi All ,
>> i wonder if a view can accept parameters becoz i only
>> want to show certain columns based on user's input.
>> if it's not possible, could i know is there other
>> alternative as to accept both the user's input as well
as
>> restrict the column to be displayed as readonly
>> thks & rdgs
>
>.
>|||No. In the relational model, a view is just like a table and a table doesn't accept parameters.
In-line table values functions is what some call "a view that accepts parameters" but that feature was added
in 2000. Can you use a stored procedure instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:042d01c47ab0$eb6bcd80$a301280a@.phx.gbl...
> Hi Kalen ,
> my appologies ..
> btw i am using SQL 7.0 , is this possible then ?
> thks
> >--Original Message--
> >What version are you running? Please always state that
> information.
> >
> >If you are using SQL Server 2000, you can create a table
> valued function
> >that can be used EXACTLY like a view which accepts
> parameters. Please read
> >about CREATE FUNCTION in the Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:056c01c47aae$ea2814d0$a501280a@.phx.gbl...
> >> Hi All ,
> >>
> >> i wonder if a view can accept parameters becoz i only
> >> want to show certain columns based on user's input.
> >>
> >> if it's not possible, could i know is there other
> >> alternative as to accept both the user's input as well
> as
> >> restrict the column to be displayed as readonly
> >>
> >> thks & rdgs
> >
> >
> >.
> >

Can user view objects they only have select permission on?

I have a user that belongs to a role. This role only has select permissions
on 10 views. When I log in as this user via Management Studio I cannot see
the views however I can execute queries against them.
On another server that I did not setup, that I'm supposed to be mimicking
the same security, this same user can see the views.
Any ideas what the difference is?
Thanks!Here's the commands that I'm executing in order:
CREATE ROLE [Customers_ROLE] Authorization dbo
CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] Deny
View
Definition to [Customers_Role]
exec sp_adduser 'phenson', 'phenson', [Customers_Role]
GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
When I log in as phenson I do not see PT_View but I can query on it. I need
to be able to see it.
"SpankyATL" wrote:

> I have a user that belongs to a role. This role only has select permissio
ns
> on 10 views. When I log in as this user via Management Studio I cannot se
e
> the views however I can execute queries against them.
> On another server that I did not setup, that I'm supposed to be mimicking
> the same security, this same user can see the views.
> Any ideas what the difference is?
> Thanks!|||I thought I'd answer my own question for those of you who come across this
some day. I need to remove the "Deny View Definition" portion and that took
care of it. The user was able to see that view and execute it but could not
see the script.
"SpankyATL" wrote:
[vbcol=seagreen]
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I ne
ed
> to be able to see it.
> "SpankyATL" wrote:
>|||SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I
> need to be able to see it.
Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
member of?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was working from a script that was given to me. The person who developed
it mistakenly thought deny view would only deny the user from viewing the
source code.
"Erland Sommarskog" wrote:

> SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
> member of?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

CAN USER EDIT DATA in the report?

Hi,
I have a report which shows all the orders in the systam . I want to be able
to view the order details for an order when an order number is clicked and be
able to edit some information so thagt it goes back to the databse. Is it
possible to edit data with REpotrting svcs?
If not, then is there a way, i can show all the orders in the databse, and
have a link in the report which takes the user to a web page where the user
can edit the order and then after editing the order, he again can view the
orginal (all orders ) in the system from the report and then click a link
which takes the user to the order details of that order so that the order can
be edited?
--
pmudNo, it is not possible to edit data in report in place.
Yes, it is possible to make a URL link to the edit page for every order.
You just need to setup an action for the textbox with order id that will be
an http link with order id parameter. Action is visible in properties for
the textbox.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:7CC6E584-B705-45A9-B83A-9821109B592A@.microsoft.com...
> Hi,
> I have a report which shows all the orders in the systam . I want to be
able
> to view the order details for an order when an order number is clicked and
be
> able to edit some information so thagt it goes back to the databse. Is it
> possible to edit data with REpotrting svcs?
> If not, then is there a way, i can show all the orders in the databse,
and
> have a link in the report which takes the user to a web page where the
user
> can edit the order and then after editing the order, he again can view the
> orginal (all orders ) in the system from the report and then click a link
> which takes the user to the order details of that order so that the order
can
> be edited?
> --
> pmudsql

Can this be done with an output parameter?

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks::How could I create an output parameter that would return their new user id and then how
::would i set it to an integer variable

do you mean from the front end or inside the stored proc ?

if you mean the stored proc:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...) select @.userid=@.@.IDENTITY
..

HTH|||Use the OUTPUT Parameter of the Stored Procedure. Refer to SQL Server BOL for further assitance ...|||Hi yeah I mean by using the output param in the sp.

How does the @.@.Idendity work then?

How would i set this to a varialble in my front end. Would I use

Dim UserID as Integer

Dim objParam as New SqlParameter("@.@.Idendity", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objParam.Value = UserID
objComm.Parameters.Add(objParam)

Thanks|||First, you should use SCOPE_IDENTITY(), not @.@.IDENTITY.

Next, use a SP like this:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...)
select @.userid=SCOPE_IDENTITY()

then

Dim objParam as New SqlParameter("@.UserID", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objComm.Parameters.Add(objParam)

Then after the command is run, check objComm.Parameters("@.UserID") for the UserID|||Many thanks Douglas. We may have to start calling you superman on here lol as you come to everyones rescue.|||Thanks. I do have the glasses, though no cape, and no one wants to se me in tights<g>.

Can the transaction log be turned off?

I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?

ThanksNo

Why what the problem?

Waht's your recovery model set to?|||Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.|||Originally posted by grahamt
Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.

When you say timeouts, what do you mean by that?

From QA, from the application layer?

Are you blocking yourself (sp_lock)

How many connections are open (sp_who2)

How big is the database?

How often do you take backups?|||Setting recovery model is nothing to do with blocks or timeouts on the database. Ensure there are no issues with network, client connections and query conditions involved which may help causing timeouts.

Whatever the recovery model ensure to maintain & schedule full backup of the database.|||The SQL Server timeouts are reported by the ASP.
A bit of config info.

Hardware

SQL Server 2000
Dual Xeon 2.4GHz CPU
Ultra 320 SCSI controller
Two Ultra 320 18GB, 15000 RPM Hard Disks (C: and D:)
Two GB RAM (1.5GB assigned to SQL Server)
Windows 2000 Server

WEB Server connected to SQL server by Gigabit Ethernet.

Two databases involved. One has a single table of perhaps 2000 records (Session state info for WEB users) and is heavily used (Insert, Delete, and Select). It resides on drive C:.

Second DB residing on drive D: has one Master table and 26 others (A-Z). Master table has about 16000 6K records, Primary key on a varchar(50) and a char(2) column. Other tables run from 1000 to 50000 records 70 bytes/record (PK on a varchar(50) field). No updates are ever done (well, every few months maybe) and the Selects are done using stored procedures, one for the Master table (2 parameters) and 26 for the other tables (1 parameter).

Every few minutes (time varies as does the DB with the DB on drive D: getting the most) the WEB app logs an SQL Server timeout. With this horsepower driving these small DBs (and nothing else running) I wouldn't expect any timeouts at all.

Since I don't care about the data in one table and the data in the other never changes, I don't bother with backups.

Any thoughts?|||What are the timeout settings on SQL & ASP script?
IT may be worth if you enable DBCC DBREINDEX and other maint.plan checks on database which will addup performance.

And also consider network settings and take help of netadmin.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm - a useful guide to troubleshoot timeouts when using SQL & ASP.|||I've been monitoring network traffic with 3COMs Network Monitor and everything seems fine. The WEB server showed high FTP traffic so I've shut that service down but there's no perceptable load on SQL server. Performance monitor shows Disk Idle Time averaging 98% or better on both drives and the CPU load never exceeds 3-4%. I've run the SPs through QA and because they are so basic the execution cost is almost nil. The two SPs are

CREATE PROCEDURE [DBO].[prGetOrigins_A]
@.SomeVar Varchar(50)
AS

SET NOCOUNT ON

SELECT Code, Master FROM A Where SomeVar = @.Somevar
GO

and

CREATE PROCEDURE [DBO].[prGetMasterRec]
@.master varChar(20),
@.code varCHar(3)
AS
SET NOCOUNT ON

SELECT id, code,SomeData,
FROM Masters WHERE Master = @.master AND Code = @.Code
GO

and still I get random timeouts.

Personally I am not concerned. A timeout every 5 minutes or so with 250 users on the WEB server probably shouldn't happen but I can live with it. It's the boss who panics and worries that he may have lost a $5.00 sale. 8-)

can the foreign key be from different database?

Hi,
I have 2 database, one with all static config data and another one is the us
er data. There is a table in the user database one column have to refer the
column in static database, can I add a foreign key to it that refers a prima
ry key in static database?
how? ThanksYou can use a trigger to emulate a FK between tables in different databases
or servers even.
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:92C2B56D-747A-4960-8C84-AA8B6C106E27@.microsoft.com...

quote:

---

> Hi,
> I have 2 database, one with all static config data and another one is the

---

user data. There is a table in the user database one column have to refer
the column in static database, can I add a foreign key to it that refers a
primary key in static database? how? Thanks

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.

Can SQL Standard Edition with User CALs be used as the backend to a website?

I just need a straight answer. Is it allowable to use SQL Standard Edition
as the database for a website if you are using user or device CALs instead o
f per processor licensing? The plan is for a company website running IIS on
d SQL standard edition on t
he same server. The only connection to the SQL server would be the user acc
ount that accesses the databases on the ASP.NET pages. There would be no ex
ternal direct connection to the SQL server.
Every search I have done has said I should use per processor licensing for t
he backend SQL server, but it does not say that I have to. Any help would b
e appreciated. Please feel free to email me.
Thanks!Exposing data through a web server to the public when in Per Seat mode is a
violation of the Licensing agreement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"yourpalqwerty" <yourpalqwerty@.yahoo.com> wrote in message
news:E97EDFA1-7D00-43F9-A1FF-070105B323D9@.microsoft.com...
> I just need a straight answer. Is it allowable to use SQL Standard
Edition as the database for a website if you are using user or device CALs
instead of per processor licensing? The plan is for a company website
running IIS ond SQL standard edition on the same server. The only
connection to the SQL server would be the user account that accesses the
databases on the ASP.NET pages. There would be no external direct
connection to the SQL server.
> Every search I have done has said I should use per processor licensing for
the backend SQL server, but it does not say that I have to. Any help would
be appreciated. Please feel free to email me.
> Thanks!|||Thanks for the quick response. I'm glad MSDE exists. I guess I'll stick wi
th that for people with a limited budget.

can sql server 2005 send messages to asp.net2.0

hello

can sql server 2005 send messages to asp.net2.0 and to inform that a user hasn't (for example) the right to read a query

how?

thanks

You need to write code to do that.

Can SQL server 2000 Enterprise Edition run in Windows XP?

Please help me here...I am a new user of sql server 2000 and a beginner in asp...We are making a web application and I have some problems with sql server 2000 in my windows XP.

1. I'm not connected in a network.
2. When I try to connect to sql server 2000 using this code:
<%
Dim CN
Dim RS
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open "Provider=SQLOLEDB.1;uid=sa;Initial Catalog=Planetchow;"
Set RS = CN.Execute ("SELECT Restaurant_Id FROM RestaurantInfo")

While Not RS.EOF
Response.Write "<TR>"
Response.Write "<TD>" & RS.Fields("Restaurant_Id") & "</TD>"
Response.Write"</TR>"
RS.MoveNext
Wend
%>

I get this error message:

"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

3. How come? What configurations do I have to make to be able to avoid this...

4. How do I connect to SQL server 2000 in ASP vbscript code?

Please I really need some help here...thank you!Go to enterprise manager and right click on the server and select properties then go to the tab that says security and make sure "SQL Server and Windows" is checked.

can SQL Profiler ('05) look into User Defined Functions (UDFs)?

particularly table valued UDFs that may cause other udfs, etc.

what settings/events do I need to see inside and get each statement or at least each seperate UDF that is executed internally?

Try SP:StmtStarting, SP:StmtCompleted events. They are under "Stored Procedures" event category.

Can SQL Lose Records?

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.

Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).

I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.

So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.

Thanks!

NeilOn Jun 20, 2:53 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.
>
Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).
>
I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.
>
So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.
>
Thanks!
>
Neil

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.|||I've never seen or heard of a row going missing either, and I spent
plenty of time using 7.0.

Along with what Alex suggested I would suggest doing a complete set of
DBCC integrity checks on the database.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 19:53:32 GMT, "Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>We are running SQL 7 with a front end that links to the tables through ODBC.
>In our main table, the user has no way to delete a record through the
>interface, though it is possible to delete it by opening the ODBC link.
>Users would have no reason to delete a record, but one of our records turned
>up missing.
>
>Now, it's possible that a user may have accidentally deleted the record.
>But, since users don't have any reason to delete records, and since they
>don't access the ODBC links, it seems unlikely (though possible).
>
>I was wondering if anyone had every heard of SQL Server ever "losing" a
>record that had previously been saved. I checked the nightly backup from the
>night after it was added, and the record was there. So either a user deleted
>it, or somehow it got lost in SQL Server. I have no code that deletes
>records in this table in any way, shape or form, so it couldn't have been
>malfunctioning code.
>
>So, while I have a hard time believing that SQL Server would just "lose" a
>record, I also know that anything's possible, so I thought I'd ask if anyone
>had ever heard of such a thing.
>
>Thanks!
>
>Neil

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

So, while I have a hard time believing that SQL Server would just "lose"
a record, I also know that anything's possible, so I thought I'd ask if
anyone had ever heard of such a thing.

Well, I have lost rows, but that was a on a system where no one was looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.

As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.

But I'm prepared to place my bets that there was a human involved.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.
>

If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?

Thanks.|||I'm not familiar with DBCC. Can you point me in the right direction?

Thanks.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955ED0DF77BAYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>So, while I have a hard time believing that SQL Server would just "lose"
>a record, I also know that anything's possible, so I thought I'd ask if
>anyone had ever heard of such a thing.

>
Well, I have lost rows, but that was a on a system where no one was
looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.
>
As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.
>
But I'm prepared to place my bets that there was a human involved.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jun 20, 5:01 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.

>
If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?
>
Thanks.

You can disable the trigger for the duration of your delete.
Alternatively you can have you trigger allow you to do whatever you
want, based on user_id() or suser_id(). Trigger can be bypassed using
nested triggers and or recursive trigger setting. There are other ways
best described in T-SQL Programming by Itzik Ben-Gan.

http://sqlserver-tips.blogspot.com/|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

I'm not familiar with DBCC. Can you point me in the right direction?

There are several DBCC commands, but the one of interest here is DBCC
CHECKDB which checks the database for consistency errors. If the database is
of any size, run it off-hours.

You should regularly run DBCC on your database, for instance as part of a
maintenance job, and make sure that you get alerted if it finds any errors.

If memory serves, you just say "DBCC CHECKDB" in the database you want to
examine. But check Books Online for details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

You can think of SQL Profiler as well if still you doubt on the SQL
Activities for some particular time.

Thanks
Ajay

Can SQL 2000 restore database and user login on another server?

Can SQL 2000 restore user login and database to another
domain and computer name? If it can't, how to export user
login to another computer?
You can migrate logins from one server to another server by generating a
script using a routine called sp_help_revlogin. Here is an article I wrote
about it:
http://databasejournal.com/features/...le.php/2228611
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:481301c49fe2$d7215e10$a401280a@.phx.gbl...
> Can SQL 2000 restore user login and database to another
> domain and computer name? If it can't, how to export user
> login to another computer?

Can SQL 2000 restore database and user login on another server?

Can SQL 2000 restore user login and database to another
domain and computer name? If it can't, how to export user
login to another computer?You can migrate logins from one server to another server by generating a
script using a routine called sp_help_revlogin. Here is an article I wrote
about it:
http://databasejournal.com/features/mssql/article.php/2228611
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Mesak" <anonymous@.discussions.microsoft.com> wrote in message
news:481301c49fe2$d7215e10$a401280a@.phx.gbl...
> Can SQL 2000 restore user login and database to another
> domain and computer name? If it can't, how to export user
> login to another computer?