Showing posts with label application. Show all posts

Thursday, March 29, 2012

Help with Custom Security Extension and Application Pool Identity

This is an interesting one.
We have implemented a custom security extension that we call from
within an application. We pass in a userid and password into the
LogonUser method, which is then checked against our database.
However, we have a Master database, and then a few other small
databases for different clients, so the extension uses another
supplied value to go to the master database, and lookup which database
it needs to verify the user in.
So, in the RSReportServer.config file, we store the connection string
to the Master DB.
This is used to connect to the Master DB, and then lookup the
connection string for the secondary DB.
The connection string for the secondary DB uses Integrated
Authentication (as our application requires this).
When the Application Pool (in IIS) that ReportServer runs under, is
set to the NetworkUser, we get an error returned from the WebService
(called from within our application), which says that the Network
Service was not authorized to access the secondary DB. Understandable
:)
So, we changed the Application Pool, so that it uses a Domain account
as it's Identity. Now we receive the following error back (and in all
the log files I can find)..
System.Web.Services.Protocols.SoapException: An internal error
occurred on the report server. See the error log for more details.
--> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified. at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo) at
RSManagedCrypto.RSCrypto.ExportPublicKey() at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection() at
Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName) at
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name) at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTranslator
pathTranslator, String userSuppliedPath, String parameterName) at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) -- End of inner
exception stack trace -- at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items)
The Application Pool User is a member of the IIS_WPG group, and has
been granted write access to the Windows\Temp folder, and the MSSQL
folder where RS is installed (and sub folders).
I also tried adding it to the local machine admin group, and it made
no difference.
That user also has DBO access to all the DBs on our database server.
Can anyone help please?
Thanks
RichardWill you send the report server web service log file?
Also, can you verify that the new identity has a user profile on the machine
(the user should have an entry under c:\Documents and Settings)?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Greenwell" <lazygun@.gmail.com> wrote in message
news:42b7583d.0406251224.18af1b8@.posting.google.com...
> This is an interesting one.
> We have implemented a custom security extension that we call from
> within an application. We pass in a userid and password into the
> LogonUser method, which is then checked against our database.
> However, we have a Master database, and then a few other small
> databases for different clients, so the extension uses another
> supplied value to go to the master database, and lookup which database
> it needs to verify the user in.
> So, in the RSReportServer.config file, we store the connection string
> to the Master DB.
> This is used to connect to the Master DB, and then lookup the
> connection string for the secondary DB.
> The connection string for the secondary DB uses Integrated
> Authentication (as our application requires this).
> When the Application Pool (in IIS) that ReportServer runs under, is
> set to the NetworkUser, we get an error returned from the WebService
> (called from within our application), which says that the Network
> Service was not authorized to access the secondary DB. Understandable
> :)
> So, we changed the Application Pool, so that it uses a Domain account
> as it's Identity. Now we receive the following error back (and in all
> the log files I can find)..
> System.Web.Services.Protocols.SoapException: An internal error
> occurred on the report server. See the error log for more details.
> -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified. at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo) at
> RSManagedCrypto.RSCrypto.ExportPublicKey() at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at
Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection() at
> Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName) at
>
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(Stri
ng
> key) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty
(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(Stri
ng
> name) at
Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate) at
> Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTransl
ator
> pathTranslator, String userSuppliedPath, String parameterName) at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) -- End of inner
> exception stack trace -- at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) at
> Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items) -- End of inner exception stack
> trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items)
> The Application Pool User is a member of the IIS_WPG group, and has
> been granted write access to the Windows\Temp folder, and the MSSQL
> folder where RS is installed (and sub folders).
> I also tried adding it to the local machine admin group, and it made
> no difference.
> That user also has DBO access to all the DBs on our database server.
> Can anyone help please?
> Thanks
> Richard|||I have emailed you the log file for this problem.
The user that the application pool runs under does not have an entry
under Docs and Settings, but it is a different user to that which the
Report Server Windows Service runs under, which Does have an entry
under Docs and Settings.
I have never seen an app pool user have a Docs and Settings folder :)
Thank you
Richard
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message news:<OTBYLlYXEHA.748@.TK2MSFTNGP11.phx.gbl>...
> Will you send the report server web service log file?
> Also, can you verify that the new identity has a user profile on the machine
> (the user should have an entry under c:\Documents and Settings)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
<snip>sql

Help with creating xml shema

can somebody, please help me with my problem. I want to create xml shema to
use it with bulkload in vb.net application. I read about it and I must have
xsd, and xml file. So, if anybody can create for me that files, I would be
very thankful. These are my example tables.
T1
ID name city
1 john NY
2 mark NY
3 eric LA
T2
ID car price color
1 mazda 10000 black
1 honda 12000 blue
2 toyota 9000 green
3 audi 4000 black
1 ford 7800 red
please, please...if someone can help me.
thanks!
What does your XML look like?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bernard" <bernard@.form.hr> wrote in message
news:clm1td$hbv$1@.ls219.htnet.hr...
> can somebody, please help me with my problem. I want to create xml shema
> to use it with bulkload in vb.net application. I read about it and I must
> have xsd, and xml file. So, if anybody can create for me that files, I
> would be very thankful. These are my example tables.
> T1
> --
> ID name city
> 1 john NY
> 2 mark NY
> 3 eric LA
> --
> T2
> --
> ID car price color
> 1 mazda 10000 black
> 1 honda 12000 blue
> 2 toyota 9000 green
> 3 audi 4000 black
> 1 ford 7800 red
> --
> please, please...if someone can help me.
> thanks!
>
|||Bernard:
It looks like you already have data in those tables, so the easy thing to might to be issue a query on them like this
select * from t1 for xml auto
Then save those results out to file. You'll have to edit out the extra line feeds and add the XML prolog to make that a valid XML file.
Once you have that, and assuming you have the .NET SDK install, you should be able to use XSD.EXE to generate usable schmea.
If you already have the XML files you want to import, you can just feed them to XSD.EXE one at a time to get a matching W3C schema.
> Irwon: What does your XML look like?
If I'm reading this right, he doesn't have any.
Thanks,
Kent Tegels
MVP - SQL Server
The SSX FAQ & Blog:
http://tinyurl.com/6r4gb
Looking for XM, the GUI for SSX? See both:
http://tinyurl.com/4dfee and http://tinyurl.com/53hts
My Blog:
http://www.tegels.org/

Tuesday, March 27, 2012

Help with Collation - Decoding the Compatibility Designator

We have a 3rd-party written application that was installed and has
been upgraded on the same server. We want to move the database to a
SAN, but it has a compatibility collation. The select
serverproperty(N'Collation') command returns:
Compatibility_52_409_20001. I have been reading about decoding this
compatibility designator, but I'm stuck. I know the 52 refers to
Dictionary Order, case-insensitive, for use with 1252 character set.
The 409 is hex 0x409 or 1033, which translates to General Unicode.
From what I've read, the 20001 is also hex which means it is 0x20001
or 131073.
Anyone have any idea what the 131073 translation is? I'd like to know
what collation sequence to choose when installing SQL 2000 to match up
with this.
Any help greatly appreciated.
Thanks,
BillThere's some more info on this in 270042 INF: Description of SQL Server
Compatibility Collations (http://support.microsoft.com/?id=270042).
Here are the bits that make up the last portion of a compatibility
collation name:
+===============+=============+==================+
| Style | Value (Hex) | Value (Decimal) |
+===============+=============+==================+
| Ignore case | 0x00001 | 1 |
+===============+=============+==================+
| Ignore accent| 0x00002 | 2 |
+===============+=============+==================+
| Ignore Kana | 0x10000 | 65536 |
+===============+=============+==================+
| Ignore width | 0x20000 | 131072 |
+===============+=============+==================+
131073 is 131072 (ignore width) + 1 (ignore case). So this collation is
accent-sensitive, kana-sensitive, case-insensitive, width-insensitive.
> I'd like to know what collation sequence to choose
> when installing SQL 2000 to match up with this.
You cannot select a compatibility collation during a clean interactive SQL
2000 setup; you can only choose one of the so-called "named collations".
Your options here are:
- Install a SQL 7.0 instance with the same collation properties and do an
in place upgrade of this instance to SQL 2000. An in-place instance
upgrade always retains the existing collation settings, even if they are
non-standard.
- Use DTS to move the data in this database to a db with a more standard
collation that you can select as an instance-level collation when
installing SQL 2K.
- Modify stored procs that join temp tables to user tables to add a
"COLLATE database_default" to the column definition of temp table
char/varchar columns. In most apps there just a handful of these
modifications are required, and one benefit of this approach is that the
app will be "system collation agnostic" from that point forward and can be
successfully run in a mixed collation evironment or on any other server.
- Do a silent install of SQL 2000. Unlike an interactive install, for a
silent install you can specify any collation name in the .ISS file, even if
it is a compatibility collation.
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: bengels@.wi.rr.com (Bill Engels)
Newsgroups: microsoft.public.sqlserver.server
Subject: Help with Collation - Decoding the Compatibility Designator
Date: 31 Oct 2003 11:32:42 -0800
Organization: http://groups.google.com
Lines: 19
Message-ID: <7b42351f.0310311132.4c922d38@.posting.google.com>
NNTP-Posting-Host: 204.87.60.232
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1067628762 19180 127.0.0.1 (31 Oct 2003
19:32:42 GMT)
X-Complaints-To: groups-abuse@.google.com
NNTP-Posting-Date: Fri, 31 Oct 2003 19:32:42 +0000 (UTC)
Path:
cpmsftngxa06.phx.gbl!cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!po
stnews1.google.com!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:314087
X-Tomcat-NG: microsoft.public.sqlserver.server
We have a 3rd-party written application that was installed and has
been upgraded on the same server. We want to move the database to a
SAN, but it has a compatibility collation. The select
serverproperty(N'Collation') command returns:
Compatibility_52_409_20001. I have been reading about decoding this
compatibility designator, but I'm stuck. I know the 52 refers to
Dictionary Order, case-insensitive, for use with 1252 character set.
The 409 is hex 0x409 or 1033, which translates to General Unicode.
From what I've read, the 20001 is also hex which means it is 0x20001
or 131073.
Anyone have any idea what the 131073 translation is? I'd like to know
what collation sequence to choose when installing SQL 2000 to match up
with this.
Any help greatly appreciated.
Thanks,
Bill|||Thanks for the help Bart. Appreciate the answer and ALL your help in the newsgroups.
Bill

Wednesday, March 21, 2012

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Monday, March 12, 2012

Help with 1205 Not raised

Hi,
I have an application that causes a dead lock at random. The issue I am
having is, when the deadlock occurs, my applications is not recieving
any errors from the DB. ie, during the deadlock SQLServer is returning
an empty recordset and user is seeing a blank screen. The app logic
does not go into the Try Catch statment in the C# code. I can't
understand why my app is not receiveing 1205 error from SQL server when
dead lock occurs.
Any ides why this is happening?

Thanks
_GJKRead the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html#ADO.Net
especially this part: "If you use ExecuteReader, there are a few extra
precautions. If the stored procedure first produces a result set, and
then a message, you must first call .NextResult before you get an
exception, or, for an informational message, any InfoMessage event
handler is invoked."

Razvan|||In my case I am using SQLDataReader and sp being killed in the deadlock
is a simple select statement containing single resultset.
_GJK

Razvan Socol wrote:
> Read the following article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/error-handling-I.html#ADO.Net
> especially this part: "If you use ExecuteReader, there are a few
extra
> precautions. If the stored procedure first produces a result set, and
> then a message, you must first call .NextResult before you get an
> exception, or, for an informational message, any InfoMessage event
> handler is invoked."
> Razvan|||In my case I am using SQLDataReader and sp being killed in the deadlock
is a simple select statement containing single resultset.
_GJK

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:
> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:
> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > I am writing a vb.net application that calls a stored procedure and need
> some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the
> only
> > way I can figure it out is to use @.@.RowCount, but can't get the right
> result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
> > I am writing a vb.net application that calls a stored procedure and need some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the only
> > way I can figure it out is to use @.@.RowCount, but can't get the right result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
> > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
of
> > the last operation... I have a feeling you really want:
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > update ...
> >
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
message
> > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > I am writing a vb.net application that calls a stored procedure and
need
> > some
> > > help.
> > >
> > > I am writting the procedure to check if multiple records exists and
the
> > only
> > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > result,
> > > please help.
> > >
> > > What I have now is
> > >
> > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > @.@.Rowcount = 1)
> > > update...
> > >
> > >
> > > I tried using the following but got an error in my vb application
becuase
> > > the Procedure was returning rows
> > >
> > > select c_driver from cartons where orderid = @.orderid
> > > if @.@.Rowcount = 1
> > > update...
> > >
> > > Is there any way to use the above query without returning rows to VB?
> > >
> > > Is there a way to write an exists to query @.@.Rowcount?
> > >
> > >
> > >
> >
> >
> >|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:
> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > I tried the count() but I don't get what I need
> >
> > what I need to do is determin is if a single driver is assigned to
> multiple
> > cartons within an order. for Instance say driver #1 was assigned to Carton
> > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> these
> > cartons are in Order #1000
> >
> > Order# Carton# Driver#
> > 1000 100 1
> > 1000 101 24
> > 1000 102 24
> >
> > What I need to get to is this without the select (becuase doing it this
> way
> > returns an error in my vb.net application because the select wants to
> return
> > rows)
> >
> > select c_driver from Cartons where orderid = @.orderid
> > if @.@.RowCount = 1
> > --Only one driver exists for this order
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> >
> > if @.@.RowCount > 1
> > -- Multiple Driver exists for this Order
> >
> >
> > "Adam Machanic" wrote:
> >
> > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> of
> > > the last operation... I have a feeling you really want:
> > >
> > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > update ...
> > >
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> message
> > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > I am writing a vb.net application that calls a stored procedure and
> need
> > > some
> > > > help.
> > > >
> > > > I am writting the procedure to check if multiple records exists and
> the
> > > only
> > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > result,
> > > > please help.
> > > >
> > > > What I have now is
> > > >
> > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > @.@.Rowcount = 1)
> > > > update...
> > > >
> > > >
> > > > I tried using the following but got an error in my vb application
> becuase
> > > > the Procedure was returning rows
> > > >
> > > > select c_driver from cartons where orderid = @.orderid
> > > > if @.@.Rowcount = 1
> > > > update...
> > > >
> > > > Is there any way to use the above query without returning rows to VB?
> > > >
> > > > Is there a way to write an exists to query @.@.Rowcount?
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
> > Again, the COUNT(*) should do exactly what you need here... can you tell me
> > why this won't work for you:
> >
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > BEGIN
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> > END
> > ELSE
> > BEGIN
> > -- do something else...
> > END
> >
> >
> > ... Given that, however, I should ask why you're updating your orders table
> > with the driver from the cartons table? Why denormalize your data like
> > that?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> > news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > > I tried the count() but I don't get what I need
> > >
> > > what I need to do is determin is if a single driver is assigned to
> > multiple
> > > cartons within an order. for Instance say driver #1 was assigned to Carton
> > > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> > these
> > > cartons are in Order #1000
> > >
> > > Order# Carton# Driver#
> > > 1000 100 1
> > > 1000 101 24
> > > 1000 102 24
> > >
> > > What I need to get to is this without the select (becuase doing it this
> > way
> > > returns an error in my vb.net application because the select wants to
> > return
> > > rows)
> > >
> > > select c_driver from Cartons where orderid = @.orderid
> > > if @.@.RowCount = 1
> > > --Only one driver exists for this order
> > > Update orders set oDriver = (select distinct c_driver from cartons
> > > where orderid = @.orderid)
> > >
> > > if @.@.RowCount > 1
> > > -- Multiple Driver exists for this Order
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> > of
> > > > the last operation... I have a feeling you really want:
> > > >
> > > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > > update ...
> > > >
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> > message
> > > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > > I am writing a vb.net application that calls a stored procedure and
> > need
> > > > some
> > > > > help.
> > > > >
> > > > > I am writting the procedure to check if multiple records exists and
> > the
> > > > only
> > > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > > result,
> > > > > please help.
> > > > >
> > > > > What I have now is
> > > > >
> > > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > > @.@.Rowcount = 1)
> > > > > update...
> > > > >
> > > > >
> > > > > I tried using the following but got an error in my vb application
> > becuase
> > > > > the Procedure was returning rows
> > > > >
> > > > > select c_driver from cartons where orderid = @.orderid
> > > > > if @.@.Rowcount = 1
> > > > > update...
> > > > >
> > > > > Is there any way to use the above query without returning rows to VB?
> > > > >
> > > > > Is there a way to write an exists to query @.@.Rowcount?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Wednesday, March 7, 2012

HELP URGENT - Error 17832 after SQL 7 SP4 install and MS03-039 install

I have an application that was developed in an older version of VB (I think
4.0). The users are heavily dependent on this application (and of course,
no source code). Yesterday the users were able to connect to the SQL 7
database. Last night I ran the Service Pack 4 on the SQL 7 machine and
today the users are unable to connect. They just get an error message
(Invalid SQL Server Login). When I look in the error log on the server I
see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
login packet(s) sent. Connection closed.
Also, the latest security patch from Microsoft (MS03-039) was installed on
the server that houses the database. It is running Windows 2000.
Thanks
Any assistance would be greatly appreciated.Connie,
The only reference to 17832 that I could find was in this article:
INF: SQL Communication Errors 17832, 17824, 1608, 232, and 109 (KB Article
109787)
http://tinyurl.com/nfm8
17832 Unable to read login packet(s). [NT only]
This can happen if a client starts to connect, but never successfully
completes the attempt because of a client operating system or application
failure. It could also be caused by the network failing between the time a
connection attempt is initiated, and when it completes.
Of course, several changes were made, all at once, which is always risky.
(We always apply upgrades to a DEV or QA server before moving on to
production, if at all possible.)
This may be due to either SP4 or MS03-039. I see that MS03-039 patches RPC.
Although your application should (ideally) not need that facility I have no
way of knowing.
A more likely possibility: Did you us SP4 to update client software as
well? Some SPs have client and server components. Usually this is not a
problem, but sometimes it has caused grief.
Russell Fields
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||You might want to check if the authentication mode got changed. Some of the
SQL service packs try to force Windows authentication or give the sa a
password other than blank. Go to SQL Server properties in SQL enterprise
manager, and check the security tab. See if you are in mixed mode or not.
From a DOS prompt on the server, you might also want to try
osql -U<user> -P<password> -S<servername>
for a user who cannot log on. That might give you a better error message.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||how is this application connecting to the SQL Server, an ODBC like? is that
pointed ata Named Pipe connection, if it is try switching it to a TCP/IP
like,
if not Look at your SQL Client Network Utility, if the default is Named
Pipes or you have a Named Pipes Alias try switching it to TCP/IP.
MS0-039 is has a lot of cross over with MS03-026, both applied a lot of
security to connectivity through Named Pipes.
HtH

Help Tracking down failed SQL login

On my SQL 2000 Server and in my application event log I
am getting numerous event id of 17055, which is a login
failure for a particular account on my SQL server. The
problem I am having is that I am trying to find out where
this account is logging in from. The description of the
event reads: 18456 :Login failed for user 'user'. Is
there a logging mode or tool I can use to find out where
this login is being generated from so I can fix this
failed login attempt?
Thanks,
Alex
Alex,
Check the SQL Profiler tool that comes with SQL Server so you already have
it.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alex Mook" <anonymous@.discussions.microsoft.com> wrote in message
news:aec401c436af$405d75a0$a001280a@.phx.gbl...
> On my SQL 2000 Server and in my application event log I
> am getting numerous event id of 17055, which is a login
> failure for a particular account on my SQL server. The
> problem I am having is that I am trying to find out where
> this account is logging in from. The description of the
> event reads: 18456 :Login failed for user 'user'. Is
> there a logging mode or tool I can use to find out where
> this login is being generated from so I can fix this
> failed login attempt?
> Thanks,
> Alex

Help Tracking down failed SQL login

On my SQL 2000 Server and in my application event log I
am getting numerous event id of 17055, which is a login
failure for a particular account on my SQL server. The
problem I am having is that I am trying to find out where
this account is logging in from. The description of the
event reads: 18456 :Login failed for user 'user'. Is
there a logging mode or tool I can use to find out where
this login is being generated from so I can fix this
failed login attempt?
Thanks,
Alex
Alex,
have a look at profiler for this - you can to pick up the "Host Name" this
way using the AuditLogin... events.
Regards,
Paul Ibison
|||Try making a network capture while the problem is occuring. This will give
you the mac address and IP address of the client.
Microsoft Network Monitor is included in Windows 2000 Server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Help Tracking down failed SQL login

On my SQL 2000 Server and in my application event log I
am getting numerous event id of 17055, which is a login
failure for a particular account on my SQL server. The
problem I am having is that I am trying to find out where
this account is logging in from. The description of the
event reads: 18456 :Login failed for user 'user'. Is
there a logging mode or tool I can use to find out where
this login is being generated from so I can fix this
failed login attempt?
Thanks,
AlexAlex,
have a look at profiler for this - you can to pick up the "Host Name" this
way using the AuditLogin... events.
Regards,
Paul Ibison|||Try making a network capture while the problem is occuring. This will give
you the mac address and IP address of the client.
Microsoft Network Monitor is included in Windows 2000 Server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Help to solve this problem!

I'm a beginer!

I host my web on a hosting service provider. But when I access an error occur:

Server Error in '/' Application.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.DetailsView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.DetailsView.EnsureDataBound() +181 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

give this a shot:

In the SQL Management Studio, RightClick the SQL server instance, Choose
Properties, and under Select a Page list, click Connections. Now you should
see a checkbox labelled "Allow Remote Connections to This Server". Make sure
it is checked.

hope this helps -- jp

Sunday, February 26, 2012

HELP sql server 2005 express connection hangs up

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank

Friday, February 24, 2012

Help setting datasource programatiically...

Hi gang...
I have sucessfully created a deployment application for my reports to be
deployed via web services to our customers. However, the only thing I am not
able to set correctly is the shared datasource for the report itself. Below
is a snippet of my code:
/Test/Test is my shared datasource. The curItem object is a catalog item
pointing to a report. The code below does not work - can anyone help me out?
Dim reference As New DataSourceReference
Dim dataSources(0) As DataSource
Dim ds As New DataSource
reference.Reference = "/Test/Test"
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = "/Test/Test"
dataSources(0) = ds
rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
Console.WriteLine("New reference set for the report.")
=-ChrisTry
ds.Name = "Test"
instead of
ds.Name = "/Test/Test"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Conner" <someone@.someplace.com> wrote in message
news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi gang...
> I have sucessfully created a deployment application for my reports to be
> deployed via web services to our customers. However, the only thing I am
> not able to set correctly is the shared datasource for the report itself.
> Below is a snippet of my code:
> /Test/Test is my shared datasource. The curItem object is a catalog item
> pointing to a report. The code below does not work - can anyone help me
> out?
> Dim reference As New DataSourceReference
> Dim dataSources(0) As DataSource
> Dim ds As New DataSource
> reference.Reference = "/Test/Test"
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = "/Test/Test"
> dataSources(0) = ds
> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
> Console.WriteLine("New reference set for the report.")
> =-Chris
>|||Lev,
Thanks for the reply. I changed the ds.Name = "Test" and this is the error
I get back:
<Message msrs:ErrorCode="rsDataSourceNotFound"
msrs:HelpLink="http://g
o.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diag
nostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsDataSourceNotFound&
amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00"
x
mlns:msrs="The">http://www.microsoft.com/sql/reportingservices">The data source
'Test' cannot be found in this report.</Message>
</MoreInformation>
<Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />
What is weird is that I *know* that the datasource 'Test' is not in the
report -> as when I upload a report definition, it will not have it - in
this case I am trying to set the datasource connection information for the
report to point to the shared datasource Test...
You know what? Looking at the documentation - It says that the method
SetReportDataSources "Sets the properties that are associated with the data
sources of a specified report." So the method I am using is not going to
work, since my report does not have a report datasource assoicated with it
yet. The documentation says in the remarks section:
Remarks
The report server throws an exception if the SetReportDataSources method is
used to set the data source properties of a linked report. If a data source
that is passed in the DataSources parameter is not associated with the given
report, a SOAP exception is thrown with the error code rsDataSourceNotFound
Which is exactly what I am getting...
I am trying to set my report to use a shared datasource.
The sample in the docuementation for SetReportDataSources does not work.
Do you have any other suggestions Lev?
Do I need to set a report property? This is crazy - I have everything elese
done but setting the datasource information for a report to use a shared
datasource. The report doesn't have any datasources associated with it
because they are invalid when I copied the report from one server to the
other. I can manually set the shared datasource for the report - but it
would be nice to do it via code since I have over 200 reports.
=-Chris
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Try
> ds.Name = "Test"
> instead of
> ds.Name = "/Test/Test"
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report itself.
>> Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>|||Lev - I figured it out. I have posted the solution above to a newer post of
mine asking if anyone has figured it out. Thanks for trying to help.
=-Chris
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Try
> ds.Name = "Test"
> instead of
> ds.Name = "/Test/Test"
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report itself.
>> Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>|||Chris,
I'll check that sample. It could be doc bug.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Conner" <someone@.someplace.com> wrote in message
news:%23WfKDmFFFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Lev - I figured it out. I have posted the solution above to a newer post
> of mine asking if anyone has figured it out. Thanks for trying to help.
> =-Chris
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
>> Try
>> ds.Name = "Test"
>> instead of
>> ds.Name = "/Test/Test"
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Christopher Conner" <someone@.someplace.com> wrote in message
>> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report
>> itself. Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>>
>

Thursday, March 29, 2012

Help with Custom Security Extension and Application Pool Identity

Help with creating xml shema

Tuesday, March 27, 2012

Help with Collation - Decoding the Compatibility Designator

Wednesday, March 21, 2012

Help with a return value stored procedure that will be used in a calculation

Monday, March 12, 2012

Help with 1205 Not raised

Help with @@Rowcount

Help with @@Rowcount

Help with @@Rowcount

Wednesday, March 7, 2012

HELP URGENT - Error 17832 after SQL 7 SP4 install and MS03-039 install

Help Tracking down failed SQL login

Help Tracking down failed SQL login

Help Tracking down failed SQL login

Help to solve this problem!

Server Error in '/' Application.

Sunday, February 26, 2012

HELP sql server 2005 express connection hangs up

Friday, February 24, 2012

Help setting datasource programatiically...

Restore Database

Blog Archive

About Me