OLAP Connection error

Below code at web.config file, when i change Data Source to other pc name....an error occur 'The data provider didn't supply any further error information'...
let said change the datasource 'localhost' to 'webserver' error will occur...so i cannot run the program at my pc and link the olap database at server...

<add key="OLAPConnectionString" value="Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=Foodmart 2000;" />The other PC that you are trying to connect should have the same users and user rights as the first one.

In addition to that, the Analysis Server on your second PC should know about these users as well.

I found that security with Analysis Server is very tight.

Good Luck

OLAP Connection Error

Hi,
Background :
Am trying to connect to the OLAP Cubes from a Web Service.
The Web Service resides on my local machine, while the Analysis Server resid
es on a different domain.
Users have been created on the Analysis Server, and roles have been defined
for the cube am trying to access.
The processModel is configured with proper user id.
Problem :
When I do that I get an error "Undefined Error".
To my knowledge I have all the access to the Analysis Server, but still am n
ot able to connect to cube.
Could someone please help me resolve this as its very crucial and is hamperi
ng our to a great extent.
Thanks,
SrinivasThe user is created on the Analysis Server, and a role too has been defined
for the user, giving access to the cube.
For example :
Let us suppose my network user id is "ABC". Then a user called "ABC" is crea
ted on the Analysis server, and has been assigned a role.
Srinivas|||microsoft.public.sqlserver.olap is probably a better newsgroup for this, but
my initial guess is that your problem revolves around the statement below
"different domain". AS only supports Windows authentication in direct
client-server mode. You have a few options. First, you could use the old NT
matching-username password trick. If you have *local accounts* on both
systems with the same username password, then you will be able to connect
even if the two machines are in untrusted domains. You will have
administrative issues keeping the two passwords in-sync, but the connection
will go through OK. Second, if you are running on Enterprise Edition, you
can use http access. This will require you to run IIS on the AS server (see
BOL on how to configure it and look at the white paper
http://msdn.microsoft.com/library/e...ql_datapump.asp for
more technical details.
Hope that helps.
Dave Wickert [MS]
dwickert@.online.microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Srinivas" <svinjamoor@.worldbank.org> wrote in message
news:19E0A695-14C5-4E9C-8BB8-FB7C26A310BC@.microsoft.com...

quote:

---

> Hi,
> Background :
> Am trying to connect to the OLAP Cubes from a Web Service.
> The Web Service resides on my local machine, while the Analysis Server

---

resides on a different domain.

quote:

---

> Users have been created on the Analysis Server, and roles have been

---

defined for the cube am trying to access.

quote:

---

> The processModel is configured with proper user id.
> Problem :
> When I do that I get an error "Undefined Error".
> To my knowledge I have all the access to the Analysis Server, but still am

---

not able to connect to cube.

quote:

---

> Could someone please help me resolve this as its very crucial and is

---

hampering our to a great extent.

quote:

---

> Thanks,
> Srinivas

---

OLAP Analysis Services: Errors in Encryption Library

Hi!

I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:

"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."

Thanks in advance,

--Dimitris Doukas

You might have changed the account for your Analysis Server service.

To solve this problem you will have to stop Analysis Server. Remove all the files from the Data folder ( located under installation folder) and then start Analysis Server again.

Create a backup for your existing databases before doing that in case you cannot re-process them.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

It worked. Thanks a lot.

--Dimitris

OLAP administrators group.?

hi all..

i have a problem about sql server analysis. i try to connect sql server for OLAP but i can't. sql server analysis give this error "unable to connect to the registry on the server or you are not members of OLAP administrator groups on this server" i use Windows Xp Pro and Sql Server Developer Edition. how can i solve this problem..

Please provide more details? Same machine for Analysis Manager and Analysis Services or different machines? Service Pack 3 or greater on both machines if different machines?

OLAP administrators group.?

hi all..

i have a problem about sql server analysis. i try to connect sql server for OLAP but i can't. sql server analysis give this error "unable to connect to the registry on the server or you are not members of OLAP administrator groups on this server" i use Windows Xp Pro and Sql Server Developer Edition. how can i solve this problem..

Please provide more details? Same machine for Analysis Manager and Analysis Services or different machines? Service Pack 3 or greater on both machines if different machines?

Olap

Hi...

Please, someone knows what is this message:

OLAP Services Processing Task error '8004ffff'
Microsoft SQL Server OLAP Services must be installed on this computer and the user must be an OLAP Administrator in order to create an OLAP processing task.

The OLAP Services are OK, and the User is a OLAP Admin....

Thanks

David.HOw about the SP level on the SQL and OS?
Try to RESTART the OLAP services and see.|||Originally posted by Satya
HOw about the SP level on the SQL and OS?
Try to RESTART the OLAP services and see.

I tried, but don't work. The problem persists.

(Sorry about my english, but I'm not American)|||May refer this OLAP resources (http://groups.msn.com/MicrosoftOLAPServicesUsersCommunity/_homepage.msnw?pgmarket=en-us) for any help.

If nothing found then the only option is to reinstall the OLAP services as specified.

Office Web Component Pivottable functionality

I use the Office Web Component 2003 Pivottable in SharePoint but there are some bad behaviours.

- slow loading

- error messages like: An Error 0x0040200 occured

We use Reporting Services in most cases, but the positive aspects like Dynamic Dimensions and measure usage or Dynamic filtering in Hierarchies are to import to lose.

The Office Web Components are not developed anymore. In SharePoint Services 3.0 they can function, but it is not 100% the correct way. Does someone know the best way to use Pivottables in SharePoint Services 3.0?

Thnx

Hessel Appers

Hi,

I am using OWC 11 and it works perfectly for me,

although I am not using sharepoint, but have my own Asp.Net portal.

I am using OWC pivottables and pivotcharts.

OWC should be as fast as your BI studio/visual studio 2005 while loading/displaying or browsing a cube. (I believe)

because BI studio or Visual studio uses OWC.

If the performance is slower in both the cases, then it might be that you need to improve performance of your cube.

if the loading time is slower, try to display a filtered dataset or a higher level dataset,

the users can then drill down to the level required.

When using OWC, it is always better to show only the most required data and also the most minimum set.

This is because OWC is browser based, it would make IE take up a lot of memory,

and thus make IE slower, eventually your OWC component would also become slow with more data.

As an alternative to using OWC pivottables,

is to use the newer excel services (Microsoft Office 2005).

It jells best with sharepoint and is infact built to support sharepoint.

another alternative is to try other third party and free OLAP controls.

one free control that I can suggest is the "CellSetGrid".

The source code is also avaliable,

so you can look through it and make changes if required.

also the simple usage is pretty easy.

search for 'CellSetGrid' in google.

please do let me know,

how this comes, what you choose and what gave you best performance and features.

Regards

of any concern??

This is a reoccurring error in one of our Domain Controllers event logs…

Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 7/24/2007
Time: 6:23:05 AM
User: N/A
Computer: DC2
Description:
There are multiple accounts with name MSSQLSvc/dbsrvr-02.olntv.local:1433 of type DS_SERVICE_PRINCIPAL_NAME.

the Server dbsrvr-02 (sql server 2000 standard edition sp3a) has a default instance and it looksl ike 2 named instances installed that we are not using. Ive searched this error and cannot find a thing.. should this concern me? anyone ?Fyi http://www.eventid.net/display.asp?eventid=11&eventno=569&source=KDC&phase=1 and also if you areusing active directory, i t means there are duplicate entries in Active Directory for this service principal name. A directory administrator can use ldp.exe to search for the duplicates and either ldp.exe again or adsiedit.msc to delete the duplicates.

ODS

In the Event Log I'm received next message:
Source: MSSQLServer
Category: ODS
ID: 17052
Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
connection 'servername' is already in use.
But my SQL Server are working normally.
What's a problem?
Michael S. Androsov
EXPOCENTRHi,
Another application or service running in the server would have taken
control of the port before SQL Server starts.
Have a look into the below article.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;293107
Thanks
Hari
MCDBA
"Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
news:f99f3e13.0403030156.77a793c5@.posting.google.com...
> In the Event Log I'm received next message:
> Source: MSSQLServer
> Category: ODS
> ID: 17052
> Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
> connection 'servername' is already in use.
> But my SQL Server are working normally.
> What's a problem?
> Michael S. Androsov
> EXPOCENTR|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<OkydjcQAEHA.712@.tk2msftngp13.phx.gbl>...
> Hi,
> Another application or service running in the server would have taken
> control of the port before SQL Server starts.
> Have a look into the below article.
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;293107
> Thanks
> Hari
> MCDBA
> "Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
> news:f99f3e13.0403030156.77a793c5@.posting.google.com...
> > In the Event Log I'm received next message:
> > Source: MSSQLServer
> > Category: ODS
> > ID: 17052
> > Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
> > connection 'servername' is already in use.
> >
> > But my SQL Server are working normally.
> >
> > What's a problem?
> >
> > Michael S. Androsov
> > EXPOCENTR
I have't other applications working with 1433 or 1118 ports. If this
applications exist my SQL Server will not working. But now the server
is working.
Michael S. Androsov
EXPOCENTR

Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

I had this line in a very, very large stored procedure
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1
Restored:
and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
Incorrect Syntax near â'Râ'.(-2147217900)
Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
So I changed the code to this and it works now:
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
Begin
Update #PY_Tbl Set After1987=1
End
Restored:
Any ideas why?Steve,
SQL Server syntax is in the process of changing and becoming more ANSI.
That means that occasionally an assumption that 2000 was willing to make,
2005 is unwilling to make. The new approved syntax for ensuring that a
command is terminated is to close with ;
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1;
Restored:
FWIW,
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>I had this line in a very, very large stored procedure
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1
> Restored:
> and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> Incorrect Syntax near "R".(-2147217900)
> Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> So I changed the code to this and it works now:
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> Begin
> Update #PY_Tbl Set After1987=1
> End
> Restored:
> Any ideas why?
>|||That look promising...
But unfortunately the ";" character did not fix the syntax error.
And keep in mind that this is during run-time of the SPROC and not compiling
into the DB.
"Russell Fields" wrote:
> Steve,
> SQL Server syntax is in the process of changing and becoming more ANSI.
> That means that occasionally an assumption that 2000 was willing to make,
> 2005 is unwilling to make. The new approved syntax for ensuring that a
> command is terminated is to close with ;
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> Restored:
> FWIW,
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >I had this line in a very, very large stored procedure
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >
> > Restored:
> >
> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >
> > Incorrect Syntax near "R".(-2147217900)
> >
> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> >
> > So I changed the code to this and it works now:
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0)
> > Begin
> > Update #PY_Tbl Set After1987=1
> > End
> >
> > Restored:
> >
> > Any ideas why?
> >
>
>|||Can you provide a repro? Your code returned an *expected* error (no such table...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||I do not understand what you want me to supply? This error is easily
duplicated on the production server.
"Tibor Karaszi" wrote:
> Can you provide a repro? Your code returned an *expected* error (no such table...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>|||Steve,
OK, I missed the boat. Which probably means that there is something going
on earlier that is at the root of the problem. My repro (comment out
whichever inserts you do not want to test) had no error on 2005, with or
without the semi-colon.
create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
insert into #Py_Tbl values(55, 1986,7,2)
insert into #Py_Tbl values(66, 1985,7,2)
insert into #Py_Tbl values(77, 1986,7,0)
insert into #Py_Tbl values(77, 1987,0,0)
insert into #Py_Tbl values(77, 1987,0,2)
If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
update #PY_Tbl Set After1987=1
Restored:
SELECT * from #PY_Tbl
drop table #Py_Tbl
What next?
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not
> compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near
>> > "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||The SPROC is over 1000 lines. If calculates a persons pension.
The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
I'm feeling it's a true parsing bug in SQL - but I've got no way to
reproduce it in a smaller fashion.
"Russell Fields" wrote:
> Steve,
> OK, I missed the boat. Which probably means that there is something going
> on earlier that is at the root of the problem. My repro (comment out
> whichever inserts you do not want to test) had no error on 2005, with or
> without the semi-colon.
> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
> insert into #Py_Tbl values(55, 1986,7,2)
> insert into #Py_Tbl values(66, 1985,7,2)
> insert into #Py_Tbl values(77, 1986,7,0)
> insert into #Py_Tbl values(77, 1987,0,0)
> insert into #Py_Tbl values(77, 1987,0,2)
> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> update #PY_Tbl Set After1987=1
> Restored:
> SELECT * from #PY_Tbl
> drop table #Py_Tbl
> What next?
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> > That look promising...
> >
> > But unfortunately the ";" character did not fix the syntax error.
> >
> > And keep in mind that this is during run-time of the SPROC and not
> > compiling
> > into the DB.
> >
> > "Russell Fields" wrote:
> >
> >> Steve,
> >>
> >> SQL Server syntax is in the process of changing and becoming more ANSI.
> >> That means that occasionally an assumption that 2000 was willing to make,
> >> 2005 is unwilling to make. The new approved syntax for ensuring that a
> >> command is terminated is to close with ;
> >>
> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> and
> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> >>
> >> Restored:
> >>
> >> FWIW,
> >> RLF
> >>
> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >> >I had this line in a very, very large stored procedure
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >> >
> >> > Restored:
> >> >
> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >> >
> >> > Incorrect Syntax near "R".(-2147217900)
> >> >
> >> > Changing the label RESTORED: to XYZ: make the error change to: ...near
> >> > "X"
> >> >
> >> > So I changed the code to this and it works now:
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0)
> >> > Begin
> >> > Update #PY_Tbl Set After1987=1
> >> > End
> >> >
> >> > Restored:
> >> >
> >> > Any ideas why?
> >> >
> >>
> >>
> >>
>
>|||Steve,
I certainly cannot argue with you about that possibility. And I understand
that it may not be worth pursuing any further.
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6BA773B3-EF55-4B52-A379-B4961D856905@.microsoft.com...
> The SPROC is over 1000 lines. If calculates a persons pension.
> The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
> I'm feeling it's a true parsing bug in SQL - but I've got no way to
> reproduce it in a smaller fashion.
> "Russell Fields" wrote:
>> Steve,
>> OK, I missed the boat. Which probably means that there is something
>> going
>> on earlier that is at the root of the problem. My repro (comment out
>> whichever inserts you do not want to test) had no error on 2005, with or
>> without the semi-colon.
>> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
>> insert into #Py_Tbl values(55, 1986,7,2)
>> insert into #Py_Tbl values(66, 1985,7,2)
>> insert into #Py_Tbl values(77, 1986,7,0)
>> insert into #Py_Tbl values(77, 1987,0,0)
>> insert into #Py_Tbl values(77, 1987,0,2)
>> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0)
>> update #PY_Tbl Set After1987=1
>> Restored:
>> SELECT * from #PY_Tbl
>> drop table #Py_Tbl
>> What next?
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
>> > That look promising...
>> >
>> > But unfortunately the ";" character did not fix the syntax error.
>> >
>> > And keep in mind that this is during run-time of the SPROC and not
>> > compiling
>> > into the DB.
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Steve,
>> >>
>> >> SQL Server syntax is in the process of changing and becoming more
>> >> ANSI.
>> >> That means that occasionally an assumption that 2000 was willing to
>> >> make,
>> >> 2005 is unwilling to make. The new approved syntax for ensuring that
>> >> a
>> >> command is terminated is to close with ;
>> >>
>> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> >> and
>> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> >>
>> >> Restored:
>> >>
>> >> FWIW,
>> >> RLF
>> >>
>> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >> >I had this line in a very, very large stored procedure
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >> >
>> >> > Restored:
>> >> >
>> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this
>> >> > error:
>> >> >
>> >> > Incorrect Syntax near "R".(-2147217900)
>> >> >
>> >> > Changing the label RESTORED: to XYZ: make the error change to:
>> >> > ...near
>> >> > "X"
>> >> >
>> >> > So I changed the code to this and it works now:
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0)
>> >> > Begin
>> >> > Update #PY_Tbl Set After1987=1
>> >> > End
>> >> >
>> >> > Restored:
>> >> >
>> >> > Any ideas why?
>> >> >
>> >>
>> >>
>> >>
>>|||>I do not understand what you want me to supply?
Something we could execute that gives us the same error message. I see from your discussion with
Russell that it isn't that easy, though. A parsing bug seems probable, and I guess that if you want
to pursue this you would have to open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:FCE7ED20-94C8-4008-8484-AF93D1953941@.microsoft.com...
>I do not understand what you want me to supply? This error is easily
> duplicated on the production server.
> "Tibor Karaszi" wrote:
>> Can you provide a repro? Your code returned an *expected* error (no such table...).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>|||On Tue, 21 Aug 2007 13:46:03 -0700, Steve Z
<SteveZ@.discussions.microsoft.com> wrote:
>I had this line in a very, very large stored procedure
>If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>PenHrs<>0) Update #PY_Tbl Set After1987=1
>Restored:
What is the *next* statement?
J.

Odd syntax error with shape queries SQL2000/Win2003

Hi,

I am running SQL Server 2000 SP3 on Windows Server 2003 and since recently
have a strange problem executing shape queries from COM+ components using
ADO.

Until 4 days ago, they worked, then from one moment to the next (I must have
changed something, but I have no clue what other than restoring a 1.2 GB
database) they started failing with this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation

I have no problem executing non-shape queries, it is just the shape queries
that fail.

Any clues what may have gone wrong? Or how I can fix it?

Cheers,
Rsa Myh> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Syntax error or access violation

And apparently this had nothing to do with Win2003 or SQL Server or any
software at all... Just with a mistake in a setting of my own software...
The datasource string for the shape data source was invalid....

I'm hanging my head in shame..

Odd Primary Key Error

Dear All,
I have been getting Primary key violation errors, however
I am a bit confused why. Here is the reason
We have a table with the following structure with the ID
field as the primary key.
CREATE TABLE [dbo].[tblTest] (
[ID] [int] IDENTITY (8, 20) NOT NULL , ...
and
ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
We then use a store procedure to insert items into the
table. The store procedure does not access the ID field,
but it does return it after insert.
Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
commands I have found the next key to be generated is
correct.
However we are starting to get intermediate primary key
violation errors.
Any pointers please.
JJulie
Did you try in the bottom of the sp adding return @.@.identity and the to
carry out on the client side?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...

quote:

---

> Dear All,
> I have been getting Primary key violation errors, however
> I am a bit confused why. Here is the reason
> We have a table with the following structure with the ID
> field as the primary key.
> CREATE TABLE [dbo].[tblTest] (
> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> and
> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> We then use a store procedure to insert items into the
> table. The store procedure does not access the ID field,
> but it does return it after insert.
> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> commands I have found the next key to be generated is
> correct.
> However we are starting to get intermediate primary key
> violation errors.
> Any pointers please.
> J

---

|||Almost at the end of every insert store procedure we have
a 'RETURN scope_identity()' command.
This is filtered back to the VB app though ado.
J

quote:

---

>--Original Message--
>Julie
> Did you try in the bottom of the sp adding return

---

@.@.identity and the to

quote:

---

>carry out on the client side?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
however[QUOTE]
>
>.
>

---

|||Julie
Look , IDENTITY property does not guarantee sequence of insertion. There may
be gaps.
Can you provide sample data + code of your sp to reproduce your problem?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...[QUOTE]
> Almost at the end of every insert store procedure we have
> a 'RETURN scope_identity()' command.
> This is filtered back to the VB app though ado.
> J
>
> @.@.identity and the to
> message
> however|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J

quote:

---

>--Original Message--
>Julie
>Look , IDENTITY property does not guarantee sequence of

---

insertion. There may

quote:

---

>be gaps.
>Can you provide sample data + code of your sp to

---

reproduce your problem?

quote:

---

>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
have[QUOTE]
the ID[QUOTE]
the[QUOTE]
field,[QUOTE]
key[QUOTE]
>
>.
>

---

Odd Primary Key Error

Dear All,
I have been getting Primary key violation errors, however
I am a bit confused why. Here is the reason
We have a table with the following structure with the ID
field as the primary key.
CREATE TABLE [dbo].[tblTest] (
[ID] [int] IDENTITY (8, 20) NOT NULL , ...
and
ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
We then use a store procedure to insert items into the
table. The store procedure does not access the ID field,
but it does return it after insert.
Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
commands I have found the next key to be generated is
correct.
However we are starting to get intermediate primary key
violation errors.
Any pointers please.
JJulie
Did you try in the bottom of the sp adding return @.@.identity and the to
carry out on the client side?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
> Dear All,
> I have been getting Primary key violation errors, however
> I am a bit confused why. Here is the reason
> We have a table with the following structure with the ID
> field as the primary key.
> CREATE TABLE [dbo].[tblTest] (
> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> and
> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> We then use a store procedure to insert items into the
> table. The store procedure does not access the ID field,
> but it does return it after insert.
> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> commands I have found the next key to be generated is
> correct.
> However we are starting to get intermediate primary key
> violation errors.
> Any pointers please.
> J|||Almost at the end of every insert store procedure we have
a 'RETURN scope_identity()' command.
This is filtered back to the VB app though ado.
J
>--Original Message--
>Julie
> Did you try in the bottom of the sp adding return
@.@.identity and the to
>carry out on the client side?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
>> Dear All,
>> I have been getting Primary key violation errors,
however
>> I am a bit confused why. Here is the reason
>> We have a table with the following structure with the ID
>> field as the primary key.
>> CREATE TABLE [dbo].[tblTest] (
>> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
>> and
>> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
>> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
>> (
>> [ID]
>> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> We then use a store procedure to insert items into the
>> table. The store procedure does not access the ID field,
>> but it does return it after insert.
>> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
>> commands I have found the next key to be generated is
>> correct.
>> However we are starting to get intermediate primary key
>> violation errors.
>> Any pointers please.
>> J
>
>.
>|||Julie
Look , IDENTITY property does not guarantee sequence of insertion. There may
be gaps.
Can you provide sample data + code of your sp to reproduce your problem?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
> Almost at the end of every insert store procedure we have
> a 'RETURN scope_identity()' command.
> This is filtered back to the VB app though ado.
> J
>
> >--Original Message--
> >Julie
> > Did you try in the bottom of the sp adding return
> @.@.identity and the to
> >carry out on the client side?
> >
> >
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
> >> Dear All,
> >> I have been getting Primary key violation errors,
> however
> >> I am a bit confused why. Here is the reason
> >>
> >> We have a table with the following structure with the ID
> >> field as the primary key.
> >>
> >> CREATE TABLE [dbo].[tblTest] (
> >> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> >>
> >> and
> >>
> >> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> >> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> >> (
> >> [ID]
> >> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> >>
> >> We then use a store procedure to insert items into the
> >> table. The store procedure does not access the ID field,
> >> but it does return it after insert.
> >>
> >> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> >> commands I have found the next key to be generated is
> >> correct.
> >>
> >> However we are starting to get intermediate primary key
> >> violation errors.
> >>
> >> Any pointers please.
> >>
> >> J
> >
> >
> >.
> >|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J
>--Original Message--
>Julie
>Look , IDENTITY property does not guarantee sequence of
insertion. There may
>be gaps.
>Can you provide sample data + code of your sp to
reproduce your problem?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
>> Almost at the end of every insert store procedure we
have
>> a 'RETURN scope_identity()' command.
>> This is filtered back to the VB app though ado.
>> J
>>
>> >--Original Message--
>> >Julie
>> > Did you try in the bottom of the sp adding return
>> @.@.identity and the to
>> >carry out on the client side?
>> >
>> >
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
>> >> Dear All,
>> >> I have been getting Primary key violation errors,
>> however
>> >> I am a bit confused why. Here is the reason
>> >>
>> >> We have a table with the following structure with
the ID
>> >> field as the primary key.
>> >>
>> >> CREATE TABLE [dbo].[tblTest] (
>> >> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
>> >>
>> >> and
>> >>
>> >> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ID]
>> >> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> >>
>> >> We then use a store procedure to insert items into
the
>> >> table. The store procedure does not access the ID
field,
>> >> but it does return it after insert.
>> >>
>> >> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
>> >> commands I have found the next key to be generated is
>> >> correct.
>> >>
>> >> However we are starting to get intermediate primary
key
>> >> violation errors.
>> >>
>> >> Any pointers please.
>> >>
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>sql

Odd error with Reporting Services and Oracle

I've got a fairly simple report that hits oracle with two parameters set as strings.

When I run the query in the designer, I get

Error Source: System.Data.OracleClient
Error Message: ORA-01858: a non-numeric character was found where a numeric was expected

However, when I run the report in preview everything just works. Same inputs on the parameters.

Any ideas?

What types are your parameters and what is the SQL you are using in your DataSet? I've seen this kind of error passing a parameter that is declared as a string in the report to an oracle parameter that is compared against a Date type in Oracle.

Mike

|||Sorery I meant to post a followup to this. The solution is that SQL RS 2k5 takes a string that happens to be a date and makes it a date without telling.

When it goes into oracle as this "Date" it will go in in the default SQL date format. So you need to match the format mask in the oracle TO_Date function call to the 3 letter month sql format.

Interesting, RS 2K did not do this.

Odd Error Upon SqlDataReader.Read() "Invalid length parameter passed to the substring func

An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error:
"Invalid length parameter passed to the substring function."

Here's the code in question:

orderConnection.Open()
orderReader = orderCommand.ExecuteReader()
setControls(orderReader)
...
Private Sub setControls(ByVal dr As SqlDataReader)
 If (dr.Read()) Then '<--*******problem line*******

The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?Can you post the Sql the reader is running as well as the relevant fields from the record that is crashing?|||It turns out that the stored procedure the DataReader was using expected names to be in first-name, space, last-name format and the record in question had only a single name (my company isn't normalizing their databases for some reason).

As a general question, though, do DataReaders not actually call a stored procedure until .Read() is called? I mean, I would have expected things to error out at the call to ExecuteReader(). I know that a DataReader forges a forward-only direct connection to the database, but I'm confused as to the underlying mechanics of the function calls.|||I'm not at dba but I think that the ExecuteReader creates a cursor and points it before the first record. When you call Read() it fetches the next record and at that time evaluates the calculated fields such as the one using the substring function.

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd @@error number

I have a few stored procedures that returns @.@.error. If I understand correctly, @.@.error should be set to 0 if there is no error. However, my procedures are returning 1 and 2 yet working just fine as far as I can tell. There is not exception popping up in SQL Profiler either.
What does this mean, and is it something I should be concerned about?My guess is that there is an error in your ADO.NET code. How areyou returning the @.@.ERROR to your calling program, and how is thecalling program picking it up? Can you show us the relevantpieces of your code?
|||

OK. Here are the C# function for creating the parameters from the form data and calling the procedure. This one consistantly returns '1' when, apparently, it's working fine.

void createInvoice()
{

myConnection = (SqlConnection)Session["connection"];

SqlCommand cmdUpdateInvoice;

cmdUpdateInvoice = new SqlCommand("usp_updateinvoice", myConnection);
cmdUpdateInvoice.CommandType = CommandType.StoredProcedure;

SqlParameter invoicedate = cmdUpdateInvoice.Parameters.Add("@.InvoiceDate", SqlDbType.SmallDateTime);
invoicedate.Value = DateTime.Parse(txtInvoiceDate.Text.Trim());

SqlParameter odate = cmdUpdateInvoice.Parameters.Add("@.OldDate", SqlDbType.SmallDateTime);

if (olddate != "")
odate.Value = DateTime.Parse(olddate);
else
odate.Value = DateTime.Parse("01/01/1900");

olddate = txtInvoiceDate.Text; //reset the olddate value

SqlParameter cycle = cmdUpdateInvoice.Parameters.Add("@.Cycle", SqlDbType.Int);
cycle.Value = rblCycles.SelectedIndex + 1;

SqlParameter accountnum = cmdUpdateInvoice.Parameters.Add("@.AccountNum", SqlDbType.VarChar, 50);
accountnum.Value = txtAccountNumber.Text.Trim();

SqlParameter carrierid = cmdUpdateInvoice.Parameters.Add("@.CarrierID", SqlDbType.Int);
carrierid.Value = lstCarriers.SelectedValue;

SqlParameter pastdue = cmdUpdateInvoice.Parameters.Add("@.PastDue", SqlDbType.Money);
pastdue.Value = Convert.ToDouble(txtPastDue.Text);

SqlParameter late = cmdUpdateInvoice.Parameters.Add("@.LateFee", SqlDbType.Money);
late.Value = Convert.ToDouble(txtLateFee.Text);

SqlParameter credit = cmdUpdateInvoice.Parameters.Add("@.Credit", SqlDbType.Money);
credit.Value = Convert.ToDouble(txtCredit.Text);

SqlParameter totaldue = cmdUpdateInvoice.Parameters.Add("@.TotalDue", SqlDbType.Money);
totaldue.Value = Convert.ToDouble(txtTotalDue.Text);

SqlParameter comments = cmdUpdateInvoice.Parameters.Add("@.Comments", SqlDbType.VarChar, 255);
comments.Value = txtComments.Text.Trim();

SqlParameter notes = cmdUpdateInvoice.Parameters.Add("@.Notes", SqlDbType.VarChar, 255);
notes.Value = txtNotes.Text.Trim();

SqlParameter ispaid = cmdUpdateInvoice.Parameters.Add("@.isPaid", SqlDbType.Bit);
ispaid.Value = 0;

SqlParameter visible = cmdUpdateInvoice.Parameters.Add("@.Visible", SqlDbType.Bit);
visible.Value = cbxVisible.Checked;

SqlParameter charges = cmdUpdateInvoice.Parameters.Add("@.CurrentCharges", SqlDbType.Money);
charges.Value = Convert.ToDouble(txtCurrentCharges.Text);

SqlParameter payment = cmdUpdateInvoice.Parameters.Add("@.Payment", SqlDbType.Money);
payment.Value = Convert.ToDouble(txtPayment.Text);

SqlParameter tax = cmdUpdateInvoice.Parameters.Add("@.Tax", SqlDbType.Money);
tax.Value = Convert.ToDouble(txtTax.Text);

int returnvalue = runStoredProcedure(cmdUpdateInvoice);

btnDisplaySubs_Click(btnDisplaySubs, EventArgs.Empty);

}

private int runStoredProcedure(SqlCommand command)
{
int returnvalue = -1;
try{
myConnection.Open();
}
catch{

Response.Write("Something went wrong with the Database Connection");
}

try{
returnvalue = command.ExecuteNonQuery();
}

catch(Exception e){
Response.Write(e.ToString());
}

finally{
myConnection.Close();
}
Response.Write(returnvalue.ToString());
return returnvalue;

}

Here is the procedure:
CREATE procedure usp_updateinvoice
(
@.InvoiceDate smalldatetime,
@.AccountNum varchar( 50 ),
@.CarrierId int,
@.PastDue money,
@.LateFee money,
@.Credit money,
@.CurrentCharges money,
@.TotalDue money,
@.isPaid bit,
@.Comments varchar( 255 ),
@.Notes varchar( 255 ),
@.Visible bit,
@.Cycle int,
@.Payment money,
@.Tax money,

@.OldDate smalldatetime
)

As

If Exists
(
select InvoiceDate, AccountNum
from invoice
where InvoiceDate = @.InvoiceDate and AccountNum = @.AccountNum and CarrierId = @.CarrierId and Cycle = @.Cycle
)

Begin

--runs if the record exists
Update invoice
Set PastDue = @.PastDue, LateFee = @.LateFee,
Credit = @.Credit, CurrentCharges = @.CurrentCharges, TotalDue = @.TotalDue, isPaid = @.isPaid,
Comments = @.Comments, Notes = @.Notes, Visible = @.Visible, Cycle = @.Cycle, Payment = @.Payment, InvoiceTax = @.Tax
Where InvoiceDate = @.InvoiceDate and AccountNum = @.AccountNum and CarrierId = @.CarrierId and Cycle = @.Cycle

return @.@.error

End

Else

Begin

begin transaction

Insert into invoice(InvoiceDate, AccountNum, CarrierId, PastDue, LateFee, Credit, CurrentCharges, TotalDue, isPaid, Comments,
Notes, Visible, Cycle, Payment, InvoiceTax)
Values(@.InvoiceDate, @.AccountNum, @.CarrierId, @.PastDue, @.LateFee, @.Credit, @.CurrentCharges, @.TotalDue, @.isPaid, @.Comments,
@.Notes, @.Visible, @.Cycle, @.Payment, @.Tax)

exec dbo.CopySubaccountsToNewInvoice @.InvoiceDate, @.OldDate, @.CarrierId, @.AccountNum, @.Cycle

commit transaction

return @.@.error

End
GO

|||I looked up error #1 in the Books Online and it says "Version date of last upgrade: 10/11/90"
What do I do with this?
|||You are not extracting the RETURN value correctly. ExecuteNonQuery returns the number of rows affected by the SQLstatement, not the RETURN value of the stored procedure.
You need to add a Parameter with ParameterDirection = ReturnValue tocapture the RETURN from the stored procedure. See this article byJames Crowley for an example:Using ADO.NET with SQL Server - Stored Procedures and other queries with parameter

Alternately, if it were mine to write, I'd add an OUTPUT parameter andSELECT @.@.ERROR into that parameter instead of doing a RETURN @.@.ERROR.

|||>ExecuteNonQuery returns the number of rows affected by the SQL statement, not the RETURN value of the stored procedure.<
Ah! That's exactly what I needed to know. Thanks
|||EDIT: I figured out that the 18 return value is caused by another stored procedure that is called when a new record is created . . . it updates related tables. Odd thing is, it's returning 2 for each row updated. And when I call the procedures for updating those tables individually, it returns 2 as well. It could just be some other minor thing I've not caught.
Ok, I was getting a 1 as a return value. Now I'm getting return values like 2 and 18 from this procedure. There are not even 18 rows in the entire table. What is going on?
|||Personally I think you should ignore the value being returned byExecuteNonQuery. It has no relevance to your application. Running the stored procedure from Query Analyzer and looking at theMessages tab will likely give you insight as to why you are seeingvalues like 2 and 18 being returned.

odbc-timeout error on a linked table from Access2k

Hi.
I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.

I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.

I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference
So what's the problem?
Does anyone have an idea?
/regards BigOlle(olle@.ylm.se) writes:

Quote:

Originally Posted by

I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.
>
I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.
>
I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference

Could be a blocking issue. You can check this in Query Analyzer by
running sp_who2. If there is a non-zero value in the Blk column,
that spid is blocking the spid on that line. With the rest of the
information from sp_who2, you may be able to identify the blocker.

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

ODBCBCP Driver Mismatch

Hello,
I am receiving the following error message whenever I try to replicate using Snapshot repl. (or any type of replication) from the snapshot agent:
Error Message: The process could not bulk copy out of table '[dbo].[syncobj_xxxxxxxx]'.
Error Details: ODBCBCP/Driver version mismatch
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
I have checked the versions of the odbcbcp.dll on both of my SQL Servers (both of which are win2k3 w/ SQL Server 2000 sp3) and they are both 2000.85.1022.0. The version number of sqlsrv32.dll and sqlsrv32.rll are 2000.85.1025.0. Do all three have to mat
ch, is that my problem? Please HELP!! I can't replicate at all!
there are some reports that this problem can be solved by upgrading to a
consistent MDAC versions on both machines.
"Paul Pelletier" <anonymous@.discussions.microsoft.com> wrote in message
news:3E372B82-39B2-4A93-B7DE-5B7740527F13@.microsoft.com...
> Hello,
> I am receiving the following error message whenever I try to replicate
using Snapshot repl. (or any type of replication) from the snapshot agent:
> Error Message: The process could not bulk copy out of table
'[dbo].[syncobj_xxxxxxxx]'.
> Error Details: ODBCBCP/Driver version mismatch
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> I have checked the versions of the odbcbcp.dll on both of my SQL Servers
(both of which are win2k3 w/ SQL Server 2000 sp3) and they are both
2000.85.1022.0. The version number of sqlsrv32.dll and sqlsrv32.rll are
2000.85.1025.0. Do all three have to match, is that my problem? Please
HELP!! I can't replicate at all!
|||Hillary,
I have, I re-applied MDAC 2.7 on both SQL Servers and still the same problem. Any other possible solutions?
Do all there files have to have the same version numbers?
Thanks,
Paul
|||Check the version of the ODBC32.dll. It could be the one that is
mismatched. It should be version 3.525.1022.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||It is mismatched in a sense. The odbcbcp.dll is version 2000.85.1022.0. The sqlsrv32.dll is 2000.85.1025.0. The sqlsrv32.rll is 2000.85.1025.0. So really the one that is mismatched is the sqlsrv32.dll, but which version should be the correct version f
or all three, the 1025 or 1022?
Thanks again,
Paul
|||Sorry about the previous post, I did not completely read your post and I missed the fact that you were talking about a completely different dll, I'm a tard! Anyway both of the odbc32.dll do match on both machines and they are in fact 3.525.1022.0. Where
now?