I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:
select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
from INFORMATION_SCHEMA.ROUTINES|||hey, thanks for the information.
what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.|||Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Exclusive Access to DataBase
Hi.
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
FranciscoALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE|||Thanks
Francisco
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
FranciscoALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE|||Thanks
Francisco
Monday, March 12, 2012
excessive sending package from the sqlserver
The users reported excessive sending package from the sqlserver, we intented down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?
Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
The server administrator rebooted the server and the network was stable
What do you think about it?
Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
excessive sending package from the sqlserver
The users reported excessive sending package from the sqlserver, we intented
down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
Excessive Free Space in Large Table
Hello all,
I'm having the following space allocation issue with a large table in a SQL
Server 2000 DB.
I have a table which stores visits to a site, currently with 95 million
rows. This table currently has a total of 60GB space: 24GB used space, 4GB
index space, and 32 GB unused space. The server has been running out of
space recently, and I would like to reclaim alot of the unused space from
this table. The table definition is as follows:
visitor_id int 4
visitor_settings varchar 7000
short_settings tinyint 1
visit_count int 4
created_reason tinyint 1
modified_by int 4
modified_date datetime 8
created_by int 4
created_date datetime 8
GUID_for_clustering uniqueidentifier 16
There are 2 indexes, a PK index on visitor_id, and a clustered index on
GUID_for_clustering, both with a 50% fill factor. Shrinking the table helps
temporarily, but the unused space climbs back up to 30GB after reindexing
the table. Any thoughts on what is causing the allocation of all the extra
free space, and how I can reduce it? My initial thoughts relate to
adjusting the fill factor, or possibly modifying the visitor_settings
varchar 7000 field (perhaps 'text' would actually reduce the space
allocation?). Any thoughts would be much appreciated.
Thanks!
-MarkMark,
What exactly do you mean when you say "Guid for clustering"? Are you
talking about a Clustered Index? If so then why would you want to do that?
A Guid is about the worst choice for a clustered index that there is.
Here's what I think your issues are other than the clustered index on the
guid. You need plenty of free space in the database for normal activities
such as Reindexing. As a matter of fact BOL recommends 1.2 times the size
of the table in free space before you reindex. That is mainly because sqls
erver will rebuild the table (if it has a clustered index on it) elsewhere
in the db and then drop the original table when done. So you need lots of
free and preferably contiguous space to do this. If you reindex a table and
then shrink the database you will most likely undo all that you accomplished
when you reindexed the table. To shrink the files sql server must clear
space at the end of the file inwards to shrink it. This means it will move
the data to unused space near the beginning. This usually results in
getting heavy fragmentation of the tables again. So if you need the space
you can't keep reindexing the tables otherwise it will just keep growing
again. The real solution is to get more disk space and always keep plenty
of free space available. If your really tight you can bcp out all the data,
truncate all the tables,Shrink the db and bcp it back in again.
Andrew J. Kelly
SQL Server MVP
"Mark Weidner" <mjw22@.drexel.edu> wrote in message
news:e9SLoI55DHA.2764@.TK2MSFTNGP09.phx.gbl...
SQL
4GB
helps
extra
I'm having the following space allocation issue with a large table in a SQL
Server 2000 DB.
I have a table which stores visits to a site, currently with 95 million
rows. This table currently has a total of 60GB space: 24GB used space, 4GB
index space, and 32 GB unused space. The server has been running out of
space recently, and I would like to reclaim alot of the unused space from
this table. The table definition is as follows:
visitor_id int 4
visitor_settings varchar 7000
short_settings tinyint 1
visit_count int 4
created_reason tinyint 1
modified_by int 4
modified_date datetime 8
created_by int 4
created_date datetime 8
GUID_for_clustering uniqueidentifier 16
There are 2 indexes, a PK index on visitor_id, and a clustered index on
GUID_for_clustering, both with a 50% fill factor. Shrinking the table helps
temporarily, but the unused space climbs back up to 30GB after reindexing
the table. Any thoughts on what is causing the allocation of all the extra
free space, and how I can reduce it? My initial thoughts relate to
adjusting the fill factor, or possibly modifying the visitor_settings
varchar 7000 field (perhaps 'text' would actually reduce the space
allocation?). Any thoughts would be much appreciated.
Thanks!
-MarkMark,
What exactly do you mean when you say "Guid for clustering"? Are you
talking about a Clustered Index? If so then why would you want to do that?
A Guid is about the worst choice for a clustered index that there is.
Here's what I think your issues are other than the clustered index on the
guid. You need plenty of free space in the database for normal activities
such as Reindexing. As a matter of fact BOL recommends 1.2 times the size
of the table in free space before you reindex. That is mainly because sqls
erver will rebuild the table (if it has a clustered index on it) elsewhere
in the db and then drop the original table when done. So you need lots of
free and preferably contiguous space to do this. If you reindex a table and
then shrink the database you will most likely undo all that you accomplished
when you reindexed the table. To shrink the files sql server must clear
space at the end of the file inwards to shrink it. This means it will move
the data to unused space near the beginning. This usually results in
getting heavy fragmentation of the tables again. So if you need the space
you can't keep reindexing the tables otherwise it will just keep growing
again. The real solution is to get more disk space and always keep plenty
of free space available. If your really tight you can bcp out all the data,
truncate all the tables,Shrink the db and bcp it back in again.
Andrew J. Kelly
SQL Server MVP
"Mark Weidner" <mjw22@.drexel.edu> wrote in message
news:e9SLoI55DHA.2764@.TK2MSFTNGP09.phx.gbl...
quote:
> Hello all,
> I'm having the following space allocation issue with a large table in a
SQL
quote:
> Server 2000 DB.
> I have a table which stores visits to a site, currently with 95 million
> rows. This table currently has a total of 60GB space: 24GB used space,
4GB
quote:
> index space, and 32 GB unused space. The server has been running out of
> space recently, and I would like to reclaim alot of the unused space from
> this table. The table definition is as follows:
> visitor_id int 4
> visitor_settings varchar 7000
> short_settings tinyint 1
> visit_count int 4
> created_reason tinyint 1
> modified_by int 4
> modified_date datetime 8
> created_by int 4
> created_date datetime 8
> GUID_for_clustering uniqueidentifier 16
> There are 2 indexes, a PK index on visitor_id, and a clustered index on
> GUID_for_clustering, both with a 50% fill factor. Shrinking the table
helps
quote:
> temporarily, but the unused space climbs back up to 30GB after reindexing
> the table. Any thoughts on what is causing the allocation of all the
extra
quote:
> free space, and how I can reduce it? My initial thoughts relate to
> adjusting the fill factor, or possibly modifying the visitor_settings
> varchar 7000 field (perhaps 'text' would actually reduce the space
> allocation?). Any thoughts would be much appreciated.
> Thanks!
> -Mark
>
Wednesday, March 7, 2012
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I just received this message:
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I am completely dead in the water. Cannot connect to SSAS 2005. HELP!
hello,
it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?
the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).
if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?
thanks a lot,
|||I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.
|||
Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:
Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:
C drive - 10 GB (RAID 1)
D drive - 8 GB (4095 pagefile is here) (RAID 10)
E drive - 50GB (RAID 10)
RAM - 8GB
OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)
DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.
Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?
How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?
I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!
|||
Okay,
I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type
'System.OutOfMemoryException' was thrown
error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?
|||We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.
Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.
Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).
Adrian Dumitrascu.
|||The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.
:)
|||
I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.
You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.
Adrian Dumitrascu.
|||I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).
Adrian Dumitrascu.
|||Adrian,
Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:
Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.
What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.
krj
|||Hi Kjr,
I have the same problem that you had before the last post.
What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?
tks,
Eder F. Dias
|||
We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.
krj
|||Thanks Kjr for answer.
Actually my environment is:
- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM
- Windows 2003 Enterprise x64 SP1
- SQL Server 2005 Enterprise x64 (SP1 - build 2153)
How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?
Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?
More informations about my server:
OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys
tks a lot
Eder F Dias
|||
Adrian,
You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?
- Steve
|||Steve,
I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.
The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I just received this message:
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I am completely dead in the water. Cannot connect to SSAS 2005. HELP!
hello,
it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?
the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).
if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?
thanks a lot,
|||I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.
|||
Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:
Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:
C drive - 10 GB (RAID 1)
D drive - 8 GB (4095 pagefile is here) (RAID 10)
E drive - 50GB (RAID 10)
RAM - 8GB
OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)
DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.
Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?
How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?
I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!
|||
Okay,
I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type
'System.OutOfMemoryException' was thrown
error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?
|||We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.
Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.
Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).
Adrian Dumitrascu.
|||The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.
:)
|||
I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.
You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.
Adrian Dumitrascu.
|||I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).
Adrian Dumitrascu.
|||Adrian,
Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:
Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.
What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.
krj
|||Hi Kjr,
I have the same problem that you had before the last post.
What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?
tks,
Eder F. Dias
|||
We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.
krj
|||Thanks Kjr for answer.
Actually my environment is:
- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM
- Windows 2003 Enterprise x64 SP1
- SQL Server 2005 Enterprise x64 (SP1 - build 2153)
How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?
Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?
More informations about my server:
OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys
tks a lot
Eder F Dias
|||
Adrian,
You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?
- Steve
|||Steve,
I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.
The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I just received this message:
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I am completely dead in the water. Cannot connect to SSAS 2005. HELP!
hello,
it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?
the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).
if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?
thanks a lot,
|||I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.
|||Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:
Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:
C drive - 10 GB (RAID 1)
D drive - 8 GB (4095 pagefile is here) (RAID 10)
E drive - 50GB (RAID 10)
RAM - 8GB
OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)
DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.
Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?
How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?
I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!
|||Okay,
I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type
'System.OutOfMemoryException' was thrown
error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?
|||We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.
Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.
Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).
Adrian Dumitrascu.
|||The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.
:)
|||I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.
You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.
Adrian Dumitrascu.
|||I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).
Adrian Dumitrascu.
|||Adrian,
Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:
Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.
What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.
krj
|||Hi Kjr,
I have the same problem that you had before the last post.
What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?
tks,
Eder F. Dias
|||We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.
krj
|||Thanks Kjr for answer.
Actually my environment is:
- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM
- Windows 2003 Enterprise x64 SP1
- SQL Server 2005 Enterprise x64 (SP1 - build 2153)
How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?
Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?
More informations about my server:
OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys
tks a lot
Eder F Dias
|||Adrian,
You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?
- Steve
|||Steve,
I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.
The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I just received this message:
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
I am completely dead in the water. Cannot connect to SSAS 2005. HELP!
hello,
it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?
the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).
if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?
thanks a lot,
|||I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.
|||Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:
Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:
C drive - 10 GB (RAID 1)
D drive - 8 GB (4095 pagefile is here) (RAID 10)
E drive - 50GB (RAID 10)
RAM - 8GB
OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)
DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.
Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?
How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?
I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!
|||Okay,
I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type
'System.OutOfMemoryException' was thrown
error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?
|||We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.
Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.
Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).
Adrian Dumitrascu.
|||The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.
:)
|||I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.
You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.
Adrian Dumitrascu.
|||I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).
Adrian Dumitrascu.
|||Adrian,
Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:
Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.
What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.
krj
|||Hi Kjr,
I have the same problem that you had before the last post.
What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?
tks,
Eder F. Dias
|||We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.
krj
|||Thanks Kjr for answer.
Actually my environment is:
- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM
- Windows 2003 Enterprise x64 SP1
- SQL Server 2005 Enterprise x64 (SP1 - build 2153)
How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?
Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?
More informations about my server:
OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys
tks a lot
Eder F Dias
|||Adrian,
You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?
- Steve
|||Steve,
I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.
The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.
Exception in xa_open with SqlServer 2000 SP3a on Win 2003
Hi!
I have an application server and try to access my SqlServer 2000
database running on Win 2003 using a two-phase commit connection
(datasource property enable2Phase=true). The JDBC driver is SP3.
I receive the following exception:
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]xa_open (0) returns -3
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
at
com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
Source)
If I disable either the enable2Phase or use a SqlServer database on
Win2000 everything works fine.
Any ideas?
My JDBC driver reports the following:
Database product name : Microsoft SQL Server
Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
JDBC driver name : SQLServer
JDBC driver version : 2.2.0040
David S. Faller wrote:
> Hi!
> I have an application server and try to access my SqlServer 2000
> database running on Win 2003 using a two-phase commit connection
> (datasource property enable2Phase=true). The JDBC driver is SP3.
> I receive the following exception:
> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]xa_open (0) returns -3
Hi. That's not a driver issue, it is a failure of an ancillary ddl that
implements the extended stored procedures thet SQLServer uses for XA.
There have been recent fixes to those DLLs, so the first thing to do is to
upgrade the DBMS to the latest fixpack.
Joe Weinstein at BEA
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
> at
> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
> at com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
> Source)
> If I disable either the enable2Phase or use a SqlServer database on
> Win2000 everything works fine.
> Any ideas?
> My JDBC driver reports the following:
> Database product name : Microsoft SQL Server
> Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> JDBC driver name : SQLServer
> JDBC driver version : 2.2.0040
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||David S. Faller wrote:
> Hi Joe,
> Thanks for this information. I verified that the SQL Server has been
> updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
> directory is the one shipped with the latest JDBC driver (SP3).
> Still no luck...
> Can you give me more details on which dll has to be updated and where to
> get the update? The list of post-SP3 updates of SQL Server does not list
> any XA related issues
> (http://support.microsoft.com/common/...v2000presp4fix).
Hi. I am surprised that sp4 isn't out yet. I had been told it would have been.
I also have heqrd that MS will make no mention of the XA problem(s) until
the sp4 comes out, at which time it will be in the release notes.
Joe
[vbcol=seagreen]
>
> Thanks,
> David Faller
> Joe Weinstein wrote:
|||--
| Message-ID: <4151A8CE.20005@.bea.com>
| Date: Wed, 22 Sep 2004 09:31:10 -0700
| From: Joe Weinstein <joeNOSPAM@.bea.com>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624 Netscape/7.1 (ax)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| To: "David S. Faller" <davidfaller@.de.ibm.com>
| Subject: Re: Exception in xa_open with SqlServer 2000 SP3a on Win 2003
| References: <#W9Gg$#nEHA.2096@.TK2MSFTNGP15.phx.gbl>
<415062D3.6080401@.bea.com> <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| In-Reply-To: <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: sj-ez-63-96-169-77.bea.com 63.96.169.77
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6296
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
|
|
| David S. Faller wrote:
|
| > Hi Joe,
| >
| > Thanks for this information. I verified that the SQL Server has been
| > updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
| > directory is the one shipped with the latest JDBC driver (SP3).
| > Still no luck...
| >
| > Can you give me more details on which dll has to be updated and where
to
| > get the update? The list of post-SP3 updates of SQL Server does not
list
| > any XA related issues
| >
(http://support.microsoft.com/common/...v2000presp4fix).
|
| Hi. I am surprised that sp4 isn't out yet. I had been told it would have
been.
| I also have heqrd that MS will make no mention of the XA problem(s) until
| the sp4 comes out, at which time it will be in the release notes.
| Joe
|
| >
| >
| > Thanks,
| > David Faller
| >
| > Joe Weinstein wrote:
| >
| >>
| >>
| >> David S. Faller wrote:
| >>
| >>> Hi!
| >>>
| >>> I have an application server and try to access my SqlServer 2000
| >>> database running on Win 2003 using a two-phase commit connection
| >>> (datasource property enable2Phase=true). The JDBC driver is SP3.
| >>>
| >>> I receive the following exception:
| >>>
| >>> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
| >>> for JDBC][SQLServer]xa_open (0) returns -3
| >>
| >>
| >>
| >> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
| >> implements the extended stored procedures thet SQLServer uses for XA.
| >> There have been recent fixes to those DLLs, so the first thing to do
| >> is to
| >> upgrade the DBMS to the latest fixpack.
| >> Joe Weinstein at BEA
| >>
| >>> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknow
n
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown
| >>> Source)
| >>> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown
Source)
| >>> at
| >>> com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
| >>> Source)
| >>>
| >>> If I disable either the enable2Phase or use a SqlServer database on
| >>> Win2000 everything works fine.
| >>>
| >>> Any ideas?
| >>>
| >>> My JDBC driver reports the following:
| >>> Database product name : Microsoft SQL Server
| >>> Database product version : Microsoft SQL Server 2000 - 8.00.760
| >>> (Intel X86)
| >>> JDBC driver name : SQLServer
| >>> JDBC driver version : 2.2.0040
| >>
| >>
| >>
|
|
We have a Knowledge Base article that describes some additional changes
that are required for XA transaction support on Windows Server 2003:
817066 INFO: Registry Entries Are Required for XA Transaction Support
http://support.microsoft.com/?id=817066
Please review this document and see if this resolves the issue.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien [MSFT] wrote:
> We have a Knowledge Base article that describes some additional
> changes
> that are required for XA transaction support on Windows Server 2003:
> 817066 INFO: Registry Entries Are Required for XA Transaction
> Support
> http://support.microsoft.com/?id=817066
> Please review this document and see if this resolves the issue.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
>
I tried adding a registry entry for sqlsrv32.dll but I still get
"[SQLServer]xa_open (0) returns -3" in Java and "xa_open_entry failed"
in sqljdbc.log. Are there other DLLs I'm supposed to add? How do we
find out which DLLs need registry entries?
memebag
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message400191.html
I have an application server and try to access my SqlServer 2000
database running on Win 2003 using a two-phase commit connection
(datasource property enable2Phase=true). The JDBC driver is SP3.
I receive the following exception:
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]xa_open (0) returns -3
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
at
com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
Source)
If I disable either the enable2Phase or use a SqlServer database on
Win2000 everything works fine.
Any ideas?
My JDBC driver reports the following:
Database product name : Microsoft SQL Server
Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
JDBC driver name : SQLServer
JDBC driver version : 2.2.0040
David S. Faller wrote:
> Hi!
> I have an application server and try to access my SqlServer 2000
> database running on Win 2003 using a two-phase commit connection
> (datasource property enable2Phase=true). The JDBC driver is SP3.
> I receive the following exception:
> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]xa_open (0) returns -3
Hi. That's not a driver issue, it is a failure of an ancillary ddl that
implements the extended stored procedures thet SQLServer uses for XA.
There have been recent fixes to those DLLs, so the first thing to do is to
upgrade the DBMS to the latest fixpack.
Joe Weinstein at BEA
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
> at
> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
> at com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
> Source)
> If I disable either the enable2Phase or use a SqlServer database on
> Win2000 everything works fine.
> Any ideas?
> My JDBC driver reports the following:
> Database product name : Microsoft SQL Server
> Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> JDBC driver name : SQLServer
> JDBC driver version : 2.2.0040
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||David S. Faller wrote:
> Hi Joe,
> Thanks for this information. I verified that the SQL Server has been
> updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
> directory is the one shipped with the latest JDBC driver (SP3).
> Still no luck...
> Can you give me more details on which dll has to be updated and where to
> get the update? The list of post-SP3 updates of SQL Server does not list
> any XA related issues
> (http://support.microsoft.com/common/...v2000presp4fix).
Hi. I am surprised that sp4 isn't out yet. I had been told it would have been.
I also have heqrd that MS will make no mention of the XA problem(s) until
the sp4 comes out, at which time it will be in the release notes.
Joe
[vbcol=seagreen]
>
> Thanks,
> David Faller
> Joe Weinstein wrote:
|||--
| Message-ID: <4151A8CE.20005@.bea.com>
| Date: Wed, 22 Sep 2004 09:31:10 -0700
| From: Joe Weinstein <joeNOSPAM@.bea.com>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624 Netscape/7.1 (ax)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| To: "David S. Faller" <davidfaller@.de.ibm.com>
| Subject: Re: Exception in xa_open with SqlServer 2000 SP3a on Win 2003
| References: <#W9Gg$#nEHA.2096@.TK2MSFTNGP15.phx.gbl>
<415062D3.6080401@.bea.com> <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| In-Reply-To: <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: sj-ez-63-96-169-77.bea.com 63.96.169.77
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6296
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
|
|
| David S. Faller wrote:
|
| > Hi Joe,
| >
| > Thanks for this information. I verified that the SQL Server has been
| > updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
| > directory is the one shipped with the latest JDBC driver (SP3).
| > Still no luck...
| >
| > Can you give me more details on which dll has to be updated and where
to
| > get the update? The list of post-SP3 updates of SQL Server does not
list
| > any XA related issues
| >
(http://support.microsoft.com/common/...v2000presp4fix).
|
| Hi. I am surprised that sp4 isn't out yet. I had been told it would have
been.
| I also have heqrd that MS will make no mention of the XA problem(s) until
| the sp4 comes out, at which time it will be in the release notes.
| Joe
|
| >
| >
| > Thanks,
| > David Faller
| >
| > Joe Weinstein wrote:
| >
| >>
| >>
| >> David S. Faller wrote:
| >>
| >>> Hi!
| >>>
| >>> I have an application server and try to access my SqlServer 2000
| >>> database running on Win 2003 using a two-phase commit connection
| >>> (datasource property enable2Phase=true). The JDBC driver is SP3.
| >>>
| >>> I receive the following exception:
| >>>
| >>> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
| >>> for JDBC][SQLServer]xa_open (0) returns -3
| >>
| >>
| >>
| >> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
| >> implements the extended stored procedures thet SQLServer uses for XA.
| >> There have been recent fixes to those DLLs, so the first thing to do
| >> is to
| >> upgrade the DBMS to the latest fixpack.
| >> Joe Weinstein at BEA
| >>
| >>> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknow
n
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown
| >>> Source)
| >>> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown
Source)
| >>> at
| >>> com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
| >>> Source)
| >>>
| >>> If I disable either the enable2Phase or use a SqlServer database on
| >>> Win2000 everything works fine.
| >>>
| >>> Any ideas?
| >>>
| >>> My JDBC driver reports the following:
| >>> Database product name : Microsoft SQL Server
| >>> Database product version : Microsoft SQL Server 2000 - 8.00.760
| >>> (Intel X86)
| >>> JDBC driver name : SQLServer
| >>> JDBC driver version : 2.2.0040
| >>
| >>
| >>
|
|
We have a Knowledge Base article that describes some additional changes
that are required for XA transaction support on Windows Server 2003:
817066 INFO: Registry Entries Are Required for XA Transaction Support
http://support.microsoft.com/?id=817066
Please review this document and see if this resolves the issue.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien [MSFT] wrote:
> We have a Knowledge Base article that describes some additional
> changes
> that are required for XA transaction support on Windows Server 2003:
> 817066 INFO: Registry Entries Are Required for XA Transaction
> Support
> http://support.microsoft.com/?id=817066
> Please review this document and see if this resolves the issue.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
>
I tried adding a registry entry for sqlsrv32.dll but I still get
"[SQLServer]xa_open (0) returns -3" in Java and "xa_open_entry failed"
in sqljdbc.log. Are there other DLLs I'm supposed to add? How do we
find out which DLLs need registry entries?
memebag
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message400191.html
Sunday, February 26, 2012
Exception Handling
Hi All
I m new to the sqlserver.In Oracle we can handle exceptions like this
declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/
We will get the message No Entry When corrsponding employee number dosent exists.
In Sqlserver how to handle these things.
Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions
Baba
You can do in sqlserver using @.@.Rowcount
select ename into name from emp where eno=&eno
if((select @.@.Rowcount)<= 0)
print 'No data'
The following explains the errorhandling in simple terms
http://www.sqlteam.com/article/handling-errors-in-stored-procedures
|||In SQL Server this situation never treated as exception, There is a workaround available - here it is,
Code Snippet
Declare @.Sno as int;
Declare @.name as varchar(100)
Set @.Sno=4
Select @.name=name from sysobjects Where id=@.Sno
If @.@.RowCount = 0
Print 'No Entry'
Else
Print @.Name
|||Hi manivannanI tried like this before only . I got the output
Thank u for u r valuable reply.
Baba
Exception error in snapshot agent
Greetings,
We are using SQLServer 2000 and
we are replicating one server to another using merge replication... It has
been working fine
but all of the sudden, we are getting "An exception occurred in the Snapshot
subsystem"...
I've tried re-creating the publication a number of times but always the same
error with the Snapshot...
I've looked through all the log files but can't find any additional info
about the error.
Does anyone have any suggestion as to where or what to look for?
Thanks in advance.
Hi Steve,
From what you described below, it looks like the replication subsystem for
launching the snapshot agent from SQLServerAgent is crashing. You may want to
try stopping and restarting the SQLServerAgent service and see if that
resolves the problem.
If you are running a version of SQL2000 < SP4, the crash may also be due to
an inproperly handled COM error in the replication subsystem of
SQLServerAgent. As such, upgrading your SQL2000 instance to SP4 may provide
you with a more descriptive error of the underlying problem. If you do get a
more descriptive error after upgrading to SP4, you may be able to find a
solution by searching for the error string on the Microsoft Support web site.
HTH
-Raymond
"Steve Stoenner" wrote:
> Greetings,
> We are using SQLServer 2000 and
> we are replicating one server to another using merge replication... It has
> been working fine
> but all of the sudden, we are getting "An exception occurred in the Snapshot
> subsystem"...
> I've tried re-creating the publication a number of times but always the same
> error with the Snapshot...
> I've looked through all the log files but can't find any additional info
> about the error.
> Does anyone have any suggestion as to where or what to look for?
> Thanks in advance.
>
>
We are using SQLServer 2000 and
we are replicating one server to another using merge replication... It has
been working fine
but all of the sudden, we are getting "An exception occurred in the Snapshot
subsystem"...
I've tried re-creating the publication a number of times but always the same
error with the Snapshot...
I've looked through all the log files but can't find any additional info
about the error.
Does anyone have any suggestion as to where or what to look for?
Thanks in advance.
Hi Steve,
From what you described below, it looks like the replication subsystem for
launching the snapshot agent from SQLServerAgent is crashing. You may want to
try stopping and restarting the SQLServerAgent service and see if that
resolves the problem.
If you are running a version of SQL2000 < SP4, the crash may also be due to
an inproperly handled COM error in the replication subsystem of
SQLServerAgent. As such, upgrading your SQL2000 instance to SP4 may provide
you with a more descriptive error of the underlying problem. If you do get a
more descriptive error after upgrading to SP4, you may be able to find a
solution by searching for the error string on the Microsoft Support web site.
HTH
-Raymond
"Steve Stoenner" wrote:
> Greetings,
> We are using SQLServer 2000 and
> we are replicating one server to another using merge replication... It has
> been working fine
> but all of the sudden, we are getting "An exception occurred in the Snapshot
> subsystem"...
> I've tried re-creating the publication a number of times but always the same
> error with the Snapshot...
> I've looked through all the log files but can't find any additional info
> about the error.
> Does anyone have any suggestion as to where or what to look for?
> Thanks in advance.
>
>
Exception comes from where?
Being new to SQLServer, I may be doing something very basic wrong, but here's
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:
>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:
> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)
> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:
> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems
> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:
> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:
> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?
> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.
> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:
>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:
> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)
> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:
> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems
> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:
> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:
> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?
> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.
> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>
Subscribe to:
Posts (Atom)