Help on how to 1 way push data

Hi there
Maybe Im missing something but..
I have a order tracking system running on sales peoples laptop's. When they connect to the company Network (via dialup) at Night I want to push all off there orders to a named SQL, and then remove those orders from their own laptop.
So in a sense, Its like a one way merge with a delete, or even a push from MSDE to the main SQL server.
I dont want existing data pushed back to the laptop.. SO its all one way.
Any ideas? or help
Ryan,
I have seen merge used for this - you can have a filter of 1=2 on the
tables. This ensures that subscriber inserts are replicated to teh
publisher, and then deletes are replicated back because the filter is not
satisfied. However, this can be a bit buggy if inserts are still carried out
on the subscriber while the merge agent is running (some of the new rows
won't be removed when next running the merge agent). Provided you can
prevent this, or it won't happen in your particular business, then this is a
viable solution.
Alternatively, each laptop could be configured as a transactional publisher
with HO as a central subscriber and for the delete command, 'none' is
entered to prevent the replication of deletes.
Finally, you could use DTS/linked servers to accomplish the same thing.
Regards,
Paul Ibison
|||Hi Paul
Thanks for your reply. Could you just explain the
> you can have a filter of 1=2 on the
> tables. This ensures that subscriber inserts are replicated to teh
> publisher, and then deletes are replicated back because the filter is not
> satisfied.
please as this sounds about the right way - as the users will not be adding new records at the time of syncronisation (they are basically using the Win Xp Syncronisation tool i.e. Annonymous.
|||Ryan,
in the filter section of the publication properties, on the filter rows tab
you specify this filter:
SELECT <published_columns> FROM [dbo].[yourtable] WHERE 2=1
HTH,
Paul Ibison

Help on Agent Login Change

Hello,
I need to change the SQLAgent start up login in roder to begin a
replication scenario.
I have been using "system account" byt now wish to change it to
another one I set up - Publish. Publish is an administartor account,
is in the Logins section, etc.
When I changed the SQLAgent and tried to restart it I received an
error as follow -
Could not start the SQLServerAgent service on Local Computer.
The service did not return an error. This could be an internal Windows
error or and internal service error.
If the problem persists, contact your system administrator.
When I tried to go back to the system account I got the same error and
now cannot start the SQLAgent.
Anyone have any suggestions on how to resolve this ?
THANK YOU in advance !
Jon SpartanWhat do you have in SQLAgent.out file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:23ltqvsp52v2gm1n22nflf100g41o6ddrv@.4ax.com...
> Hello,
> I need to change the SQLAgent start up login in roder to begin a
> replication scenario.
> I have been using "system account" byt now wish to change it to
> another one I set up - Publish. Publish is an administartor account,
> is in the Logins section, etc.
> When I changed the SQLAgent and tried to restart it I received an
> error as follow -
> Could not start the SQLServerAgent service on Local Computer.
> The service did not return an error. This could be an internal Windows
> error or and internal service error.
> If the problem persists, contact your system administrator.
> When I tried to go back to the system account I got the same error and
> now cannot start the SQLAgent.
> Anyone have any suggestions on how to resolve this ?
> THANK YOU in advance !
> Jon Spartan|||Contents of SQLAgent.Out are as follows -
11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
NT service control
11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
SQLServerAgent cannot start
11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
>Hello,
>I need to change the SQLAgent start up login in roder to begin a
>replication scenario.
>I have been using "system account" byt now wish to change it to
>another one I set up - Publish. Publish is an administartor account,
>is in the Logins section, etc.
>When I changed the SQLAgent and tried to restart it I received an
>error as follow -
>Could not start the SQLServerAgent service on Local Computer.
>The service did not return an error. This could be an internal Windows
>error or and internal service error.
>If the problem persists, contact your system administrator.
>When I tried to go back to the system account I got the same error and
>now cannot start the SQLAgent.
>Anyone have any suggestions on how to resolve this ?
>THANK YOU in advance !
>Jon Spartan|||That is strange. The error message suggests that Agent tries to login as
LocalSystem, and it cannot do that because that isn't added as a Windows
Login. LocaSystem should be there, and also you mention that you aren't
using LocalSystem. Sorry, beats me...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:jhbvqv08sbaehnl7lsrvk1ooa59090mpjf@.4ax.com...
> Contents of SQLAgent.Out are as follows -
> 11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
> NT service control
> 11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
> for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
> 11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
> SQLServerAgent cannot start
> 11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
>
> On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
> >Hello,
> >
> >I need to change the SQLAgent start up login in roder to begin a
> >replication scenario.
> >
> >I have been using "system account" byt now wish to change it to
> >another one I set up - Publish. Publish is an administartor account,
> >is in the Logins section, etc.
> >
> >When I changed the SQLAgent and tried to restart it I received an
> >error as follow -
> >
> >Could not start the SQLServerAgent service on Local Computer.
> >The service did not return an error. This could be an internal Windows
> >error or and internal service error.
> >If the problem persists, contact your system administrator.
> >
> >When I tried to go back to the system account I got the same error and
> >now cannot start the SQLAgent.
> >
> >Anyone have any suggestions on how to resolve this ?
> >
> >THANK YOU in advance !
> >
> >Jon Spartan
>

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.

Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.

Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

sql

Help Needed!! Error connecting to SQL 2005 EE via VB 2005 EE

System.Data.SqlClient.SqlException was unhandled
Class=20
ErrorCode=-2146232060
LineNumber=0
Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Number=2
Server=""
Source=".Net SqlClient Data Provider"
State=0

Hi guys, I have just set up a database in SQL 2005 Express Edition. However, I have a problem accessing it with the application program created in VB 2005 EE. Before using SQL 2005, the application worked perfectly in SQL 2000.
Here is my connection string used in SQL 2000:

Dim sqlConn As New SqlConnection("Data Source=(local); Database='Inventory List'; Integrated Security=yes")

Is this valid in SQL 2005 Express Edition? Or should I change the connection string instead? Thank you very much.

The normal connection string for express would be;

Dim sqlConn As New SqlConnection("Data Source=(local)\sqlexpress; Database='Inventory List'; Integrated Security=yes")

|||Did you enable remote connections for the SQL Express instance ? See the Screencast on my site for more information.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help needed making a tidy join query

(Simplfied for question asking purposes)
I have a help desk system with support categories and assignable
employees. I would like to run a query that will pull back a list of
all categories along with a count of unassigned tickets. So my tables
are:
tblSupportCategories
CategoryID int
CategoryName varchar(50)
tblSupportTickets
TicketID int
CategoryID int
SupportContact varchar(15) (default is null)
if I say
select tsc.categoryid,count(*)
from tblsupportcategories tsc
left outer join tblsupporttickets tst on tsc.categoryid =
tst.categoryid and tst.supportcontact is null
what I get is a list of all the support categories along with a non
zero number whereas what I want is something that looks like
Website 0
Office 2
E-Mail 1
Warranty 3
I suspect that an elegant query can be written to query this back in
one statement but I am at a loss as to how to do it. If anyone could
help, I certainly appreciate it.
TIAAssuming you really are using SQL Server (layout smells like Access) you cou
ld
do the following:
Select TSC.CategoryId, Count([TST.TicketId])
From tblSupportCategories As TSC
Left Join tblSupportTickets As TST
On TSC.CategoryId = TST.CategoryId
And TST.SupportContact Is Null
I'm assuming that TicketId is the PK of the tickets table (you didn't provid
e
DDL so I can't tell for sure).
Note that the criteria is in the Join clause not the Where clause. In this w
ay,
the tickets list will be filtered *before* it is joined to the categories ta
ble.
Also, by using Count([TST.TicketId]), I'm counting all ticket values where
support contact was null.
Thomas
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:50tl511ophieh5k8v1i1haiv7ls6ee4gi7@.
4ax.com...
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
> Website 0
> Office 2
> E-Mail 1
> Warranty 3
> I suspect that an elegant query can be written to query this back in
> one statement but I am at a loss as to how to do it. If anyone could
> help, I certainly appreciate it.
> TIA
>|||Matthew Speed wrote:
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
You do? That's not even a legal query ... you left out the Group By clause

> Website 0
> Office 2
> E-Mail 1
> Warranty 3
>
Sample data would have been helpful ... However, this should get you what
you want:
select 1 categoryid, 'Website' categoryname into #categories
union all
select 2,'Office'
union all
select 3,'Email'
union all
select 4,'Warranty'
select 1 ticketid,1 categoryid,'test1' supportcontact into #tickets
union all
select 2,2,'test'
union all
select 3,2,null
union all
select 4,3,null
union all
select 5,4,null
union all
select 6,4,null
union all
select 7,4,null
union all
select 8,2,null
select tsc.categoryid,coalesce(ticketcount,0)
unassignedtickets
from #categories tsc
left outer join (
select categoryid,count(*) ticketcount from
#tickets
where supportcontact is null
group by categoryid
) tst on tsc.categoryid =
tst.categoryid
drop table #categories
drop table #tickets
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Mon, 11 Apr 2005 18:53:22 -0400, "Bob Barrows [MVP]"
<reb01501@.NOyahoo.SPAMcom> wrote:

>Matthew Speed wrote:
>You do? That's not even a legal query ... you left out the Group By clause
>
That is not my actual query. I was trying to include a simplified
version of my real query and left out the group by clause in my
rewrite.|||
>Assuming you really are using SQL Server (layout smells like Access)
I have a SQL 7.0 MCDBA that I am currently upgrading to SQL 2K, thank
you very much. I haven't used Access to query anything since Access
97.

>Select TSC.CategoryId, Count([TST.TicketId])
>From tblSupportCategories As TSC
> Left Join tblSupportTickets As TST
> On TSC.CategoryId = TST.CategoryId
> And TST.SupportContact Is Null
>Note that the criteria is in the Join clause not the Where clause. In this
way,
>the tickets list will be filtered *before* it is joined to the categories table.[/c
olor]
I had this part.
>Also, by using Count([TST.TicketId]), I'm counting all ticket values where
>support contact was null.
>
This is what got me. I was trying to count(*). My original thinking
was that by doing a left outer join with the null clause in the join I
would get back a zero unless rows were matched on the right side of
the join. Unfortunately, the outer also got me a one count on every
left side row that didn't match on the right. Obviously that was
wrong but your solution worked great.
Thanks

Help Needed Disadter Recovery!

Our c drive has failed on our SQL Server following a major system crash!

We are going to have to re-install Windows and SQL Server.

Our backup failed last night.

When we re-install Windows we going to be able to see the drives in with our .mdf and .ldf file on them.

After we re-install sql server 2000 will we be able to run sp_dbattach to re-connect the mdf and ldf files?

Please help!

SimonYou will be able to atach database but if you had replication going on it will fail.

Help Needed - "," as decimal Point instead of "."

Hi All,
If we change the System settings to Swedish(Finland), the decimal space is a
Comma "," instead of a Dot "." Now the problem is SQL Server does not
recognize this and gives error. We can not do collation setting for numeric
fields also.
Any help on the same will be highly appreciated.
Regards
Rajib
hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> If we change the System settings to Swedish(Finland), the decimal space is
a
> Comma "," instead of a Dot "." Now the problem is SQL Server does not
> recognize this and gives error. We can not do collation setting for
numeric
> fields also.
SQL Server alwayse recognize decimal separator as "." (dot)... provide that
all your expressions are in this format
SET NOCOUNT ON
DECLARE @.n NUMERIC (12 , 5)
SELECT @.n = 1234567.12345
SELECT @.n AS [Number]
, CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
, CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
'#' END AS [Equal]
--<--
Number Cast Equal
-- -- --
1234567.12345 1234567.12345 =
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> If we change the System settings to Swedish(Finland), the decimal space is
a
> Comma "," instead of a Dot "." Now the problem is SQL Server does not
> recognize this and gives error. We can not do collation setting for
numeric
> fields also.
SQL Server alwayse recognize decimal separator as "." (dot)... provide that
all your expressions are in this format
SET NOCOUNT ON
DECLARE @.n NUMERIC (12 , 5)
SELECT @.n = 1234567.12345
SELECT @.n AS [Number]
, CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
, CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
'#' END AS [Equal]
--<--
Number Cast Equal
-- -- --
1234567.12345 1234567.12345 =
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Many Thanks!!
Can you please elaborate it further... We have an application front end
which is passing the data to Stored Procedure in the back end. Front end
changes the decimal places to COMMA automatically and try sending the same
data to back-end and SP fails to update the DB .
The problem is not only with COMMA but with anything apart from DOT "." ,
what SQL Server rejects. What would be the most effective way to develop of
a full proved system.
Regards
Rajib
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kbdd5Fc8tmU1@.uni-berlin.de...[vbcol=seagreen]
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
is
> a
> numeric
> SQL Server alwayse recognize decimal separator as "." (dot)... provide
that
> all your expressions are in this format
> SET NOCOUNT ON
> DECLARE @.n NUMERIC (12 , 5)
> SELECT @.n = 1234567.12345
> SELECT @.n AS [Number]
> , CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
> , CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
> '#' END AS [Equal]
> --<--
> Number Cast Equal
> -- -- --
> 1234567.12345 1234567.12345 =
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi Andrea,
Many Thanks!!
Can you please elaborate it further... We have an application front end
which is passing the data to Stored Procedure in the back end. Front end
changes the decimal places to COMMA automatically and try sending the same
data to back-end and SP fails to update the DB .
The problem is not only with COMMA but with anything apart from DOT "." ,
what SQL Server rejects. What would be the most effective way to develop of
a full proved system.
Regards
Rajib
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kbdd5Fc8tmU1@.uni-berlin.de...[vbcol=seagreen]
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
is
> a
> numeric
> SQL Server alwayse recognize decimal separator as "." (dot)... provide
that
> all your expressions are in this format
> SET NOCOUNT ON
> DECLARE @.n NUMERIC (12 , 5)
> SELECT @.n = 1234567.12345
> SELECT @.n AS [Number]
> , CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
> , CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
> '#' END AS [Equal]
> --<--
> Number Cast Equal
> -- -- --
> 1234567.12345 1234567.12345 =
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> Hi Andrea,
> Many Thanks!!
> Can you please elaborate it further...
answered privately...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> Hi Andrea,
> Many Thanks!!
> Can you please elaborate it further...
answered privately...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks a lot!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kdf5eF10epbU1@.uni-berlin.de...
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> answered privately...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?
master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
--
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

Help modifying MDX - Allow for Ownership Change in Team System

I am asking for your help solving a problem. I am very new to MDX and OLAP in general.

Here is my statement of the problem: Changing ownership of Bugs in Team System results in incorrect reporting of Completed Work.

I have crafted the following MDX , which works fine if Ownership of a Bug or Work Item is not changed.

=================================================================================

WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

(STRTOMember(@.prmStartDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

(STRTOMember(@.prmEndDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(

[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]

=================================================================================

The problem arises when anyone changes ownership of a bug (or work item).

I have used [Changed By].[Person].[Person] and [Assigned To].[Person].[Person] as the first element in the NONEMPTYCROSSJOIN, and neither return the proper result.

BTW: I know that NONEMPTYCROSSJOIN is depreciated, but I am unsure how to replace it.

Thanks in advance.

Hi Doug:

At first glance your MDX look sound. No obvious reason why changing Work Item ownership results in the wrong answer. Which leads me to think the issue is in the dimensional model, or fact table. Not in the MDX of your query. As an aside, you asked how to emulate the NONEMPTYCROSSJOIN with a different statement. I created an example below with two equivalent statements. One uses the NONEMPTYCROSSJOIN, and the other uses a FILTER() with a CROSSJOIN to give the same results. The samples work on the Adventure Works database.

To help track down the root of your changing ownership problem it will help to see the sample fact table and query result before the problem is created and after the problem is created.

Hope this helps - PGoldy

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS

,NONEMPTYCROSSJOIN([Customer].[Customer Geography].[France].Children

,

{[Date].[Calendar].[Calendar Year].&[2004]}, [Measures].[Internet Sales Amount], 2)

ON ROWS

FROM [Adventure Works]

WHERE (

[Product].[Product Categories].[Category].&[3]

)

////

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS

,FILTER(CROSSJOIN([Customer].[Customer Geography].[France].Children

,

{[Date].[Calendar].[Calendar Year].&[2004]}), [Measures].[Internet Sales Amount] <> 0)

ON ROWS

FROM [Adventure Works]

WHERE (

[Product].[Product Categories].[Category].&[3]

)

Help me!emergency!

dear:

can anyone tell how to connect to Microsoft Mobile Server 2005 using System.Data.SqlceConnection or using System.oledbConnection connect to Microsoft Access DataBase in Pocket Pc or windows CE program?

dose there any minimum requirements i need to install?would you like to list a list for me

thanks alot.

If you are using Visual Studio 2005 that has Smart Device programming support, you are ready to develop against SQL Server Mobile 2005.

If not, then this link describes the installation for SQL Server Mobile 2005: http://msdn2.microsoft.com/en-us/library/ms171938(SQL.90).aspx

For developing SQLMobile applications using Visual Studio 2005 you can refer:

http://msdn2.microsoft.com/en-us/7bx62e55(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/kckakk5w(VS.80).aspx

SQL Mobile programming references to help you out:

http://msdn2.microsoft.com/en-us/library/ms174628(SQL.90).aspx. This link walks your through writing a smart device application that creates a new SQL Mobile database.

http://msdn2.microsoft.com/en-us/library/ms174462(SQL.90).aspx: This link is the SQL Server Mobile .NET programming reference. The documentation for System.Data.SqlServerCe classes can help you program to connect and access SQL Mobile in a device application.

Please let me know if this can help you get started.

Help me in writing sql query

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this query

ThanksWell, if all you're logging is when someone logged in, the only thing I can see you being able to get is the count of how many people logged in on a given date.
How long can people keep their connections? Overnight?
Unless you've also kept track of when they logged out, I don't see any way to know how many people were logged in at a given time. So I don't see a way to get the minimum, maximum or average.

help me in sql server codes

Hi,

I am new member here.
I have post system , and the items sends from sender to receiver.
The item takes from 24 hors to 168 hours to reach to the receiver.
I use MS SQL SERVER 2000, I have database (EMS_IN_REPORT)
And seven fields.
The field EVENT_TYPE has many letters, for example: A, C, D, H AND I.
I want make query include the following:
1- every field (ITEM_IDENTIFICATION) must has letters c and d in the
(EVENT_TYPE) field.
2-the total number of items.
3-make percentage for every type:24,48,72,96,120,144,168
4-make the total number for every type.

As the following picture:

and the final result must entry in this table:

Please help me and thank you for all.
Regards,
th goldwhy no any reply?!!!

please help me .|||please any one can help me????