new to sql please help

i use access 2000 as my front end. i had a form named main with a couple of unbound fields on it . i used to put in last name and first name and then in a query i would put "like [forms]![main]![text0] & "*"
and also "like [forms]![main]![text2] & "*"

the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.

how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.

by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.

thanks in advance.Hoe are you connecting to SQL Sever?

You shouldn't have to change your code...did you create linked tables?|||im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.

i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.

my code i had to change was something like

dim db as dao.database
dim rs as dao.recordset

i had to change that to
dim conn as adodb.connection
dim rs as new adodb.recordset

and quite a few other things

if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .|||this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If|||Well this is a TOTAL guess...

If you're doing passthrus, you'll need SQL Server syntax..

(Why not just call stored procedures if you're making such a radical change?)

anyway...

SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
FROM tblpatientinfo
WHERE UPPER(LEFT(chartnumber,5)) = " _
& "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

my front end skills have gotten rusty...

You might want to also look to post here:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.

if i use select lname from tblpatientinfo where lname="riley"

my form will only give me patients with last name riley

but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.

new to sql - trying to convert vb function to udf

Hi

I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000.
I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.

The VB function looks like this...

----------
Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency

Dim iDuration As Integer
iDuration = DateDiff("d", datFromDate, datToDate) + 1

' if either date not in current year then 0 (both dates should be in same year)
If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then
calcPercentOfBudget = 0
End If

Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer

' if passed month outside of period then 0
If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then
calcPercentOfBudget = 0
Exit Function
End If

idaysInMonth = daysInMonth(iMonth, Year(Now))


'if from date and to date in same month then 100% of budget
If Month(datFromDate) = Month(datToDate) Then
calcPercentOfBudget = cBudget
Exit Function
End If

' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%)
If Month(datFromDate) = iMonth Then
'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget
calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget
Exit Function
End If

'if passed month in To month then ratio of passed month (caters for last day of month - 100%)
If Month(datToDate) = iMonth Then
'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget
calcPercentOfBudget = Day(datToDate) / iDuration * cBudget
Exit Function
End If

' if passed month within period then 100%
If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then
'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget
calcPercentOfBudget = idaysInMonth / iDuration * cBudget
Exit Function
End If

End Function
----------
Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer

Dim datTemp As Date

datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
datTemp = DateAdd("m", 1, datTemp)
datTemp = DateAdd("d", -1, datTemp)

daysInMonth = Day(datTemp)

End Function
----------

I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.

However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

case
when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate)
set @.returnvalue = 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo)
set @.returnvalue 0
when month(@.datFrom) = month(@.datTo)
@.set @.returnvalue = @.cBudget
when month(@.datFrom) = @.iMonth
set @.returnvalue = (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth
set @.returnvalue = Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo)
set @.returnvalue = @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

This is my first post to this forum - so any constructive criticism will be welcomed.

Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!

All help greatfully received,
Paul:eek:Case statements work a little differently in T-SQL. Try this:

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

select @.returnvalue =
case when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate) then 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo) then 0
when month(@.datFrom) = month(@.datTo) then @.cBudget
when month(@.datFrom) = @.iMonth then (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth then Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo) then @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

Case in T-SQL returns a value, rather than executes a block of code.|||fantastic, after a couple of other debugs from translation I have a result that works!

Many thanks :)

New to SQL

Hi
I am setting for the first time SQL to access a database. I can get the
Enterprise Manager and the Network Utility to see the databases (on 2
computers) but when I go to the program to access the databases, I get a
Connection error.
Strangely, I set up the exact replica on the server and it can only see
itself and not the other computer.
I have scanned newsgroups etc but cannot locate the problem.
Thanks
cds
Could you please post the exact error message? There are various types of
connectivity issues, and we need to see the error message, to be able to
understand what's happening.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"cds" <cdsaus2001@.yahoo.com.au> wrote in message
news:4140ccde$0$3711$afc38c87@.news.optusnet.com.au ...
> Hi
> I am setting for the first time SQL to access a database. I can get the
> Enterprise Manager and the Network Utility to see the databases (on 2
> computers) but when I go to the program to access the databases, I get a
> Connection error.
> Strangely, I set up the exact replica on the server and it can only see
> itself and not the other computer.
> I have scanned newsgroups etc but cannot locate the problem.
> Thanks
> cds
>
sql

new to mssql problem with query conversion

im managing most queries without any problems (im converting from access to mssql) but this one is causing me grief - how do i put this into mssql ?

SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted, dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown, dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));

Instead of this,

IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3

Use

case
 when [Expr1]<1000 then [Expr1]*0.3
 when [Expr1]<2000 then [Expr1]*0.4
 else [Expr1]*0.5
end as Expr3

similar syntax for Expr4 too.

Hope that helps.|||ill give it a go thanks!|||ive got as far as this :-

CREATE PROCEDURE solnorth AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.SourceOfLead, dbo.Lead.DateOfLead, dbo.Mortgage.MortgageAppSubmitted, dbo.Mortgage.MortgageOfferedAccepted, dbo.Mortgage.MortgageDrawndown, dbo.Mortgage.MortgageApplicationClosed,
dbo.Mortgage.MortgageCommissionAnticipated+dbo.Life.LifeCommissionAnticipated+dbo.BuildingsAndContents.BandCCommissionAnticipated+dbo.OtherBusiness.OtherBusinessCommissionAnticipated AS Expr1,
dbo.commissions.MortgageCommissionReceived+dbo.commissions.LifeCommissionReceived+dbo.commissions.BandCCommissionReceived+dbo.commissions.OtherBusinessCommissionReceived AS Expr2,
case
when Expr1<1000 then Expr1*0.3
when Expr1<2000 then Expr1*0.4
else Expr1*0.5
end as Expr3,
case
when Expr2<1000 then Expr2*0.3
when Expr2<2000 then Expr2*0.4
else Expr2*0.5
end as Expr4
FROM (((((dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID) LEFT JOIN dbo.OtherBusiness ON dbo.Personal.ID = dbo.OtherBusiness.ID)
LEFT JOIN dbo.BuildingsAndContents ON dbo.Personal.ID = dbo.BuildingsAndContents.ID) LEFT JOIN dbo.Commissions ON dbo.Personal.ID = dbo.Commissions.ID)
LEFT JOIN dbo.Life ON dbo.Personal.ID = dbo.Life.ID
WHERE (((dbo.Lead.SourceOfLead) Like 'Solutions*'));
GO

but im getting invalid column name for Expr1 and Expr2 ?
where am i going wrong ?

thanks

mark|||You cannot use Expr1 and Expr2 (an alias) in the case. Just replace Expr1 and Expr2 with the same expressions you use to create Expr1 and Expr2.|||You will have to replace
Expr1
with
dbo.Mortgage.MortgageCommissionAnticipated+dbo.Life.LifeCommissionAnticipated+dbo.BuildingsAndContents.BandCCommissionAnticipated+dbo.OtherBusiness.OtherBusinessCommissionAnticipated

and

Expr2
dbo.commissions.MortgageCommissionReceived+dbo.commissions.LifeCommissionReceived+dbo.commissions.BandCCommissionReceived+dbo.commissions.OtherBusinessCommissionReceived|||great thanks that worked - well theres no errors now - just some errors with the datagrid but i should be able to sort that - thanks for the help everyone!|||quick (question how do i create Expr1 ?
eg

[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1

id still like to show expr1 on a datagrid if i could (eg add the columns together)

thanks

mark|||Umm, you create it by placing what you have in the select list. Am I missing something?|||heh my bad (im still learning!)
all works great now thanks for all the help

New to MS SQL

I have a rookie question, I have used Access in the past and I am in the beginning stages of learning MS SQL. Does MS SQL have anything that is equilivant to the "memo" data type in Access so that you can format the data to make notes more readable by honoring the <carriage return> and the <tab> in your free text?Yeah, TEXT datatype (which is not the Access datatype)

Don't use it...

can you live with varchar(8000)|||Thanks for the info... I'll see what happens there.sql

New to Crystal Reports

Hi,

I am new to crystal Reports. I am on a project to migrate database from MS Access to MS SQL 2000 for a project which is in VB6. We are using Crysal Report 7.0. Earlier version reports in MS Access were working fine and used SelectionFormula. I want to use the same thing in my next MS SQL version also. But I don't know where and how to begin.

In my project, user will chose company (from combo box), subcompany (combo box), location (combo box), Item Category (combo box) and press the Report button which triggers the below code. Here each company name, subcompany name, location, item category were from different master tables and list of items for them is in another table. In report I need to list items for user selected company, subcompany, location, item category.

The code is like this:

Private Sub cmdAssetRpt_Click()
Dim crptApp As New CRAXDRT.Application
Dim crptRep As New CRAXDRT.Report

Set crptRep = crptApp.OpenReport(App.Path & "\Reports\AssetDetailsLocationWiseRpt.rpt")
crptRep.RecordSelectionFormula = "{LocationMaster.LocationName}='" & (cboLocation.Text) & _
"'and {ItemDetails.ItemGroupCod}='" & strItemgrpCode & "'"
If frmReports.CRViewer1.IsBusy = False Then
frmReports.CRViewer1.ReportSource = crptRep
frmReports.CRViewer1.DisplayGroupTree = False
frmReports.CRViewer1.ViewReport
End If
Exit Sub
End If
End sub

I was surprised how SelectionFormula will query my database to filter only requiered data and send it to report?
I need complete info about Reports, SelectionFormula, Tips, sample code anything. Plese help me. its urgent for me. Only this work is leftout in my project!!query is inside report, and selection formula just filters records returned by the query.sql

new tables everyday

We have a software package that exports its data in MS Access 97 and 2000
formats. It overwrites and creates a new Access file each time it exports.
These exports serve a handful of front end applications. I'd like to get
this data tied to our SQL 2005 server. Any suggestions on whether to look at
just linking these in vs importing, etc?Hi,
Are you using the data imported just to query against it from an application
that connects to SQL Server? In that case you can indeed avoid the hassle
of importing by setting up linked servers (see
http://msdn2.microsoft.com/en-us/library/ms190479.aspx). This will allow
you to access your data over connections to the SQL Server using a four part
object name i.e. linkedservername.database.schema.table. You can also use
functions like OPENROWSET and OPENDATASOURCE, but if you access the external
data in various places in your code then I would suggest that you use linked
servers instead.
Jonathan
"Schmeg" <sschmeg@.yahoo.com> wrote in message
news:OE54Ig14HHA.1484@.TK2MSFTNGP06.phx.gbl...
> We have a software package that exports its data in MS Access 97 and 2000
> formats. It overwrites and creates a new Access file each time it exports.
> These exports serve a handful of front end applications. I'd like to get
> this data tied to our SQL 2005 server. Any suggestions on whether to look
> at just linking these in vs importing, etc?
>|||On Aug 20, 11:31 am, "Schmeg" <ssch...@.yahoo.com> wrote:
> We have a software package that exports its data in MS Access 97 and 2000
> formats. It overwrites and creates a new Access file each time it exports.
> These exports serve a handful of front end applications. I'd like to get
> this data tied to our SQL 2005 server. Any suggestions on whether to look at
> just linking these in vs importing, etc?
Why do you export the data to Access? Is it for off line use or just
because? I support multipule Access Projects that connect directly to
MS SQL server and they have thousands of lines of code for working
with the data. This works well and is good for rappid development.
On the other hand if you only want a couple of the tables in the SQL
DB available for the users to work in then link the tables in access.

new SSRS install, works great until I try to access Oracle datasource - System.Data.Oracle

re: new SSRS install, works great until I try to access Oracle
datasource - System.Data.OracleClient requires Oracle client software
>From my desktop the report accessing an Oracle 9i db with no problem.
I've deployed other reports accesing sql server with no problem.
Now I deploy the report and try to access my Oracle source and get:
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater
On the server in question, I know they've installed the oracle client.
I can access the db from sql plus with no problem.
As the user that I access ssrs credentials, i remote login and am able
to write to oracle home.. C:\oracle\ora10g\NETWORK\ADMIN
i've confirmed my database is in tnsnames and it works from sqlplus.On Nov 5, 3:27 pm, jobs <j...@.webdos.com> wrote:
> re: new SSRS install, works great until I try to access Oracle
> datasource - System.Data.OracleClient requires Oracle client software
> >From my desktop the report accessing an Oracle 9i db with no problem.
> I've deployed other reports accesing sql server with no problem.
> Now I deploy the report and try to access my Oracle source and get:
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater
> On the server in question, I know they've installed the oracle client.
> I can access the db from sql plus with no problem.
> As the user that I access ssrs credentials, i remote login and am abl
> to write to oracle home.. C:\oracle\ora10g\NETWORK\ADMIN
> i've confirmed my database is in tnsnames and it works from sqlplus.
This is just a guess; but, have you checked for all necessary assembly
references from the Oracle-side. I would start with maybe getting a
comparison tool (possibly 30-day trial version of Merge 2007:
http://www.araxis.com/ ) and compare the directory structure of your
machine versus the machine where the report is deployed (in reference
to Oracle 8.1.7). Also, you might want to check into the driver for
the SSRS connection to Oracle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you have the Oracle client installed on the server I think the issue is
that the directory it is installed in is not accessible to RS. I googled
this a little (oracle client reporting services permissions)
http://technet.microsoft.com/en-us/library/ms159689.aspx
a.. (Optional) Install Oracle client tools on the report server if your
reports use the Microsoft .NET Framework Data Provider for Oracle. After you
install the client tools, you must restart IIS. To support subscriptions to
reports that use Oracle data sources, you must grant permissions to the
NetworkService account. For more information, see
http://support.microsoft.com/kb/870668.
http://support.microsoft.com/kb/870668
To work around this problem, add permissions for the NETWORK SERVICE
security principal to the Oracle\Oracle version\bin directory and to the
Oracle\Oracle version\network\admin directory. To do this, follow these
steps. ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jobs" <jobs@.webdos.com> wrote in message
news:1194298047.081192.142180@.o3g2000hsb.googlegroups.com...
> re: new SSRS install, works great until I try to access Oracle
> datasource - System.Data.OracleClient requires Oracle client software
>
>>From my desktop the report accessing an Oracle 9i db with no problem.
> I've deployed other reports accesing sql server with no problem.
> Now I deploy the report and try to access my Oracle source and get:
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater
> On the server in question, I know they've installed the oracle client.
> I can access the db from sql plus with no problem.
> As the user that I access ssrs credentials, i remote login and am able
> to write to oracle home.. C:\oracle\ora10g\NETWORK\ADMIN
> i've confirmed my database is in tnsnames and it works from sqlplus.
>

New SQL Login cannot list DB in EM

I have a SQL Server 2000 instance running sp3 (in a
cluster).
When I create a NEW SQL user, and give that user access to
ONE database (public and DBO) I cannot list ANY databases
from Enterprise Manager when I login with that user. If I
refresh the Enterprise Manager Databases View, eventually
it will display the Databases - but only after about 10
minutes and I get access violation errors in my SQL and NT
logs. Even if I create a 'dummy SQL login' and give it
access to Northwind, the same results - I cannot list
databases in Enterprise Manager. I tried making the
Defalt DB both Master and the actual DB with the same
results
I tried to recreate this problem on another Clustered SQL
Server 2000 Instance - this one running sp3a, and I am NOT
able to recreate this problem.
I can't imagine that this is a Service Pack issue. Does
anyone have any suggestions?
If you remove the guest user in any database a similar problem can occur.
Look at the following article:
http://support.microsoft.com/?kbid=315523
PRB: Removal of Guest Account May Cause Handled Exception Access Violation
in SQL Server
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thank you very much!!! I ran the script in the KB article and it fixed my problem!
sql

New SQL Login cannot list DB in EM

I have a SQL Server 2000 instance running sp3 (in a
cluster).
When I create a NEW SQL user, and give that user access to
ONE database (public and DBO) I cannot list ANY databases
from Enterprise Manager when I login with that user. If I
refresh the Enterprise Manager Databases View, eventually
it will display the Databases - but only after about 10
minutes and I get access violation errors in my SQL and NT
logs. Even if I create a 'dummy SQL login' and give it
access to Northwind, the same results - I cannot list
databases in Enterprise Manager. I tried making the
Defalt DB both Master and the actual DB with the same
results
I tried to recreate this problem on another Clustered SQL
Server 2000 Instance - this one running sp3a, and I am NOT
able to recreate this problem.
I can't imagine that this is a Service Pack issue. Does
anyone have any suggestions?If you remove the guest user in any database a similar problem can occur.
Look at the following article:
http://support.microsoft.com/?kbid=315523
PRB: Removal of Guest Account May Cause Handled Exception Access Violation
in SQL Server
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thank you very much!!! I ran the script in the KB article and it fixed my problem!

New SQL Login cannot list DB in EM

I have a SQL Server 2000 instance running sp3 (in a
cluster).
When I create a NEW SQL user, and give that user access to
ONE database (public and DBO) I cannot list ANY databases
from Enterprise Manager when I login with that user. If I
refresh the Enterprise Manager Databases View, eventually
it will display the Databases - but only after about 10
minutes and I get access violation errors in my SQL and NT
logs. Even if I create a 'dummy SQL login' and give it
access to Northwind, the same results - I cannot list
databases in Enterprise Manager. I tried making the
Defalt DB both Master and the actual DB with the same
results
I tried to recreate this problem on another Clustered SQL
Server 2000 Instance - this one running sp3a, and I am NOT
able to recreate this problem.
I can't imagine that this is a Service Pack issue. Does
anyone have any suggestions?If you remove the guest user in any database a similar problem can occur.
Look at the following article:
http://support.microsoft.com/?kbid=315523
PRB: Removal of Guest Account May Cause Handled Exception Access Violation
in SQL Server
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thank you very much!!! I ran the script in the KB article and it fixed my
problem!

New SQL Express

I am working with Microsoft Office Access 2003 on MSWXP Professional SP2. In attempting to open one of the sample database projects that come with Access 2003, NorthwindCS.adp, I get the following message: "Microsoft Access could not find either Microsoft SQL Server 2000 Desktop Engine or Microsoft SQL Server installed on your computer. Click OK to display the Data Link Properties dialog box and log on to a remote server. If you want to install SQL Server 2000 Desktop Engine on this computer first, click Cancel, install it from the MSDE2000 folder on the Office CD-ROM, and then reopen this Access project". Instead of downloading & installing the Microsoft SQL Server 2000 Desktop Engine, I downloaded & installed the SQL Server 2005 Express Edition SP1 instead. After having restarted the computer and relaunching Access 2003 & opening the NorthwindCS.adp I still get the same message as mentioned previously above. My question is can the SQL Server 2005 Express Edition SP1 be used or must the SQL Server 2000 Desktop Engine be used instead. I understand that running Microsoft Windows Vista must use the SQL Server 2005 Express Edition SP1 but I am running MSWXP Professional SP2 instead. Advise?

Hi,

AFAIC remember 2003 ADP projects are not fully supported / working in conjunction with SQL Server Express even with t the compt. settings changed.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

New SQL Database

I have set up applications in Access and am now starting in SQL Server. I
have taken a SQL class, but it did not cover starting from scratch. Our IT
dept set up a blank SQL database for me and gave me all permissions, but I'm
unsure of how to proceed. Do I need some sort of an interface set up on my
PC to connect?
You probably want to get the SQL Server install and choose Client Tools Only
option. Look in the Books Online that installs for how to register a server
in the Enterprise Manager (that is the SQL Server GUI for creating
databases, viewing various objects, etc.) The Query Analyzer is the utility
for creating everything but the most basic objects...
Post back as needed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"bills249" <bills249@.discussions.microsoft.com> wrote in message
news:6D26CB63-283F-4395-B990-680187867E7B@.microsoft.com...
>I have set up applications in Access and am now starting in SQL Server. I
> have taken a SQL class, but it did not cover starting from scratch. Our
> IT
> dept set up a blank SQL database for me and gave me all permissions, but
> I'm
> unsure of how to proceed. Do I need some sort of an interface set up on
> my
> PC to connect?

New SQL Database

I have set up applications in Access and am now starting in SQL Server. I
have taken a SQL class, but it did not cover starting from scratch. Our IT
dept set up a blank SQL database for me and gave me all permissions, but I'm
unsure of how to proceed. Do I need some sort of an interface set up on my
PC to connect?You probably want to get the SQL Server install and choose Client Tools Only
option. Look in the Books Online that installs for how to register a server
in the Enterprise Manager (that is the SQL Server GUI for creating
databases, viewing various objects, etc.) The Query Analyzer is the utility
for creating everything but the most basic objects...
Post back as needed...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"bills249" <bills249@.discussions.microsoft.com> wrote in message
news:6D26CB63-283F-4395-B990-680187867E7B@.microsoft.com...
>I have set up applications in Access and am now starting in SQL Server. I
> have taken a SQL class, but it did not cover starting from scratch. Our
> IT
> dept set up a blank SQL database for me and gave me all permissions, but
> I'm
> unsure of how to proceed. Do I need some sort of an interface set up on
> my
> PC to connect?

New Role Permissions

Hi,
I have installed RS in a production environment successfully. I then added a
user into the windows registry. I can access all 'tool bar' options like
'Upload Report' as an administrator on the localhost, but when I access RS by
the new role I have created I only get the almost blank 'home' page. I have
given the new role user every permission in RS via site wide configuration
settings ect., but I still don't get the upload option and other two options
on the 'tool bar'. What do Ineed to do to give the new user adminstration
wide access in RS, but not an administrator in windows.
ThanksYou need to set item-level permissions for that user.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Quinton" <Quinton@.discussions.microsoft.com> wrote in message
news:3BCBB0BF-D591-4189-9FB1-877599C85422@.microsoft.com...
> Hi,
> I have installed RS in a production environment successfully. I then added
> a
> user into the windows registry. I can access all 'tool bar' options like
> 'Upload Report' as an administrator on the localhost, but when I access RS
> by
> the new role I have created I only get the almost blank 'home' page. I
> have
> given the new role user every permission in RS via site wide configuration
> settings ect., but I still don't get the upload option and other two
> options
> on the 'tool bar'. What do Ineed to do to give the new user adminstration
> wide access in RS, but not an administrator in windows.
> Thanks|||Hi,
Thanks - but I must be a thick head, as the new user does not have any items
to set permissions on. Do you mean that as administrator I create a directory
and then set item level permissions on that for the new user?
Thnks,
Quinton
"Lev Semenets [MSFT]" wrote:
> You need to set item-level permissions for that user.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Quinton" <Quinton@.discussions.microsoft.com> wrote in message
> news:3BCBB0BF-D591-4189-9FB1-877599C85422@.microsoft.com...
> > Hi,
> > I have installed RS in a production environment successfully. I then added
> > a
> > user into the windows registry. I can access all 'tool bar' options like
> > 'Upload Report' as an administrator on the localhost, but when I access RS
> > by
> > the new role I have created I only get the almost blank 'home' page. I
> > have
> > given the new role user every permission in RS via site wide configuration
> > settings ect., but I still don't get the upload option and other two
> > options
> > on the 'tool bar'. What do Ineed to do to give the new user adminstration
> > wide access in RS, but not an administrator in windows.
> >
> > Thanks
>
>|||Yes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Quinton" <Quinton@.discussions.microsoft.com> wrote in message
news:902431F4-EBAE-4F44-A22A-4D33A68F2893@.microsoft.com...
> Hi,
> Thanks - but I must be a thick head, as the new user does not have any
> items
> to set permissions on. Do you mean that as administrator I create a
> directory
> and then set item level permissions on that for the new user?
> Thnks,
> Quinton
> "Lev Semenets [MSFT]" wrote:
>> You need to set item-level permissions for that user.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Quinton" <Quinton@.discussions.microsoft.com> wrote in message
>> news:3BCBB0BF-D591-4189-9FB1-877599C85422@.microsoft.com...
>> > Hi,
>> > I have installed RS in a production environment successfully. I then
>> > added
>> > a
>> > user into the windows registry. I can access all 'tool bar' options
>> > like
>> > 'Upload Report' as an administrator on the localhost, but when I access
>> > RS
>> > by
>> > the new role I have created I only get the almost blank 'home' page. I
>> > have
>> > given the new role user every permission in RS via site wide
>> > configuration
>> > settings ect., but I still don't get the upload option and other two
>> > options
>> > on the 'tool bar'. What do Ineed to do to give the new user
>> > adminstration
>> > wide access in RS, but not an administrator in windows.
>> >
>> > Thanks
>>|||Thanks again Lev,
... but that didn't work, the new user that I added in the 'users' group in
windows still didn't get any tabs (content/properties) or any of the other
stuff needed to upload reports. I decided to cheat and just add my user into
the Administrators group. This worked, but, when I tried to add a data source
I get the error 'The underlying connection was closed: Could not establish
trust relationship with remote server.' I did try to create a connection with
every type of credentials.
This is my 4th installation of RS, the configurations of this installation
are:
Windows server 2003.
Reports/ReportServer are on a different drive to SQL instance.
SQL data on a different drive again.
Any ideas'
Quinton
"Lev Semenets [MSFT]" wrote:
> Yes.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Quinton" <Quinton@.discussions.microsoft.com> wrote in message
> news:902431F4-EBAE-4F44-A22A-4D33A68F2893@.microsoft.com...
> > Hi,
> >
> > Thanks - but I must be a thick head, as the new user does not have any
> > items
> > to set permissions on. Do you mean that as administrator I create a
> > directory
> > and then set item level permissions on that for the new user?
> >
> > Thnks,
> > Quinton
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> You need to set item-level permissions for that user.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Quinton" <Quinton@.discussions.microsoft.com> wrote in message
> >> news:3BCBB0BF-D591-4189-9FB1-877599C85422@.microsoft.com...
> >> > Hi,
> >> > I have installed RS in a production environment successfully. I then
> >> > added
> >> > a
> >> > user into the windows registry. I can access all 'tool bar' options
> >> > like
> >> > 'Upload Report' as an administrator on the localhost, but when I access
> >> > RS
> >> > by
> >> > the new role I have created I only get the almost blank 'home' page. I
> >> > have
> >> > given the new role user every permission in RS via site wide
> >> > configuration
> >> > settings ect., but I still don't get the upload option and other two
> >> > options
> >> > on the 'tool bar'. What do Ineed to do to give the new user
> >> > adminstration
> >> > wide access in RS, but not an administrator in windows.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

New Role Assignment Question

I have just set up SQL Reporting Services in our Development environment and
want to set all users with browser or read-only access to our reports but do
not see how. I am going in through the web interface and it tells me that I
must have a valid domain group or interface. Any idea on how to just select
"Everyone"?DomainName\Everyone
ComputerName\Everyone
HTH, Jens Süßmeyer
--
http://www.sqlserver2005.de
--
"Charles Collins" <Charles Collins@.discussions.microsoft.com> schrieb im
Newsbeitrag news:234DF76B-A041-4989-A116-6CADDEB19E9B@.microsoft.com...
>I have just set up SQL Reporting Services in our Development environment
>and
> want to set all users with browser or read-only access to our reports but
> do
> not see how. I am going in through the web interface and it tells me that
> I
> must have a valid domain group or interface. Any idea on how to just
> select
> "Everyone"?|||Currently Reporting Services only supports Windows Authentication. Which
means you need to create a network group (with everyone you want) in it and
then give that network group access to the reports. Reporting Services
security is extensible though if you want to create your own security
extension (Not an easy task). Here is link to a microsoft example of
creating your own security extension...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
"Charles Collins" wrote:
> I have just set up SQL Reporting Services in our Development environment and
> want to set all users with browser or read-only access to our reports but do
> not see how. I am going in through the web interface and it tells me that I
> must have a valid domain group or interface. Any idea on how to just select
> "Everyone"?|||When I try that I get:
The user or group name '(domain or computer name)\everyone' is not
recognized. (rsUnknownUserName)
"Jens Sü�meyer" wrote:
> DomainName\Everyone
> ComputerName\Everyone
> HTH, Jens Sü�meyer
> --
> http://www.sqlserver2005.de
> --
> "Charles Collins" <Charles Collins@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:234DF76B-A041-4989-A116-6CADDEB19E9B@.microsoft.com...
> >I have just set up SQL Reporting Services in our Development environment
> >and
> > want to set all users with browser or read-only access to our reports but
> > do
> > not see how. I am going in through the web interface and it tells me that
> > I
> > must have a valid domain group or interface. Any idea on how to just
> > select
> > "Everyone"?
>
>|||Is there an way to assign mulitple users an role at once?
"Jens Sü�meyer" wrote:
> DomainName\Everyone
> ComputerName\Everyone
> HTH, Jens Sü�meyer
> --
> http://www.sqlserver2005.de
> --
> "Charles Collins" <Charles Collins@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:234DF76B-A041-4989-A116-6CADDEB19E9B@.microsoft.com...
> >I have just set up SQL Reporting Services in our Development environment
> >and
> > want to set all users with browser or read-only access to our reports but
> > do
> > not see how. I am going in through the web interface and it tells me that
> > I
> > must have a valid domain group or interface. Any idea on how to just
> > select
> > "Everyone"?
>
>

New request is not allowed to start

Hi folks,
We have an Access front end that happily does all its reading, riting
and rithmetic onto a SQL Server 2005 BE.
There is a timer event on one of the user forms that polls to see if
the DBA wants to kick you out (a flag on a table that gets set by an
Administrators task, and if it is on, then users get a message).
This all works fine, unless the user is running one of the two stored
procedures that take a long while to execute (up to 60 seconds).
When that happens, if the execution time of the sp is longer than the
polling time interval (20 seconds), a conflict seems to occur, and we
get the message "New request is not allowed to start because it should
come with a valid transaction descriptor."
We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
helped.
Any ideas on how we can solve the problem?
Can we somehow run the procedure as a batch that is initiated by the
front-end, but doesn't need to maintain a connection with it?
We were required by the Powers That Be to open a single connection on
our front end for any DB access, rather than open a connection for
each form or process. Could this have something to do with it?
Any ideas gratefully received, Ray<ray@.aic.net.au> wrote in message
news:1194497708.231234.313780@.z24g2000prh.googlegroups.com...
> Hi folks,
> We have an Access front end that happily does all its reading, riting
> and rithmetic onto a SQL Server 2005 BE.
> There is a timer event on one of the user forms that polls to see if
> the DBA wants to kick you out (a flag on a table that gets set by an
> Administrators task, and if it is on, then users get a message).
> This all works fine, unless the user is running one of the two stored
> procedures that take a long while to execute (up to 60 seconds).
> When that happens, if the execution time of the sp is longer than the
> polling time interval (20 seconds), a conflict seems to occur, and we
> get the message "New request is not allowed to start because it should
> come with a valid transaction descriptor."
> We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
> READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
> helped.
> Any ideas on how we can solve the problem?
> Can we somehow run the procedure as a batch that is initiated by the
> front-end, but doesn't need to maintain a connection with it?
> We were required by the Powers That Be to open a single connection on
> our front end for any DB access, rather than open a connection for
> each form or process. Could this have something to do with it?
> Any ideas gratefully received, Ray
>
Have you considered using
ALTER DATABASE SET SINGLE_USER WITH
[
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
];
?
David Portas|||Well, no, I hadn't considered that because the problem can occur when
only one user is connected to the database.
I'm sure that the conflict is not between User A and User B.
As far as I can see (and our error trapping seems to confirm this),
the problem is that Stored Proc 1 executed by User A causes a conflict
with Stored Proc 2 also executed by User A (the polling process).
So thanks for your suggestion, but I am hoping to find a way to allow
the user to initiate and receive notifications from two separate
processes. Perhaps one needs to run independently somehow, just by
being initiated at the front end? Or do we need multiple connections
from the front end?
Thanks,
Ray|||Just for the record, in case anybody else encounters this, we opened a
separate connection in the Access front end and did the polling
through it, which resolved the problemo.

New request is not allowed to start

Hi folks,
We have an Access front end that happily does all its reading, riting
and rithmetic onto a SQL Server 2005 BE.
There is a timer event on one of the user forms that polls to see if
the DBA wants to kick you out (a flag on a table that gets set by an
Administrators task, and if it is on, then users get a message).
This all works fine, unless the user is running one of the two stored
procedures that take a long while to execute (up to 60 seconds).
When that happens, if the execution time of the sp is longer than the
polling time interval (20 seconds), a conflict seems to occur, and we
get the message "New request is not allowed to start because it should
come with a valid transaction descriptor."
We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
helped.
Any ideas on how we can solve the problem?
Can we somehow run the procedure as a batch that is initiated by the
front-end, but doesn't need to maintain a connection with it?
We were required by the Powers That Be to open a single connection on
our front end for any DB access, rather than open a connection for
each form or process. Could this have something to do with it?
Any ideas gratefully received, Ray
Well, no, I hadn't considered that because the problem can occur when
only one user is connected to the database.
I'm sure that the conflict is not between User A and User B.
As far as I can see (and our error trapping seems to confirm this),
the problem is that Stored Proc 1 executed by User A causes a conflict
with Stored Proc 2 also executed by User A (the polling process).
So thanks for your suggestion, but I am hoping to find a way to allow
the user to initiate and receive notifications from two separate
processes. Perhaps one needs to run independently somehow, just by
being initiated at the front end? Or do we need multiple connections
from the front end?
Thanks,
Ray
|||Just for the record, in case anybody else encounters this, we opened a
separate connection in the Access front end and did the polling
through it, which resolved the problemo.

New request is not allowed to start

Hi folks,
We have an Access front end that happily does all its reading, riting
and rithmetic onto a SQL Server 2005 BE.
There is a timer event on one of the user forms that polls to see if
the DBA wants to kick you out (a flag on a table that gets set by an
Administrators task, and if it is on, then users get a message).
This all works fine, unless the user is running one of the two stored
procedures that take a long while to execute (up to 60 seconds).
When that happens, if the execution time of the sp is longer than the
polling time interval (20 seconds), a conflict seems to occur, and we
get the message "New request is not allowed to start because it should
come with a valid transaction descriptor."
We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
helped.
Any ideas on how we can solve the problem?
Can we somehow run the procedure as a batch that is initiated by the
front-end, but doesn't need to maintain a connection with it?
We were required by the Powers That Be to open a single connection on
our front end for any DB access, rather than open a connection for
each form or process. Could this have something to do with it?
Any ideas gratefully received, Ray<ray@.aic.net.au> wrote in message
news:1194497708.231234.313780@.z24g2000prh.googlegroups.com...
> Hi folks,
> We have an Access front end that happily does all its reading, riting
> and rithmetic onto a SQL Server 2005 BE.
> There is a timer event on one of the user forms that polls to see if
> the DBA wants to kick you out (a flag on a table that gets set by an
> Administrators task, and if it is on, then users get a message).
> This all works fine, unless the user is running one of the two stored
> procedures that take a long while to execute (up to 60 seconds).
> When that happens, if the execution time of the sp is longer than the
> polling time interval (20 seconds), a conflict seems to occur, and we
> get the message "New request is not allowed to start because it should
> come with a valid transaction descriptor."
> We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
> READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
> helped.
> Any ideas on how we can solve the problem?
> Can we somehow run the procedure as a batch that is initiated by the
> front-end, but doesn't need to maintain a connection with it?
> We were required by the Powers That Be to open a single connection on
> our front end for any DB access, rather than open a connection for
> each form or process. Could this have something to do with it?
> Any ideas gratefully received, Ray
>
Have you considered using
ALTER DATABASE SET SINGLE_USER WITH
[
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
];
?
--
David Portas|||Well, no, I hadn't considered that because the problem can occur when
only one user is connected to the database.
I'm sure that the conflict is not between User A and User B.
As far as I can see (and our error trapping seems to confirm this),
the problem is that Stored Proc 1 executed by User A causes a conflict
with Stored Proc 2 also executed by User A (the polling process).
So thanks for your suggestion, but I am hoping to find a way to allow
the user to initiate and receive notifications from two separate
processes. Perhaps one needs to run independently somehow, just by
being initiated at the front end? Or do we need multiple connections
from the front end?
Thanks,
Ray|||Just for the record, in case anybody else encounters this, we opened a
separate connection in the Access front end and did the polling
through it, which resolved the problemo.

New Reporting Control

Does the new report control for vs 2005 utilize SOAP or the URL method
to access the report server?
I need to utilize a pure SOAP based solution to get reports from the
report server. Just curious if the new solution is used that way or if
rolling my own is the only option.
ThanksThe new controls use soap (web services). They are a much more complete
solution that the old way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivaldi" <ZachMattson@.gmail.com> wrote in message
news:1131634098.219113.321000@.g49g2000cwa.googlegroups.com...
> Does the new report control for vs 2005 utilize SOAP or the URL method
> to access the report server?
> I need to utilize a pure SOAP based solution to get reports from the
> report server. Just curious if the new solution is used that way or if
> rolling my own is the only option.
> Thanks
>