Hi,
Could anyone tell, the way to automatically startup SQL
Server service and SQL Agent at mid-night in command line
in SQL Server 2000?
I tried to startup with command servr.exe in Binn
directory and seems like it stop the Launched startup
procedure 'sp_MScleanupmergepublisher'. How can I startup
SQL Server service without single user mode?
Thanks a lot.
-JennyMaybe:
net start mssqlserver
net start sqlserveragent
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>|||Hi,
NET START MSSQLSERVER
NETSTART SQLSERVERAGENT
Put the above commands inside a batch file and schedule to fire at midnight
using OS scheduler. If the service is already running then it will
give an error and exits.
Thanks
Hari
MCDBA
"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>|||"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>
In SQL 7.0 it was: net stop mssqlserver and net stop mssqlagent
I'll assume it's the same in SQL 2000, and it should be in Books On Line.
hth.
steve.sql
Showing posts with label line. Show all posts
Showing posts with label line. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to apply database design changes?
Hi all, I am not sure if this question has a one line answer or I have to
read an entire book on the subject. Here is my problem... I am developing VB
..NET based web app with MS SQL Server 2000 as backend. The beta version of
the system is already in use from the main server. Meanwhile I keep changing
the database design (including editing stored proc and/or views) on my
development server. Now how could I migrate the changes I have made to the
main server without loosing the data already there?
Any reference to an online guide would also be helpful.
Thanks in advance
Raj
Since you posted the question in .replication forum. I guess the first
question everyone has is:
Does the database set up for replication?
"Raj" wrote:
> Hi all, I am not sure if this question has a one line answer or I have to
> read an entire book on the subject. Here is my problem... I am developing VB
> .NET based web app with MS SQL Server 2000 as backend. The beta version of
> the system is already in use from the main server. Meanwhile I keep changing
> the database design (including editing stored proc and/or views) on my
> development server. Now how could I migrate the changes I have made to the
> main server without loosing the data already there?
> Any reference to an online guide would also be helpful.
> Thanks in advance
> --
> Raj
>
|||Jack, I was not sure initially where to post this question, so posted here. I
just created a publisher of my development database and selected all the four
objects (tables, stored proc, views, and UDFs) for publishing.
I do not want to publish the data, but only the schema. The replication
wizard gave me a warning that columns with INT IDENTITY property would be
converted to just INT, which, if happens, will screw up my database.
Any guidance would be appreciated
Thanks
"Jack" wrote:
[vbcol=seagreen]
> Since you posted the question in .replication forum. I guess the first
> question everyone has is:
> Does the database set up for replication?
> "Raj" wrote:
|||If this is the case, then I would not recommend Replication. Tables, views,
stored procedures, and functions all can create and alter using SQL script.
You just need to keep track of the changes you want to make, put them in a
script or scripts, then apply the changes to production database on regular
basics (i.e. once a week).
"Raj" wrote:
[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted here. I
> just created a publisher of my development database and selected all the four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Raj,
It seems either you got mixed up or I don't understand your problem.
1. What are you using replication for? Just to manage schema?
2. What type of replication are you using? My guess is you are using
Transactional.
3. How many servers are you managing in production?
4. Have you got your development and production servers mixed up? If yes,
bad idea!
Please clarify the above.
Normally if you want to deploy database changes from development to
production you would be keeping change scripts so you could apply them in an
ordered fashion, especially if table changes are involved. Adding columns
requires special handling when applying to replicated database, and cannot
be done using the conventional way. Please let me know your exact
requirements
Sorry for not being able to help more at this moment.
Raj Moloye.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted
> here. I
> just created a publisher of my development database and selected all the
> four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Thanks Raj Moloye and Jack.
I only need to migrate the schema changes from development server to main
server. I do no need to synchronize the data.
I only created replication agent for self-study (just to know what the heck
is it..). Based on your reply I guess replication is not the answer to my
problem.
You rightly mentioned that I need to place all the changes in a script, and
apply them to the main server regularly. I just do not know how to do it,
particularly the changes I make to the tables.
Please advise me the best way to achieve this.
Thanks again
BTW: could you also guide me to any online replication docoment?
"Khooseeraj Moloye" wrote:
> Raj,
> It seems either you got mixed up or I don't understand your problem.
> 1. What are you using replication for? Just to manage schema?
> 2. What type of replication are you using? My guess is you are using
> Transactional.
> 3. How many servers are you managing in production?
> 4. Have you got your development and production servers mixed up? If yes,
> bad idea!
> Please clarify the above.
> Normally if you want to deploy database changes from development to
> production you would be keeping change scripts so you could apply them in an
> ordered fashion, especially if table changes are involved. Adding columns
> requires special handling when applying to replicated database, and cannot
> be done using the conventional way. Please let me know your exact
> requirements
> Sorry for not being able to help more at this moment.
> Raj Moloye.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...
>
>
|||If you make changes to a table. For example, you added a column:
ALTER TABLE table
ADD column DATATYPE ........
As far as stored procedures, views, and functions. You can simple use the
ALTER command to update them unless your company has rules for those changes.
The difference between changing the schema of a table versus others is that
you don't want to change the entire schema of the table, you just want to
apply the necessary changes to it.
Book On Line provides a lot of informatioin on Replication already. This
forum is a great resource also especially there are some replication experts
checking this forum all the time.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Raj Moloye and Jack.
> I only need to migrate the schema changes from development server to main
> server. I do no need to synchronize the data.
> I only created replication agent for self-study (just to know what the heck
> is it..). Based on your reply I guess replication is not the answer to my
> problem.
> You rightly mentioned that I need to place all the changes in a script, and
> apply them to the main server regularly. I just do not know how to do it,
> particularly the changes I make to the tables.
> Please advise me the best way to achieve this.
> Thanks again
> BTW: could you also guide me to any online replication docoment?
> "Khooseeraj Moloye" wrote:
|||To replicate schema changes only, I'd recommend Redgate's
SQLCompare tool.
Rgds,
Paul Ibison
|||Thanks Jack,
I use enterprise manager to edit tables, views etc. I think it will be a
pain to record all the changes I make to the tables as a script manually. I
am probably making changes to the tables, views etc all the time, then how is
it possible/feasible to record them all in a script?
Is there any alternative?
"Jack" wrote:
[vbcol=seagreen]
> If you make changes to a table. For example, you added a column:
> ALTER TABLE table
> ADD column DATATYPE ........
> As far as stored procedures, views, and functions. You can simple use the
> ALTER command to update them unless your company has rules for those changes.
> The difference between changing the schema of a table versus others is that
> you don't want to change the entire schema of the table, you just want to
> apply the necessary changes to it.
> Book On Line provides a lot of informatioin on Replication already. This
> forum is a great resource also especially there are some replication experts
> checking this forum all the time.
> "Raj" wrote:
|||It is very important to record all the changes you want to make to your
production database even though it's a pain. That's why the DBA's like
myself still have jobs. :-) Views, stored procedures, and functions should
not be a problem. You can just right-click on the object from EM > All Tasks
> Generate SQL script. But when it comes to tables, I think it's better for
you to learn how to use T-SQL to make modifications. You can generate the
SQL script on the tables from EM also if you want to get yourself familiar
with the syntax first.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Jack,
> I use enterprise manager to edit tables, views etc. I think it will be a
> pain to record all the changes I make to the tables as a script manually. I
> am probably making changes to the tables, views etc all the time, then how is
> it possible/feasible to record them all in a script?
> Is there any alternative?
> "Jack" wrote:
read an entire book on the subject. Here is my problem... I am developing VB
..NET based web app with MS SQL Server 2000 as backend. The beta version of
the system is already in use from the main server. Meanwhile I keep changing
the database design (including editing stored proc and/or views) on my
development server. Now how could I migrate the changes I have made to the
main server without loosing the data already there?
Any reference to an online guide would also be helpful.
Thanks in advance
Raj
Since you posted the question in .replication forum. I guess the first
question everyone has is:
Does the database set up for replication?
"Raj" wrote:
> Hi all, I am not sure if this question has a one line answer or I have to
> read an entire book on the subject. Here is my problem... I am developing VB
> .NET based web app with MS SQL Server 2000 as backend. The beta version of
> the system is already in use from the main server. Meanwhile I keep changing
> the database design (including editing stored proc and/or views) on my
> development server. Now how could I migrate the changes I have made to the
> main server without loosing the data already there?
> Any reference to an online guide would also be helpful.
> Thanks in advance
> --
> Raj
>
|||Jack, I was not sure initially where to post this question, so posted here. I
just created a publisher of my development database and selected all the four
objects (tables, stored proc, views, and UDFs) for publishing.
I do not want to publish the data, but only the schema. The replication
wizard gave me a warning that columns with INT IDENTITY property would be
converted to just INT, which, if happens, will screw up my database.
Any guidance would be appreciated
Thanks
"Jack" wrote:
[vbcol=seagreen]
> Since you posted the question in .replication forum. I guess the first
> question everyone has is:
> Does the database set up for replication?
> "Raj" wrote:
|||If this is the case, then I would not recommend Replication. Tables, views,
stored procedures, and functions all can create and alter using SQL script.
You just need to keep track of the changes you want to make, put them in a
script or scripts, then apply the changes to production database on regular
basics (i.e. once a week).
"Raj" wrote:
[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted here. I
> just created a publisher of my development database and selected all the four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Raj,
It seems either you got mixed up or I don't understand your problem.
1. What are you using replication for? Just to manage schema?
2. What type of replication are you using? My guess is you are using
Transactional.
3. How many servers are you managing in production?
4. Have you got your development and production servers mixed up? If yes,
bad idea!
Please clarify the above.
Normally if you want to deploy database changes from development to
production you would be keeping change scripts so you could apply them in an
ordered fashion, especially if table changes are involved. Adding columns
requires special handling when applying to replicated database, and cannot
be done using the conventional way. Please let me know your exact
requirements
Sorry for not being able to help more at this moment.
Raj Moloye.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted
> here. I
> just created a publisher of my development database and selected all the
> four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Thanks Raj Moloye and Jack.
I only need to migrate the schema changes from development server to main
server. I do no need to synchronize the data.
I only created replication agent for self-study (just to know what the heck
is it..). Based on your reply I guess replication is not the answer to my
problem.
You rightly mentioned that I need to place all the changes in a script, and
apply them to the main server regularly. I just do not know how to do it,
particularly the changes I make to the tables.
Please advise me the best way to achieve this.
Thanks again
BTW: could you also guide me to any online replication docoment?
"Khooseeraj Moloye" wrote:
> Raj,
> It seems either you got mixed up or I don't understand your problem.
> 1. What are you using replication for? Just to manage schema?
> 2. What type of replication are you using? My guess is you are using
> Transactional.
> 3. How many servers are you managing in production?
> 4. Have you got your development and production servers mixed up? If yes,
> bad idea!
> Please clarify the above.
> Normally if you want to deploy database changes from development to
> production you would be keeping change scripts so you could apply them in an
> ordered fashion, especially if table changes are involved. Adding columns
> requires special handling when applying to replicated database, and cannot
> be done using the conventional way. Please let me know your exact
> requirements
> Sorry for not being able to help more at this moment.
> Raj Moloye.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...
>
>
|||If you make changes to a table. For example, you added a column:
ALTER TABLE table
ADD column DATATYPE ........
As far as stored procedures, views, and functions. You can simple use the
ALTER command to update them unless your company has rules for those changes.
The difference between changing the schema of a table versus others is that
you don't want to change the entire schema of the table, you just want to
apply the necessary changes to it.
Book On Line provides a lot of informatioin on Replication already. This
forum is a great resource also especially there are some replication experts
checking this forum all the time.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Raj Moloye and Jack.
> I only need to migrate the schema changes from development server to main
> server. I do no need to synchronize the data.
> I only created replication agent for self-study (just to know what the heck
> is it..). Based on your reply I guess replication is not the answer to my
> problem.
> You rightly mentioned that I need to place all the changes in a script, and
> apply them to the main server regularly. I just do not know how to do it,
> particularly the changes I make to the tables.
> Please advise me the best way to achieve this.
> Thanks again
> BTW: could you also guide me to any online replication docoment?
> "Khooseeraj Moloye" wrote:
|||To replicate schema changes only, I'd recommend Redgate's
SQLCompare tool.
Rgds,
Paul Ibison
|||Thanks Jack,
I use enterprise manager to edit tables, views etc. I think it will be a
pain to record all the changes I make to the tables as a script manually. I
am probably making changes to the tables, views etc all the time, then how is
it possible/feasible to record them all in a script?
Is there any alternative?
"Jack" wrote:
[vbcol=seagreen]
> If you make changes to a table. For example, you added a column:
> ALTER TABLE table
> ADD column DATATYPE ........
> As far as stored procedures, views, and functions. You can simple use the
> ALTER command to update them unless your company has rules for those changes.
> The difference between changing the schema of a table versus others is that
> you don't want to change the entire schema of the table, you just want to
> apply the necessary changes to it.
> Book On Line provides a lot of informatioin on Replication already. This
> forum is a great resource also especially there are some replication experts
> checking this forum all the time.
> "Raj" wrote:
|||It is very important to record all the changes you want to make to your
production database even though it's a pain. That's why the DBA's like
myself still have jobs. :-) Views, stored procedures, and functions should
not be a problem. You can just right-click on the object from EM > All Tasks
> Generate SQL script. But when it comes to tables, I think it's better for
you to learn how to use T-SQL to make modifications. You can generate the
SQL script on the tables from EM also if you want to get yourself familiar
with the syntax first.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Jack,
> I use enterprise manager to edit tables, views etc. I think it will be a
> pain to record all the changes I make to the tables as a script manually. I
> am probably making changes to the tables, views etc all the time, then how is
> it possible/feasible to record them all in a script?
> Is there any alternative?
> "Jack" wrote:
Monday, March 12, 2012
How to add if ~else to a exist Stored Procedure
here is my original Sql Code
I add a parameters @.CustId in line 13
when CustID not Null, I want to add this to the Where clause line 146 and 156
if CustId not null then where clause will add a reference like CustId = @.CustID
if CustId is Null then not reference @.custID
how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..
1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171
CustID=COALESCE(@.CustID,CustId)
COALESCE function returns the first non-null expression in its expression list.
Refer the below link for more information
http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
Wednesday, March 7, 2012
How to add a line/separator between groups?
Hi,
I have a simple report with multiple grouping. I like to add a separator between groups.
Some data (Group #1)
================= <-- separator line or color row or something
Some data (Group #2)
=================
Thanks.
You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!
|||What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.
hth
How to add a line/separator between groups?
Hi,
I have a simple report with multiple grouping. I like to add a separator between groups.
Some data (Group #1)
================= <-- separator line or color row or something
Some data (Group #2)
=================
Thanks.
You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!
|||What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.
hth
How to add a line chart while building report
This is my first time with Reporting Services. I was using the wizard and it
only had the options of Matrix or tabular.
I want to add a line chart instead. How can I do so ?
ThanksInstead of using the report wizard, create a blank report. Then add a chart
from the toolbox.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Hassan" <hassan@.test.com> wrote in message
news:OAbD2FjYIHA.6140@.TK2MSFTNGP02.phx.gbl...
> This is my first time with Reporting Services. I was using the wizard and
> it only had the options of Matrix or tabular.
> I want to add a line chart instead. How can I do so ?
> Thanks
only had the options of Matrix or tabular.
I want to add a line chart instead. How can I do so ?
ThanksInstead of using the report wizard, create a blank report. Then add a chart
from the toolbox.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Hassan" <hassan@.test.com> wrote in message
news:OAbD2FjYIHA.6140@.TK2MSFTNGP02.phx.gbl...
> This is my first time with Reporting Services. I was using the wizard and
> it only had the options of Matrix or tabular.
> I want to add a line chart instead. How can I do so ?
> Thanks
Sunday, February 19, 2012
How to access!
Hi everybody,
I want to know how can I access to SQL Server 7.0 (installed on windows 2000) from Other platforms line Win9X in a visual basic program.
Please tell me complete story,
1) What I have to do on server (windows 2000 - MSSQL 7.0)
2) What I have to do on clients (Windows 9X)
3) Connection string to connect to server from client in VB 6.0 language
Thanks :)Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
blindman|||Originally posted by blindman
Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
blindman
Just a question:
Is there any configuration nedded on Server and Client system?|||Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
Unatratnag
1) What I have to do on server (windows 2000 - MSSQL 7.0)
you have to create the database, and if you're doing odbc, then you'll need to create the odbc on the pc where the application resides
2) What I have to do on clients (Windows 9X)
write the code to connect to the database =P
3) Connection string to connect to server from client in VB 6.0 language
well if you have access to the database files create a .udl file and then double click it and it will allow you to create a connection string through the gui, open it back up in notepad and the connection string is the third line.|||People talking about ODBC are running behind for two Microsoft Data Access generations. After ODBC (related to DAO = Data Access Objects) came ADO (= ActiveX Data Objects) with its collection of OLE DB providers, needing as you asked connectionstrings. Currently, we are living in the ADO.NET age, however, you are working with VB 6, which means you would have to choose ADO.
You don't need any extra installations on your server.
On your Win9x client, you need to start with DCOM98. Furthermore, you need MDAC, preferably the latest version 2.7 SP1a. You may browse the www.keper.com (ftp://dbxprof:dbxprof@.FTP server of my DB Explorer for the installations, and you find some hints in the installation manual.
For a discussion of the connection string in trusted environments, or with DB security see ABLE Consulting (http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer). However, you may also want to build a connection string. Herefore, Microsoft utilizes Data Link Property forms, which can be accessed within your application.|||The FTP server link doesn't work. It is DB Explorer (ftp://dbxprof:dbxprof@.213.84.56.3/)|||setup an ADODB connection, use SQLOLEDB as the connection provider.|||I don't think there are Gurus here, just a bunch of people with no life :)
amirnezhad,
The topic may be big, but not the task. What is it you're trying to do? If you want to learn and looking for the way to start, then follow DoktorBlue's links. But if you were given a task to do a specific thing, then post it and someone may give you the whole answer.
P.S.: Since you're from Tehran, how are things looking from there?|||rocheyc: can't figure out the added vaue of your contribution, please help me.
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)|||Originally posted by DoktorBlue
rocheyc: can't figure out the added vaue of your contribution, please help me.
Funny, when I read your post I felt the same way about you!
Originally posted by DoktorBlue
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)
Yes, DoktorBlue the real qurus know.|||Thanks, Paul, you let me see that real gurus don't contribute to the topic, right?
I'd prefer to move further discussions in a new thread, or to keep it private.|||I see the p***ing contest is starting again. Well, I'll be here in the front row watching. Is it time to place bets yet?
I want to know how can I access to SQL Server 7.0 (installed on windows 2000) from Other platforms line Win9X in a visual basic program.
Please tell me complete story,
1) What I have to do on server (windows 2000 - MSSQL 7.0)
2) What I have to do on clients (Windows 9X)
3) Connection string to connect to server from client in VB 6.0 language
Thanks :)Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
blindman|||Originally posted by blindman
Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
blindman
Just a question:
Is there any configuration nedded on Server and Client system?|||Ali,
This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.
Your VB documentation should tell you how to go about getting data from outside datasources.
My short answer: ODBC connections.
Unatratnag
1) What I have to do on server (windows 2000 - MSSQL 7.0)
you have to create the database, and if you're doing odbc, then you'll need to create the odbc on the pc where the application resides
2) What I have to do on clients (Windows 9X)
write the code to connect to the database =P
3) Connection string to connect to server from client in VB 6.0 language
well if you have access to the database files create a .udl file and then double click it and it will allow you to create a connection string through the gui, open it back up in notepad and the connection string is the third line.|||People talking about ODBC are running behind for two Microsoft Data Access generations. After ODBC (related to DAO = Data Access Objects) came ADO (= ActiveX Data Objects) with its collection of OLE DB providers, needing as you asked connectionstrings. Currently, we are living in the ADO.NET age, however, you are working with VB 6, which means you would have to choose ADO.
You don't need any extra installations on your server.
On your Win9x client, you need to start with DCOM98. Furthermore, you need MDAC, preferably the latest version 2.7 SP1a. You may browse the www.keper.com (ftp://dbxprof:dbxprof@.FTP server of my DB Explorer for the installations, and you find some hints in the installation manual.
For a discussion of the connection string in trusted environments, or with DB security see ABLE Consulting (http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer). However, you may also want to build a connection string. Herefore, Microsoft utilizes Data Link Property forms, which can be accessed within your application.|||The FTP server link doesn't work. It is DB Explorer (ftp://dbxprof:dbxprof@.213.84.56.3/)|||setup an ADODB connection, use SQLOLEDB as the connection provider.|||I don't think there are Gurus here, just a bunch of people with no life :)
amirnezhad,
The topic may be big, but not the task. What is it you're trying to do? If you want to learn and looking for the way to start, then follow DoktorBlue's links. But if you were given a task to do a specific thing, then post it and someone may give you the whole answer.
P.S.: Since you're from Tehran, how are things looking from there?|||rocheyc: can't figure out the added vaue of your contribution, please help me.
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)|||Originally posted by DoktorBlue
rocheyc: can't figure out the added vaue of your contribution, please help me.
Funny, when I read your post I felt the same way about you!
Originally posted by DoktorBlue
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)
Yes, DoktorBlue the real qurus know.|||Thanks, Paul, you let me see that real gurus don't contribute to the topic, right?
I'd prefer to move further discussions in a new thread, or to keep it private.|||I see the p***ing contest is starting again. Well, I'll be here in the front row watching. Is it time to place bets yet?
Subscribe to:
Posts (Atom)