New to SQL - Please help

Welcome to SQL
Try doing the following at a time when the database has
few or no users.
Right Click on the Database and select Properties
Select Options Tab
Under Recovery - Model if its not set to simple change it
to simple.
Press OK
Right Click the database and select All Tasks -> Shrink
Database
Select the check box 'Move Pages to beginning...'
Press ok
Go make yourself a drink whilst your waiting ;)
And after if finishes change your 'Recovery - Model' back
to what it was prior to your change.
Peter

>--Original Message--
>Please excues my ignorance, but I am in no way a SQL guy;
so here goes:
>I have an accounting package that uses SQL 2000. SQL is
creating a backup
>file that is getting HUGE (about 2 GB)
>The actual data is around a few hundred MB.
>Is there a way to reduce the size of the backup file by:
>Compacting?
>Performaing a backup (that SQL knows about)?
>Is there a way to move that file?
>Can any of this be done on-line?
>Thanks for any help.
>Regards;
>PB
>
>.
>
Please forgive my ignorance, but:
Where is the database? It appears that the app that uses SQL is pointing to
a file share on another server. The backup file (BAK) is local, and it is
killing me. As I said, it is 2 GB and growing.
If it is the BAK file that is huge; will that fix my problem? The database
itself is only 132 MB.
TIA;
PB
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...[vbcol=seagreen]
> Welcome to SQL
> Try doing the following at a time when the database has
> few or no users.
> Right Click on the Database and select Properties
> Select Options Tab
> Under Recovery - Model if its not set to simple change it
> to simple.
> Press OK
> Right Click the database and select All Tasks -> Shrink
> Database
> Select the check box 'Move Pages to beginning...'
> Press ok
> Go make yourself a drink whilst your waiting ;)
> And after if finishes change your 'Recovery - Model' back
> to what it was prior to your change.
> Peter
>
>
> so here goes:
> creating a backup
|||When releasing space SQL Server doesn't actually free
disk space, its thinking is if its needed it before, it
needs it now, so if you say delete half your database you
will see the same disk space.
The problem though is probably to do with your log file
that will grows a heck of a lot.
To work out where your files are click onto your database
then at the top menu selecy view 'Something (I'm away
from SQL) pad'
It will give you the location and size of your data files
and log files.
These files can be anywhere on your Network.
As for your backup files (.BAK) its considered good
practice to store them on a different server than the one
that SQL is on.
And yes, performing the steps will solve the problem as
the steps will release the unused disk space and so the
backups will not be fooled into thinking there is more to
the files than there actually is.
Anyway its 22:15 here and I am off to do something else.
Good luck
Peter
Peter

>--Original Message--
>Please forgive my ignorance, but:
>Where is the database? It appears that the app that uses
SQL is pointing to
>a file share on another server. The backup file (BAK) is
local, and it is
>killing me. As I said, it is 2 GB and growing.
>If it is the BAK file that is huge; will that fix my
problem? The database
>itself is only 132 MB.
>TIA;
>PB
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
it[vbcol=seagreen]
back[vbcol=seagreen]
guy;[vbcol=seagreen]
is[vbcol=seagreen]
by:
>
>.
>

new to SQL - create table command question

hi

can someone please explain the following please

why does this fail with specified owner testdb does not exist or you do not have permissions

create testdb.testtable (

testval int

)

yet if i create testdb and then run the following against this new db

create testtable (

testval int

)

command command completes successfully......what am i not understanding or doing wrong?

thank you

oooops....sorry

im so new i cant even type the command correctly

this command fails....

create table testdb.testtable (

testval int

)

this command works....

create table testtable (

testval int

)

|||Depending on the version of SQL Server you are using, the prefix testdb indicates either the owner of the table or the schema name that the object / table will be stored in. it does not identify the database where the object has to be created in. For the database identifier you will have to you the three partname which would be (assuming that you want to store the object in the users default schema (for SQL 2k5) or within his owner "schema":

CREATE TABLE testdb..SomeTable
(SomeColumn int)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

New to reporting service

I am new to reporting service but has used VS 2005 beta to develop winform app for a long time. I have the following questions:

1. Are the report object and report viewer in VS2005(winform) related to those of SQL server reporting service? If yes, what are the relationship?
2. If I just want to create/view report with report viewer of VS2005 in my app which connect to SQL Server Express, do I need reporting service?
3. I can see there are options to install reporting service while installing SQL Server Express(free of charge), can I develop app for my client which use reporting service with SQL Server Express?

Thank a lot!Any idea?|||

Okay, here we go.

Reporting Services
Reporting Services is a component of SQL Server 2000 and SQL Server 2005. Basically the Reporting Services acts as a Report Repository and supports such features as:
* Server-Side Rendering = The server does the work
* Subscriptions - Reports can be generated at scheduled times and be automatically distributed to email or file shares.
* Report Snapshots - Report output can be rendered and stored on the server, for instance a monthly report can have a monthly snapshot. This way you never to need to print and store common reports, the server has the old copy even if the orginial report data is gone.

ReportViewer Control
Okay, so Reporting Services basically rocks because it solved the majority of your reporting issues and comes free with most versions of SQL Server. Now, in with SQL Server 2005 and Visual Studio 2005 there are 2 new controls that make reporting services even better. There is a Windows forms and asp.net ReportViewer control. The control can work in 2 modes, Remote and Local. In Remote mode it can be used to integrate with Reporting Services as users can view reports stored on the server. In local mode (what you want) it can displayed reports that have been embedded within your application. In this mode Reporting Services is not required, which makes it ideal to use with SQL Express. There are only 2 minor disadvantages to running in local mode. The first is that the client computer has to do all the work and report rendering, but its not bad for smaller applications. The second is that the client must have this ReportViewer pack installed. Basically, it contains all of the rendering pieces needed to render a report on the client machine. The great news is that if you deploy your application using the new ClickOnce, you can automatically deploy the ReportViewerPack (also it is very small like only a couple of MB!).

|||Thanks

New to reporting service

I am new to reporting service but has used VS 2005 beta to develop winform app for a long time. I have the following questions:

1. Are the report object and report viewer in VS2005(winform) related to those of SQL server reporting service? If yes, what are the relationship?
2. If I just want to create/view report with report viewer of VS2005 in my app which connect to SQL Server Express, do I need reporting service?
3. I can see there are options to install reporting service while installing SQL Server Express(free of charge), can I develop app for my client which use reporting service with SQL Server Express?

Thank a lot!

Any idea?|||

Okay, here we go.

Reporting Services
Reporting Services is a component of SQL Server 2000 and SQL Server 2005. Basically the Reporting Services acts as a Report Repository and supports such features as:
* Server-Side Rendering = The server does the work
* Subscriptions - Reports can be generated at scheduled times and be automatically distributed to email or file shares.
* Report Snapshots - Report output can be rendered and stored on the server, for instance a monthly report can have a monthly snapshot. This way you never to need to print and store common reports, the server has the old copy even if the orginial report data is gone.

ReportViewer Control
Okay, so Reporting Services basically rocks because it solved the majority of your reporting issues and comes free with most versions of SQL Server. Now, in with SQL Server 2005 and Visual Studio 2005 there are 2 new controls that make reporting services even better. There is a Windows forms and asp.net ReportViewer control. The control can work in 2 modes, Remote and Local. In Remote mode it can be used to integrate with Reporting Services as users can view reports stored on the server. In local mode (what you want) it can displayed reports that have been embedded within your application. In this mode Reporting Services is not required, which makes it ideal to use with SQL Express. There are only 2 minor disadvantages to running in local mode. The first is that the client computer has to do all the work and report rendering, but its not bad for smaller applications. The second is that the client must have this ReportViewer pack installed. Basically, it contains all of the rendering pieces needed to render a report on the client machine. The great news is that if you deploy your application using the new ClickOnce, you can automatically deploy the ReportViewerPack (also it is very small like only a couple of MB!).

|||Thanks

New to CR

I'm coming in on the back end of a project and I don't understand the following record selection formula. Would someone please explain what it's doing?

if {?@.rptfilter} = 0 then
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka|||This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka

I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?|||Hello,

I'm doing project using Access as backend and VB6 FrontEnd. I have the following problem during compilation. I'm using Runtime Activex lib of CR 8.5.

Error: Method or Data member not found

===> Private Sub mnu_stock_Click()
With Form1.CrystalReport1
.DataFiles(0) = App.Path & "\MyDB.MDB"
.ReportFileName = App.Path & "\Report\RPT_AVA_STOCK.rpt"
.username = "Admin"
.Password = "1010101010" & Chr(10) & "1010101010"
.Action = 1
.PageZoom (100)
End With
End Sub

Thank you,

Regards,

Niranjan Dixit|||I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?

Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values

and the code it self:
if {?@.rptfilter} = 0 then
- rpt filter = 0

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
- This section does nothing

and
(if {?@.typeid} = 0 then
- rptfilter = 0 and typeid = 0

ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- When rptfilter = 0 and typeid = 0 then only record having CAS_SP_SolnLosses;1.Typevalue = "ANY" are included

else
if {?@.typeid} = 1 then
- rptfilter = 0 and typeid = 1

{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
- When rptfilter = 0 and typeid = 1 then is joined to CAS_SP_SolnLosses;1.Typevalue = {CAS_SP_SolnLosses;1.Region}, i.e. values have to be equal
- Similar for typeid 2 - 8

else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else

if {?@.rptfilter} = 1 then
- rptfilter = 1

({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
- rptfilter = 1 and CAS_SP_SolnLosses;1.Contractor = "ARORA" or CAS_SP_SolnLosses;1.Contractor "CAJV"

and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- This is similar to above except that rptfilter has value 1 and further below value 2

else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})|||Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
<snip>

Thanks for your help!

New Subscription

We are getting the following error when trying to create a new subscription:
An internal error occurred on the report server. See the error log for more
details.
(rsInternalError) Get Online Help EXECUTE permission denied on object
'xp_sqlagent_is_starting', database 'master', owner 'dbo'.
I can't seem to find what's causing it.
Help'I'm having the same problem...any resolution to this?
"Bob Sherman" wrote:
> We are getting the following error when trying to create a new subscription:
> An internal error occurred on the report server. See the error log for more
> details.
> (rsInternalError) Get Online Help EXECUTE permission denied on object
> 'xp_sqlagent_is_starting', database 'master', owner 'dbo'.
> I can't seem to find what's causing it.
> Help'

New Sqlcommand question??

Hello,

I was trying to do the following:

Dim cmd As SqlCommand
Dim objConnection As SqlConnection
objConnection = New SqlConnection = Web.config

where the We.config is where my connection string is set. but I get a sintax error in the Web.config line.

is it possible to asign the value of the web.config content to the new sql connection?

thanks for any suggestions.

Hi, you can use WebConfigurationManager class to get connection string from web.config. Please refer to this article:

http://msdn2.microsoft.com/en-us/library/system.web.configuration.webconfigurationmanager(d=ide).aspx

New SQL Server 2005 Installation

I have just purchased SQL Server 2005 and a new Dell 2800 server to run it on. I was told by my Dell rep to configure the server with the following drives. 2 - 36GB drives in a RAID 0 (15,000 RPM), 2 - 73GB drives in a RAID 0 (10,000 RPM) and 3 - 146GB drives in a RAID 5 (10,000 RPM). Dell pre-installed the OS on the 36GB drive. I was told to install SQL on the 73GB drives and the data (primarily TIFF images) on the 146GB. The server is a 64bit machine with Windows 2003 Server and 4GB of RAM. I was told to put the transaction logs on a different physical drive that the database to speed the writes. Is this a correct installation? Is there any information on a best practice installation? I have spent about 6 hours on MS and Google trying to find an answer. Thanks for any help you can give. Recap of Dell recommendation:

2 - 36GB Mirrored Drives = OS

2 - 73BG Mirrored Drives = SQL Server 2005

3 - 146GB RAID 5 Drives = Data (Images)

Is this correct?

Where should the Transaction Logs Be?

Because transaction logging is a mostly write operation the most efficient place for them is on a mirrored drive set. I usually install SQL Server executables on the same drive as the operating system. (The network admins here configure 10GB for the OS and SQL Server, and it's always been plenty.) I place the data files on the RAID array, and the transaction logs on the mirrored set. When you're running setup specify the RAID drive for the data and the 36GB mirror set for the program files, then after installation set your default location for the log files to the 73GB mirror set and you should be all set.

New request is not allowed to start because it should come with valid transaction des

I have a C# windows service that uses MSMQ, DTC Transactions, SQL server
2005 and Notification services.
I am doing the following in the service:
OnStart
Create the queue
set the P Complete event
call Begin P
PComplete
WaitHandle[] waitHandle = new WaitHandle[] { new
AutoResetEvent(false) };
ThreadPool.QueueUserWorkItem(ProcessMessage, waitHandle[0])
Call End P
WaitHandle.WaiteAll(waitHandle)
queue.BeginP
ProcessMessage(object stateInfo)
AutoResetEvent are = (AutoResetEvent)stateInfo;
Create DTC Transaction
try
Message msg =
callResultQueue.Receive(MessageQueueTransactionType.Automatic);
are.Set();
retreive Message Body
Using (SQLConnection conn = new SQLConnection(connectionString))
conn.open
Write Message Body to database
If Messagebody.Status = 20
Update Notification Services
Commit DTC transaction
catch
Roll Back DTC Transaction
Quite often I am receiving the above message, I've googled it and all I've
come up with is people asking how to solve the error. Any information would
be greatly appreciated. I know the above is mixed psudeo code with code, if
additional code is required please let me know, I'll provide what I can.
Thanks
WayneIt would be helpful if you could post working code...
Noel
Wayne Sepega wrote:
> I have a C# windows service that uses MSMQ, DTC Transactions, SQL server
> 2005 and Notification services.
> I am doing the following in the service:
> OnStart
> Create the queue
> set the P Complete event
> call Begin P
> PComplete
> WaitHandle[] waitHandle = new WaitHandle[] { new
> AutoResetEvent(false) };
> ThreadPool.QueueUserWorkItem(ProcessMessage, waitHandle[0])
> Call End P
> WaitHandle.WaiteAll(waitHandle)
> queue.BeginP
> ProcessMessage(object stateInfo)
> AutoResetEvent are = (AutoResetEvent)stateInfo;
> Create DTC Transaction
> try
> Message msg =
> callResultQueue.Receive(MessageQueueTransactionType.Automatic);
> are.Set();
> retreive Message Body
> Using (SQLConnection conn = new SQLConnection(connectionString))
> conn.open
> Write Message Body to database
> If Messagebody.Status = 20
> Update Notification Services
> Commit DTC transaction
> catch
> Roll Back DTC Transaction
>
> Quite often I am receiving the above message, I've googled it and all I've
> come up with is people asking how to solve the error. Any information woul
d
> be greatly appreciated. I know the above is mixed psudeo code with code, i
f
> additional code is required please let me know, I'll provide what I can.
> Thanks
> Wayne

New Processes after upgrade

Hi There

After upgrading to Sql Server 2005 Enterprise Edition , SP1.

if i run the following query:

select * from sysprocesses where waittime <> 0 order by waittime desc

I get the following result:

spid waittime lastwaittype status

10 4480 0 0x007E 78424187 ONDEMAND_TASK_QUEUE background
4 5620 0 0x009D 78418906 KSOURCE_WAKEUP background
16 4512 0 0x00A9 78418890 BROKER_TRANSMITTER background
17 4516 0 0x00A9 78418875 BROKER_TRANSMITTER background
12 5056 0 0x00AD 78418796 BROKER_EVENTHANDLER background
11 5728 0 0x0081 179140 CHECKPOINT_QUEUE background

Basscially the waittime just keeps going up infinitely for these processes, is this normal, should i be worried ?

Thanx

Have same issue with BROKER_EVENTHANDLER and BROKER_TRANSMITTER.

Normal behavior?

|||

Hi

This link from microsoft describes the various Wait Types (of which there a lot more in SQL Server 2005):

http://msdn2.microsoft.com/en-us/library/ms179984.aspx

Here is some information from it about your specific items.

ONDEMAND_TASK_QUEUE waits for high priority requests (and high wait times simply indicates there have been none of these.)

KSOURCE_WAKEUP waits for requests from the Service Control Manager and long waits are to be expected (pause then unpause the SQL Server service to cause this to reset).

CHECKPOINT_QUEUE - This is the Checkpoint task waiting for the next checkpoint request (few checkpoints = large wait - and that's only 3 minutes).

BROKER_* - These are associated with the service broker and whilst it does not specifically say, I suspect if you do not have service broker servicing requests then these just wait and wait.

If you are interested in monitoring blocking then you might like to read this knowledge base article:

http://support.microsoft.com/kb/271509 - (LiveID required)

This describes sp_blocker_pss80 - a stored procedure to monitor blocked processes in SQL server 2000 up (it does however still produce these system processes in it's list).

new problem

I got the following error message when I do an insert trigger, can anyone
please help? Thanks.
Server: Msg 8115, Level 16, State 8, Procedure ADM_APRN_ins_upd_trg, Line
108
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.Pls post "ADM_APRN_ins_upd_trg" code, or continue using
imagination. Thanks.
Regards,
Alfred.
>--Original Message--
>I got the following error message when I do an insert
trigger, can anyone
>please help? Thanks.
>Server: Msg 8115, Level 16, State 8, Procedure
ADM_APRN_ins_upd_trg, Line
>108
>Arithmetic overflow error converting numeric to data type
numeric.
>The statement has been terminated.
>
>.
>|||Hello Alfred,
There is very little we can do here.
The problem as the error states on in line 108. I am going
to assume that it is some sort of maths function being
carried out. Whatever it is been converted into does not
like it.
Could you post that line and the 5 lines before and after
it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Pls post "ADM_APRN_ins_upd_trg" code, or continue using
>imagination. Thanks.
>Regards,
>Alfred.
>>--Original Message--
>>I got the following error message when I do an insert
>trigger, can anyone
>>please help? Thanks.
>>Server: Msg 8115, Level 16, State 8, Procedure
>ADM_APRN_ins_upd_trg, Line
>>108
>>Arithmetic overflow error converting numeric to data
type
>numeric.
>>The statement has been terminated.
>>
>>.
>.
>

New partition using XMLA

Hi,

I have an existing cube containing a single partition.

I am running the following xmla script to create a new partition :

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>myDB_id</DatabaseID>

<CubeID>mycube_id</CubeID>

<MeasureGroupID>mymg_id</MeasureGroupID>

</ParentObject>

<ObjectDefinition>

<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>mypartition_M1</ID>

<Name>mypartition_M1</Name>

<Annotations>

<Annotation>

<Name>AggregationPercent</Name>

<Value>30</Value>

</Annotation>

</Annotations>

<Source xsi:type="QueryBinding">

<DataSourceID>myDS_id</DataSourceID>

<QueryDefinition>

SELECT blabla...

</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<ProactiveCaching>

<SilenceInterval>-PT1S</SilenceInterval>

<Latency>-PT1S</Latency>

<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

<ForceRebuildInterval>-PT1S</ForceRebuildInterval>

<AggregationStorage>MolapOnly</AggregationStorage>

<Source xsi:type="ProactiveCachingInheritedBinding">

<NotificationTechnique>Server</NotificationTechnique>

</Source>

</ProactiveCaching>

<EstimatedRows>1000000</EstimatedRows>

<AggregationDesignID>AggregationDesign</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Create>

My partition is created and I can see it into SQL Server Management Studio.

I can process it as well and everything looks fine.

The only problem is that I cannot see the partition in BIDS which is quite strange....

Any ideas ?

Regards,

JL

BIDS works against project. Unless you made changes to the project - it won't see the changes on the live server. You can create new project by using "Import" option - i.e. create project from current server state. Or you can also work in "online mode" by doing File -> Open -> Analysis Services Database.|||

Mosha,

Thx a lot !

JL

New Job Sql Server Agent error *FIXED*

I am running SQL Server 2005 and recently installed Office Sharepoint Server 2007 on the same server. After the install I receive the following error message while trying to add a new SQL Server Agent Job.

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Also when I right click on a job to view properties a new job window pops up instead.

I realised that Office Sharepoint Server 2007 installs SQL Server Express and might me causing my issues. I uninstalled Sharepoint however I am still having the same problem.

Can anyone shed some light or have any suggestions?

Installing SP2 solved the problem.|||

I am having the same error when trying to create a job in SQLServer using SSIS package. The SSIS package does a very simple two steps ftp tasks: delete the existing file on the ftp server and copy the new one. Can somebody also share experience or idea on my case as well please.

Thanks

|||

I am have the same issue as Htin. I have run all Windows updates and I am running office 2007.

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

|||This problem seems to have been resolved by running the SQL Server SP2.|||

Hi I got the same error but after I install SP 2 for SQL 2005

Any idea how to resolve this problem ?:-)

|||I have succeeded in getting a job deployed but I accessed the Management Studio whilst on the Server so it looks like it's the tools not the server itself where the problem lies.|||I have SQL SERVER 2005 (SP2). I'm having the same problem. I upgraded the server via command prompt (c:/>setup SKUUPGRADE = 1). Still I'm having the same problem. Can any one tell me how to resolve this problem.

New Job Sql Server Agent error

I am running SQL Server 2005 and recently installed Office Sharepoint Server 2007 on the same server. After the install I receive the following error message while trying to add a new SQL Server Agent Job.

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Also when I right click on a job to view properties a new job window pops up instead.

I realised that Office Sharepoint Server 2007 installs SQL Server Express and might me causing my issues. I uninstalled Sharepoint however I am still having the same problem.

Can anyone shed some light or have any suggestions?

Installing SP2 solved the problem.|||

I am having the same error when trying to create a job in SQLServer using SSIS package. The SSIS package does a very simple two steps ftp tasks: delete the existing file on the ftp server and copy the new one. Can somebody also share experience or idea on my case as well please.

Thanks

|||

I am have the same issue as Htin. I have run all Windows updates and I am running office 2007.

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

|||This problem seems to have been resolved by running the SQL Server SP2.|||

Hi I got the same error but after I install SP 2 for SQL 2005

Any idea how to resolve this problem ?:-)

|||I have succeeded in getting a job deployed but I accessed the Management Studio whilst on the Server so it looks like it's the tools not the server itself where the problem lies.

New Job Sql Server Agent error

I am running SQL Server 2005 and recently installed Office Sharepoint Server 2007 on the same server. After the install I receive the following error message while trying to add a new SQL Server Agent Job.

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Also when I right click on a job to view properties a new job window pops up instead.

I realised that Office Sharepoint Server 2007 installs SQL Server Express and might me causing my issues. I uninstalled Sharepoint however I am still having the same problem.

Can anyone shed some light or have any suggestions?

Installing SP2 solved the problem.|||

I am having the same error when trying to create a job in SQLServer using SSIS package. The SSIS package does a very simple two steps ftp tasks: delete the existing file on the ftp server and copy the new one. Can somebody also share experience or idea on my case as well please.

Thanks

|||

I am have the same issue as Htin. I have run all Windows updates and I am running office 2007.

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

OK

|||This problem seems to have been resolved by running the SQL Server SP2.|||

Hi I got the same error but after I install SP 2 for SQL 2005

Any idea how to resolve this problem ?:-)

|||I have succeeded in getting a job deployed but I accessed the Management Studio whilst on the Server so it looks like it's the tools not the server itself where the problem lies.

New job opportunities

One of our clients is in need of following requirements .

OM Designer:-

Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 6

Skills
8 years of experience
Strong Functional Skills in Order Management and Distribution Modules.
Atleast 3 Implementation experience in Order Management.
Proven experience in designing Business solutions to Customer in OM
based on Business problems or requirements

OM & AR Techno-Functional:-

Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 6

Skills
6 years of experience
Exclusive Strong Techno-Functional Skills in Order Management and
Distribution Modules & Accounts Receivables modules.
Document Technical Designs, Unit Test Cases and Code.
Maintain Software written by yourself and others
Ensure Code is high Quality.
Good verbal and written communication skills
Solid software engineering skills.
Oracle Development tools - PLSQL, Forms, Reports, Workflow,XML.
Implementation Experience in Oracle Modules: OM, AR.
Nice to have Exposure to tools: Kintana, Teamtrack

OM & SCM Techno-Functional:-

Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 6

Skills
Strong Techno-Functional Skills in Order Management and Distribution
Modules & Supply Chain (Inv, PO, Shipping, AR) modules.
Document Technical Designs, Unit Test Cases and Code.
Maintain Software written by yourself and others
Ensure Code is high Quality.
Good verbal and written communication skills
Solid software engineering skills.
Oracle Development tools - PLSQL, Forms, Reports, Workflow,XML.
Implementation Experience in Oracle Modules: Inventory, Purchasing(Drop
Ship PO), Third Party Order Fulfillment, EDI Integration.
Nice to have Exposure to tools: Kintana, Teamtrack
6

I2C/AR Techno-Functional:-
Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 4

Skills
6 years of experience
Minimum 5 years experience in AR as Techno-Functional Consultant
Project experience in and comprehensive knowledge of AR tables and
APIs.
Functional understanding of the AR flow and able to translate into
technical terms
Functional understanding of the AR flow to GL (revenue recognition,
accounting entries) and able to translate into technical terms.
Functional understanding of invoice, credit memos, debit memos,
receipts and chargebacks and able to translate into technical terms
Project experience and comprehensive knowledge of AR Tables and APIs

F2M/P2P Techno-functional:-

Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 6

Skills
6 years of experience
strong Techno-Functional Skills in GL, PA, CM, FA, AP, PO, iPayables

Release Coordinator:-
Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 1
Skills
5 years of experience
Detailed knowledge and proven experience with Oracle AOL, Kintana and
VSS(configuration management).
1
Very Good Communication & interpersonal skills
Release management for non production environments(DEV, QA and UAT)
- Coordinating DEV and QA team for Kintana releases
- Kintana Package migration activities(code deployment)
- Submitting builds for migration
- Code Retrofit activities(planning and coordination only)
- Resolving release/code migration issues by discussing with
Engineering and SCM teams
Coordination and follow up with SCM(s/w configuration mgmt) teams for
Patches and environmental issues

Performance Tuning Expert:-

Location: Mountainview,CA
Duration: Long-term
Startdate: ASAP
No of positions: 1

Skills
6 years of experience
strong experience in oracle apps 11i performance tuning with expertise
in distribution and

ATG Dynamo Developer:-

Location: Minneapolis, MN
Duration: Long-term
Startdate: ASAP.

Skills
* Strong experience in JAVA
* ATG Dynamo App framework
* JSP
* Servlets
* Personalization
* Profile management.
* Good to have (ATG Portal Admin)
* Good Communication Skills

If you are interested in the above opportunity, please send your
updated resume with followingdetails

Full Name:
Availability:
Salary/rate:
Contact No.:
Current Location:
Visa Status:

Reply ASAP,
Thanks and Regards,
Kalyan Mudda
Spectraforce Technologies Inc.
919-846-7975 x 302
919-341-8358.
kmudda@.spectraforce.com
www.spectraforce.comRadhesh Reddy (radheshreddy@.gmail.com) writes:
> One of our clients is in need of following requirements .

I hope you don't plan to ask any SQL questions for the next 90 days. In
that case, I will not see them, as you just entered my kill file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are you phishing for useful info by any chance? Google & Oracle don't
recruit like that!

*** Sent via Developersdex http://www.developersdex.com ***

New Dimension

Hi!
I am a newbie, creating new Dimensions, Dimension structures, getting following error running a process; How would I fix this...
"the Dim_Salesperson was not found in the DW database on the server "

In Browser following sentence:
"Either the user, name, does not have access to the DW database or database does not exist."Sorry sorry... problem solved... :-)