A client I'm producing several reports for wants the spacing between the
lines of text to be less and I haven't any ideas other than this property to
get it to happen.
Any suggestions would be AWESOME!
Thanks,
DiaDia
I use tables alot and the height property works very well in a table. You
can also adjust the distance between rows with the padding property.
"Dia" wrote:
> A client I'm producing several reports for wants the spacing between the
> lines of text to be less and I haven't any ideas other than this property to
> get it to happen.
> Any suggestions would be AWESOME!
> Thanks,
> Dia
2012年3月27日星期二
2012年2月23日星期四
Any Ideas? SQL teaser
A little SQL problem I'm having –
I want to update the MaxInd and MinInd columns of a table to indicate which
rows have minimum or maximum event date for a given URN.
It is important that only one record per URN be marked as the Maximum or
Minimum but it is possible that a single event may be both the maximum AND
the minimum
This problem is quite simple to solve if event dates are all unique;
problems start to occur when multiple minimum or maximum dates exist in this
list
Consider the table below which for the moment has no ‘problem’ entries
Tables
Event
ID URN EventDate MaxInd MinInd
-- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
Values for the MaxInd and MinInd columns where supplied using the following
update
UPDATE Event
SET
MaxInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END,
MinInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END
FROM (
SELECT MAX(EventDate) AS MaxDate, MIN(EventDate) AS MinDate, URN
FROM Event
GROUP BY URN
) AS Ranges
WHERE Event.URN = Ranges.URN
Now lets update the Event table to introduce some problem events and the
values for MaxInd and MinInd I would like –
ID URN EventDate MaxInd MinInd Notes
-- -- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
7 1 01/01/2006 N N Same event date
as ID 1
8 2 08/01/2006 N N Same event date
as ID 4
9 3 08/01/2006 N N Same event date
as ID 6
When using the SQL presented above, this data scenario will result in the
three new records being marked with a MaxInd or MinInd in addition to their
related events also being marked as MinInd or MaxInd.
This situate breaks the rule that only one event per URN can be either the
Maximum or MinimumStephen wrote:
> This situate breaks the rule that only one event per URN can be either the
> Maximum or Minimum
... and therefore your requirements specification is incomplete. You
said you only want to update one row but you haven't told us which one
it should be. SQL isn't good at stuff like "Give me any one row. I
don't care which". Even if it were easier solve, code that returns
random results tends to look like a bug to end users and testers. For
that reason, you should always query an ambiguity like this one by
going back to the user, business owner or whoever.
Also, please post a CREATE TABLE statement for the table. That way we
don't have to guess what the actual datatypes and constraints are and
we can test out possible solutions for you. Don't forget to include
keys. Presumably you do have some keys to work with? I don't like to
make too many assumptions without seeing the table structure but on the
face of it the table design looks weak. I'd suggest you eliminate the
duplicate data rather than write lots of complex code to cope with it.
Finally, do tell us what version of SQL Server you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Also, the maximum and minimum should be functions of the data in your table,
and not stored as data themselves. They are results of a query and should
be determined when you need to access them, not stored and physically
updated. Of course this is a rule of database design, and sometimes there
are reasons (i.e. performance) for breaking these rules. Usually, however,
you can follow the rules and still get good performance, if you are careful
with how you write your SQL queries and how you index your columns.
You should consider either creating a unique constraint that prevents these
duplicate dates, or adding a sequence column to differentiate them.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143818532.404505.236730@.i40g2000cwc.googlegroups.com...
> Stephen wrote:
the
> ... and therefore your requirements specification is incomplete. You
> said you only want to update one row but you haven't told us which one
> it should be. SQL isn't good at stuff like "Give me any one row. I
> don't care which". Even if it were easier solve, code that returns
> random results tends to look like a bug to end users and testers. For
> that reason, you should always query an ambiguity like this one by
> going back to the user, business owner or whoever.
> Also, please post a CREATE TABLE statement for the table. That way we
> don't have to guess what the actual datatypes and constraints are and
> we can test out possible solutions for you. Don't forget to include
> keys. Presumably you do have some keys to work with? I don't like to
> make too many assumptions without seeing the table structure but on the
> face of it the table design looks weak. I'd suggest you eliminate the
> duplicate data rather than write lots of complex code to cope with it.
> Finally, do tell us what version of SQL Server you are using.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I want to update the MaxInd and MinInd columns of a table to indicate which
rows have minimum or maximum event date for a given URN.
It is important that only one record per URN be marked as the Maximum or
Minimum but it is possible that a single event may be both the maximum AND
the minimum
This problem is quite simple to solve if event dates are all unique;
problems start to occur when multiple minimum or maximum dates exist in this
list
Consider the table below which for the moment has no ‘problem’ entries
Tables
Event
ID URN EventDate MaxInd MinInd
-- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
Values for the MaxInd and MinInd columns where supplied using the following
update
UPDATE Event
SET
MaxInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END,
MinInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END
FROM (
SELECT MAX(EventDate) AS MaxDate, MIN(EventDate) AS MinDate, URN
FROM Event
GROUP BY URN
) AS Ranges
WHERE Event.URN = Ranges.URN
Now lets update the Event table to introduce some problem events and the
values for MaxInd and MinInd I would like –
ID URN EventDate MaxInd MinInd Notes
-- -- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
7 1 01/01/2006 N N Same event date
as ID 1
8 2 08/01/2006 N N Same event date
as ID 4
9 3 08/01/2006 N N Same event date
as ID 6
When using the SQL presented above, this data scenario will result in the
three new records being marked with a MaxInd or MinInd in addition to their
related events also being marked as MinInd or MaxInd.
This situate breaks the rule that only one event per URN can be either the
Maximum or MinimumStephen wrote:
> This situate breaks the rule that only one event per URN can be either the
> Maximum or Minimum
... and therefore your requirements specification is incomplete. You
said you only want to update one row but you haven't told us which one
it should be. SQL isn't good at stuff like "Give me any one row. I
don't care which". Even if it were easier solve, code that returns
random results tends to look like a bug to end users and testers. For
that reason, you should always query an ambiguity like this one by
going back to the user, business owner or whoever.
Also, please post a CREATE TABLE statement for the table. That way we
don't have to guess what the actual datatypes and constraints are and
we can test out possible solutions for you. Don't forget to include
keys. Presumably you do have some keys to work with? I don't like to
make too many assumptions without seeing the table structure but on the
face of it the table design looks weak. I'd suggest you eliminate the
duplicate data rather than write lots of complex code to cope with it.
Finally, do tell us what version of SQL Server you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Also, the maximum and minimum should be functions of the data in your table,
and not stored as data themselves. They are results of a query and should
be determined when you need to access them, not stored and physically
updated. Of course this is a rule of database design, and sometimes there
are reasons (i.e. performance) for breaking these rules. Usually, however,
you can follow the rules and still get good performance, if you are careful
with how you write your SQL queries and how you index your columns.
You should consider either creating a unique constraint that prevents these
duplicate dates, or adding a sequence column to differentiate them.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143818532.404505.236730@.i40g2000cwc.googlegroups.com...
> Stephen wrote:
the
> ... and therefore your requirements specification is incomplete. You
> said you only want to update one row but you haven't told us which one
> it should be. SQL isn't good at stuff like "Give me any one row. I
> don't care which". Even if it were easier solve, code that returns
> random results tends to look like a bug to end users and testers. For
> that reason, you should always query an ambiguity like this one by
> going back to the user, business owner or whoever.
> Also, please post a CREATE TABLE statement for the table. That way we
> don't have to guess what the actual datatypes and constraints are and
> we can test out possible solutions for you. Don't forget to include
> keys. Presumably you do have some keys to work with? I don't like to
> make too many assumptions without seeing the table structure but on the
> face of it the table design looks weak. I'd suggest you eliminate the
> duplicate data rather than write lots of complex code to cope with it.
> Finally, do tell us what version of SQL Server you are using.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
any ideas?
I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
any ideas?
I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
Thanks
Hi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
Thanks
Hi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
any ideas?
I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go o
ut
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take th
e
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncroniz
e
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in th
e
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go o
ut
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take th
e
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncroniz
e
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in th
e
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>
Any ideas, please...
I have create a database, login, role, user at a named instance of MSDE by
running the following scropt:
CREATE DATABASE [testdb_vg]
USE Master
EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
USE [testdb_vg]
EXEC SP_ADDROLE [test_role]
GRANT CREATE TABLE TO [test_role]
EXEC SP_ADDUSER [test_login], [test_user], [test_role]
Then I run SQL Analyser and try to connect to the database using SQL
Authentication:
user: test_user
password: psw
and I am getting message:
Unable to connect to server SSSSSS\NNNN
Message: 18452, level 16, state 1,
[Microsoft][ODBC SQL Server Driver][SQL Server] Login for user "test_user".
Reason: not associated with a trusted SQL Server connection
with other SQL server instance it works OK?!Are you sure MSDE is set up to use both SQL Server and Windows
authentication? Sounds like it is set to Windows authentication only...
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||See the section that starts with "Another way to change the security mode
after installation..." in
http://support.microsoft.com/?kbid=285097
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||In my case MSDE was set up to use Windows authentication only...|||is there any SQL query way to recognize what the mode is for an instance of
SQL Server?
running the following scropt:
CREATE DATABASE [testdb_vg]
USE Master
EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
USE [testdb_vg]
EXEC SP_ADDROLE [test_role]
GRANT CREATE TABLE TO [test_role]
EXEC SP_ADDUSER [test_login], [test_user], [test_role]
Then I run SQL Analyser and try to connect to the database using SQL
Authentication:
user: test_user
password: psw
and I am getting message:
Unable to connect to server SSSSSS\NNNN
Message: 18452, level 16, state 1,
[Microsoft][ODBC SQL Server Driver][SQL Server] Login for user "test_user".
Reason: not associated with a trusted SQL Server connection
with other SQL server instance it works OK?!Are you sure MSDE is set up to use both SQL Server and Windows
authentication? Sounds like it is set to Windows authentication only...
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||See the section that starts with "Another way to change the security mode
after installation..." in
http://support.microsoft.com/?kbid=285097
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||In my case MSDE was set up to use Windows authentication only...|||is there any SQL query way to recognize what the mode is for an instance of
SQL Server?
Any ideas on this one ?
I have a Datetime parameter from a dataset. It also shows the time but I
only need the date, is it possible somehow to change how it displays the
parameter. As what I can see it is only possible to choose datetime format.
I tried to convert it like this convert(datetime,period,105) as dateonly and
then use this field but it shows exactly the same !
JackI ended up writing a sql function to do this.
Basically the function subtracts the hours , minutes and seconds using
the DateAdd statement. There vb equivalents to this to - look up date
functions in BOL.
Chris
Jack Nielsen wrote:
> I have a Datetime parameter from a dataset. It also shows the time
> but I only need the date, is it possible somehow to change how it
> displays the parameter. As what I can see it is only possible to
> choose datetime format. I tried to convert it like this
> convert(datetime,period,105) as dateonly and then use this field but
> it shows exactly the same !
> Jack|||Could this be used somehow, and if how do I show this new field in the
Parameter and still use the datetime field in the sql statement ?
USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
DROP FUNCTION [dbo].[udf_MyDate]
GO
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
This is the statement where i include the parameter
HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
@.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
And it now shows like this 12/01/2005 00:00:00 in the report parameter,
would like it only to show 12/01/2005 but still using a real datetime format
so that the statement still works.
Jack
> I ended up writing a sql function to do this.
> Basically the function subtracts the hours , minutes and seconds using
> the DateAdd statement. There vb equivalents to this to - look up date
> functions in BOL.
> Chris
> Jack Nielsen wrote:
> > I have a Datetime parameter from a dataset. It also shows the time
> > but I only need the date, is it possible somehow to change how it
> > displays the parameter. As what I can see it is only possible to
> > choose datetime format. I tried to convert it like this
> > convert(datetime,period,105) as dateonly and then use this field but
> > it shows exactly the same !
> >
> > Jack
>|||Can you not just return the datetime from the dataset and format it in the
layout of the report using an expression (=Format(Fields!dateTimeField.Value,
"MMM dd, yyyy"))? Am I missing something in your requirements?
MKD
"Jack Nielsen" wrote:
> Could this be used somehow, and if how do I show this new field in the
> Parameter and still use the datetime field in the sql statement ?
> USE Northwind
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id => object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> DROP FUNCTION [dbo].[udf_MyDate]
> GO
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> This is the statement where i include the parameter
> HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
> @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> And it now shows like this 12/01/2005 00:00:00 in the report parameter,
> would like it only to show 12/01/2005 but still using a real datetime format
> so that the statement still works.
> Jack
> > I ended up writing a sql function to do this.
> > Basically the function subtracts the hours , minutes and seconds using
> > the DateAdd statement. There vb equivalents to this to - look up date
> > functions in BOL.
> >
> > Chris
> >
> > Jack Nielsen wrote:
> >
> > > I have a Datetime parameter from a dataset. It also shows the time
> > > but I only need the date, is it possible somehow to change how it
> > > displays the parameter. As what I can see it is only possible to
> > > choose datetime format. I tried to convert it like this
> > > convert(datetime,period,105) as dateonly and then use this field but
> > > it shows exactly the same !
> > >
> > > Jack
> >
>
>|||Ducky,
This is a date from a dataset, being used to populate a parameter list.
This is why we have to be a bit more convaluted.
Jack,
In your dataset do something like this;
Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
In your parameter set MyDate as the Value and Label as the Label.
Chris
ducky wrote:
> Can you not just return the datetime from the dataset and format it
> in the layout of the report using an expression
> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
> something in your requirements?
> MKD
>
> "Jack Nielsen" wrote:
> > Could this be used somehow, and if how do I show this new field in
> > the Parameter and still use the datetime field in the sql statement
> > ?
> >
> > USE Northwind
> > GO
> > IF EXISTS (SELECT * FROM sysobjects WHERE id => > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> > DROP FUNCTION [dbo].[udf_MyDate]
> > GO
> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> > RETURNS Nchar(20)
> > AS
> > BEGIN
> > RETURN
> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> > END
> > GO
> >
> > This is the statement where i include the parameter
> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> >
> > And it now shows like this 12/01/2005 00:00:00 in the report
> > parameter, would like it only to show 12/01/2005 but still using a
> > real datetime format so that the statement still works.
> >
> > Jack
> >
> > > I ended up writing a sql function to do this.
> > > Basically the function subtracts the hours , minutes and seconds
> > > using the DateAdd statement. There vb equivalents to this to -
> > > look up date functions in BOL.
> > >
> > > Chris
> > >
> > > Jack Nielsen wrote:
> > >
> > > > I have a Datetime parameter from a dataset. It also shows the
> > > > time but I only need the date, is it possible somehow to change
> > > > how it displays the parameter. As what I can see it is only
> > > > possible to choose datetime format. I tried to convert it like
> > > > this convert(datetime,period,105) as dateonly and then use this
> > > > field but it shows exactly the same !
> > > >
> > > > Jack
> > >
> >
> >
> >|||If your RS parameter is set to a string, the original
"convert(datetime,period,105)" should give you the listing you desire. When
this parameter is then passed to SQL it "should" automatically be recognized
as a date, but if not, you could pass the parameter as as string, and then
declare and set a new SQL parameter to the cast(@.param as datetime).
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:eP03NtUiFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Ducky,
> This is a date from a dataset, being used to populate a parameter list.
> This is why we have to be a bit more convaluted.
> Jack,
> In your dataset do something like this;
> Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
> In your parameter set MyDate as the Value and Label as the Label.
> Chris
>
> ducky wrote:
>> Can you not just return the datetime from the dataset and format it
>> in the layout of the report using an expression
>> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
>> something in your requirements?
>> MKD
>>
>> "Jack Nielsen" wrote:
>> > Could this be used somehow, and if how do I show this new field in
>> > the Parameter and still use the datetime field in the sql statement
>> > ?
>> >
>> > USE Northwind
>> > GO
>> > IF EXISTS (SELECT * FROM sysobjects WHERE id =>> > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
>> > DROP FUNCTION [dbo].[udf_MyDate]
>> > GO
>> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> > RETURNS Nchar(20)
>> > AS
>> > BEGIN
>> > RETURN
>> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> > END
>> > GO
>> >
>> > This is the statement where i include the parameter
>> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
>> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
>> >
>> > And it now shows like this 12/01/2005 00:00:00 in the report
>> > parameter, would like it only to show 12/01/2005 but still using a
>> > real datetime format so that the statement still works.
>> >
>> > Jack
>> >
>> > > I ended up writing a sql function to do this.
>> > > Basically the function subtracts the hours , minutes and seconds
>> > > using the DateAdd statement. There vb equivalents to this to -
>> > > look up date functions in BOL.
>> > >
>> > > Chris
>> > >
>> > > Jack Nielsen wrote:
>> > >
>> > > > I have a Datetime parameter from a dataset. It also shows the
>> > > > time but I only need the date, is it possible somehow to change
>> > > > how it displays the parameter. As what I can see it is only
>> > > > possible to choose datetime format. I tried to convert it like
>> > > > this convert(datetime,period,105) as dateonly and then use this
>> > > > field but it shows exactly the same !
>> > > >
>> > > > Jack
>> > >
>> >
>> >
>> >
>|||> If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack
only need the date, is it possible somehow to change how it displays the
parameter. As what I can see it is only possible to choose datetime format.
I tried to convert it like this convert(datetime,period,105) as dateonly and
then use this field but it shows exactly the same !
JackI ended up writing a sql function to do this.
Basically the function subtracts the hours , minutes and seconds using
the DateAdd statement. There vb equivalents to this to - look up date
functions in BOL.
Chris
Jack Nielsen wrote:
> I have a Datetime parameter from a dataset. It also shows the time
> but I only need the date, is it possible somehow to change how it
> displays the parameter. As what I can see it is only possible to
> choose datetime format. I tried to convert it like this
> convert(datetime,period,105) as dateonly and then use this field but
> it shows exactly the same !
> Jack|||Could this be used somehow, and if how do I show this new field in the
Parameter and still use the datetime field in the sql statement ?
USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
DROP FUNCTION [dbo].[udf_MyDate]
GO
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
This is the statement where i include the parameter
HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
@.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
And it now shows like this 12/01/2005 00:00:00 in the report parameter,
would like it only to show 12/01/2005 but still using a real datetime format
so that the statement still works.
Jack
> I ended up writing a sql function to do this.
> Basically the function subtracts the hours , minutes and seconds using
> the DateAdd statement. There vb equivalents to this to - look up date
> functions in BOL.
> Chris
> Jack Nielsen wrote:
> > I have a Datetime parameter from a dataset. It also shows the time
> > but I only need the date, is it possible somehow to change how it
> > displays the parameter. As what I can see it is only possible to
> > choose datetime format. I tried to convert it like this
> > convert(datetime,period,105) as dateonly and then use this field but
> > it shows exactly the same !
> >
> > Jack
>|||Can you not just return the datetime from the dataset and format it in the
layout of the report using an expression (=Format(Fields!dateTimeField.Value,
"MMM dd, yyyy"))? Am I missing something in your requirements?
MKD
"Jack Nielsen" wrote:
> Could this be used somehow, and if how do I show this new field in the
> Parameter and still use the datetime field in the sql statement ?
> USE Northwind
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id => object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> DROP FUNCTION [dbo].[udf_MyDate]
> GO
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> This is the statement where i include the parameter
> HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
> @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> And it now shows like this 12/01/2005 00:00:00 in the report parameter,
> would like it only to show 12/01/2005 but still using a real datetime format
> so that the statement still works.
> Jack
> > I ended up writing a sql function to do this.
> > Basically the function subtracts the hours , minutes and seconds using
> > the DateAdd statement. There vb equivalents to this to - look up date
> > functions in BOL.
> >
> > Chris
> >
> > Jack Nielsen wrote:
> >
> > > I have a Datetime parameter from a dataset. It also shows the time
> > > but I only need the date, is it possible somehow to change how it
> > > displays the parameter. As what I can see it is only possible to
> > > choose datetime format. I tried to convert it like this
> > > convert(datetime,period,105) as dateonly and then use this field but
> > > it shows exactly the same !
> > >
> > > Jack
> >
>
>|||Ducky,
This is a date from a dataset, being used to populate a parameter list.
This is why we have to be a bit more convaluted.
Jack,
In your dataset do something like this;
Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
In your parameter set MyDate as the Value and Label as the Label.
Chris
ducky wrote:
> Can you not just return the datetime from the dataset and format it
> in the layout of the report using an expression
> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
> something in your requirements?
> MKD
>
> "Jack Nielsen" wrote:
> > Could this be used somehow, and if how do I show this new field in
> > the Parameter and still use the datetime field in the sql statement
> > ?
> >
> > USE Northwind
> > GO
> > IF EXISTS (SELECT * FROM sysobjects WHERE id => > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> > DROP FUNCTION [dbo].[udf_MyDate]
> > GO
> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> > RETURNS Nchar(20)
> > AS
> > BEGIN
> > RETURN
> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> > END
> > GO
> >
> > This is the statement where i include the parameter
> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> >
> > And it now shows like this 12/01/2005 00:00:00 in the report
> > parameter, would like it only to show 12/01/2005 but still using a
> > real datetime format so that the statement still works.
> >
> > Jack
> >
> > > I ended up writing a sql function to do this.
> > > Basically the function subtracts the hours , minutes and seconds
> > > using the DateAdd statement. There vb equivalents to this to -
> > > look up date functions in BOL.
> > >
> > > Chris
> > >
> > > Jack Nielsen wrote:
> > >
> > > > I have a Datetime parameter from a dataset. It also shows the
> > > > time but I only need the date, is it possible somehow to change
> > > > how it displays the parameter. As what I can see it is only
> > > > possible to choose datetime format. I tried to convert it like
> > > > this convert(datetime,period,105) as dateonly and then use this
> > > > field but it shows exactly the same !
> > > >
> > > > Jack
> > >
> >
> >
> >|||If your RS parameter is set to a string, the original
"convert(datetime,period,105)" should give you the listing you desire. When
this parameter is then passed to SQL it "should" automatically be recognized
as a date, but if not, you could pass the parameter as as string, and then
declare and set a new SQL parameter to the cast(@.param as datetime).
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:eP03NtUiFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Ducky,
> This is a date from a dataset, being used to populate a parameter list.
> This is why we have to be a bit more convaluted.
> Jack,
> In your dataset do something like this;
> Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
> In your parameter set MyDate as the Value and Label as the Label.
> Chris
>
> ducky wrote:
>> Can you not just return the datetime from the dataset and format it
>> in the layout of the report using an expression
>> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
>> something in your requirements?
>> MKD
>>
>> "Jack Nielsen" wrote:
>> > Could this be used somehow, and if how do I show this new field in
>> > the Parameter and still use the datetime field in the sql statement
>> > ?
>> >
>> > USE Northwind
>> > GO
>> > IF EXISTS (SELECT * FROM sysobjects WHERE id =>> > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
>> > DROP FUNCTION [dbo].[udf_MyDate]
>> > GO
>> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> > RETURNS Nchar(20)
>> > AS
>> > BEGIN
>> > RETURN
>> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> > END
>> > GO
>> >
>> > This is the statement where i include the parameter
>> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
>> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
>> >
>> > And it now shows like this 12/01/2005 00:00:00 in the report
>> > parameter, would like it only to show 12/01/2005 but still using a
>> > real datetime format so that the statement still works.
>> >
>> > Jack
>> >
>> > > I ended up writing a sql function to do this.
>> > > Basically the function subtracts the hours , minutes and seconds
>> > > using the DateAdd statement. There vb equivalents to this to -
>> > > look up date functions in BOL.
>> > >
>> > > Chris
>> > >
>> > > Jack Nielsen wrote:
>> > >
>> > > > I have a Datetime parameter from a dataset. It also shows the
>> > > > time but I only need the date, is it possible somehow to change
>> > > > how it displays the parameter. As what I can see it is only
>> > > > possible to choose datetime format. I tried to convert it like
>> > > > this convert(datetime,period,105) as dateonly and then use this
>> > > > field but it shows exactly the same !
>> > > >
>> > > > Jack
>> > >
>> >
>> >
>> >
>|||> If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack
Any ideas on this one ?
If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
JackJack, recommend you check Books on Line. Here's what it says:"Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )"
Style is very useful here; you may need to experiment with it until you get
the output you want. By using this, you won't need a function. You can use
the convert statement in your query.
I hope this helps you.
Henry Robinette
"Jack Nielsen" wrote:
> If your RS parameter is set to a string, the original
> > "convert(datetime,period,105)" should give you the listing you desire.
> When
> > this parameter is then passed to SQL it "should" automatically be
> recognized
> > as a date, but if not, you could pass the parameter as as string, and then
> > declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
JackJack, recommend you check Books on Line. Here's what it says:"Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )"
Style is very useful here; you may need to experiment with it until you get
the output you want. By using this, you won't need a function. You can use
the convert statement in your query.
I hope this helps you.
Henry Robinette
"Jack Nielsen" wrote:
> If your RS parameter is set to a string, the original
> > "convert(datetime,period,105)" should give you the listing you desire.
> When
> > this parameter is then passed to SQL it "should" automatically be
> recognized
> > as a date, but if not, you could pass the parameter as as string, and then
> > declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>
Any ideas on how to speed up this sp?
it is working but takes about 3-4 seconds per exec.
CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as
if (@.p_asofdate <= '00000000')
begin
set @.p_asofdate = '99999999'
end
delete from XAPAPTTOT
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @.p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @.p_comp) and (a.apph_vend = @.p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @.p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate) and a.apph_unpost_dt > @.p_asofdate and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GOPost the DDL and the indexes for the tables. Read the hint sticky at the top of the forum. Sample data might help as well, but what kind of volume are we talking about?|||the volume varies(multiple customers)
1,000-10,000 APTRANF and APPHISTF records
the Relationship between the tables is for each APTRANF record
you can have 0 to 9999 APPHISTF records.(generally only 0 or 1)
in unusually instances the APPHISTF might have 2-5 records.
the APPHISTF is a payment history(detail) to the APTRANF(master)
we allow unposting of a payment (apph_unpost_dt) and reissueing a new payment.
not sure about DDL ?|||Read this link here
http://www.dbforums.com/t1196943.html|||Hi,
Since I don't have any idea of your table structures and indexes thereon, I would go with eliminating redundencies in your code to reduce time. Several of the conditions and calculations are repeated and have now been changed to occur once. The code is given below. Hope this helps:
CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as
if (@.p_asofdate <= '00000000')
set @.p_asofdate = '99999999'
delete from XAPAPTTOT
where xapt_comp = @.p_comp
and xapt_vend = @.p_vend
and xapt_asof_date = @.p_asofdate
insert into XAPAPTTOT
select apph_comp
,apph_vend
,apph_type
,apph_id
,@.p_asofdate
,sum(apph_paymnts)
,sum(apph_discts)
,sum(apph_adjts)
,count(apph_paymnts)
,sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + b.apt_gross
,0
,max(str_1)
from (select apph_comp
,apph_vend
,apph_type
,apph_id
,apph_paymnts
,apph_discts
,apph_adjts
,apph_paymnts
,apph_unpost_dt
,str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') str_1
from APPHISTF
where apph_comp = @.p_comp
and apph_vend = @.p_vend) a
inner join
(select apt_gross
,apt_type
,apt_id
,apt_unposted_fg
from APTRANF
where bapt_comp = @.p_comp
and apt_vend = @.p_vend) b
on ( b.apt_type = a.apph_type
and b.apt_id = a.apph_id)
where (a.apph_unpost_dt = 0
and a.str_1 <= @.p_asofdate)
or (a.apph_unpost_dt > 0
and a.apph_unpost_dt <= @.p_asofdate
and b.apt_unposted_fg = 1)
or (a.str_1 <= @.p_asofdate
and a.apph_unpost_dt > @.p_asofdate)
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck = (select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp
and apph_vend = xapt_vend
and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GO
--Scalability Experts.
CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as
if (@.p_asofdate <= '00000000')
begin
set @.p_asofdate = '99999999'
end
delete from XAPAPTTOT
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @.p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @.p_comp) and (a.apph_vend = @.p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @.p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate) and a.apph_unpost_dt > @.p_asofdate and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GOPost the DDL and the indexes for the tables. Read the hint sticky at the top of the forum. Sample data might help as well, but what kind of volume are we talking about?|||the volume varies(multiple customers)
1,000-10,000 APTRANF and APPHISTF records
the Relationship between the tables is for each APTRANF record
you can have 0 to 9999 APPHISTF records.(generally only 0 or 1)
in unusually instances the APPHISTF might have 2-5 records.
the APPHISTF is a payment history(detail) to the APTRANF(master)
we allow unposting of a payment (apph_unpost_dt) and reissueing a new payment.
not sure about DDL ?|||Read this link here
http://www.dbforums.com/t1196943.html|||Hi,
Since I don't have any idea of your table structures and indexes thereon, I would go with eliminating redundencies in your code to reduce time. Several of the conditions and calculations are repeated and have now been changed to occur once. The code is given below. Hope this helps:
CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as
if (@.p_asofdate <= '00000000')
set @.p_asofdate = '99999999'
delete from XAPAPTTOT
where xapt_comp = @.p_comp
and xapt_vend = @.p_vend
and xapt_asof_date = @.p_asofdate
insert into XAPAPTTOT
select apph_comp
,apph_vend
,apph_type
,apph_id
,@.p_asofdate
,sum(apph_paymnts)
,sum(apph_discts)
,sum(apph_adjts)
,count(apph_paymnts)
,sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + b.apt_gross
,0
,max(str_1)
from (select apph_comp
,apph_vend
,apph_type
,apph_id
,apph_paymnts
,apph_discts
,apph_adjts
,apph_paymnts
,apph_unpost_dt
,str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') str_1
from APPHISTF
where apph_comp = @.p_comp
and apph_vend = @.p_vend) a
inner join
(select apt_gross
,apt_type
,apt_id
,apt_unposted_fg
from APTRANF
where bapt_comp = @.p_comp
and apt_vend = @.p_vend) b
on ( b.apt_type = a.apph_type
and b.apt_id = a.apph_id)
where (a.apph_unpost_dt = 0
and a.str_1 <= @.p_asofdate)
or (a.apph_unpost_dt > 0
and a.apph_unpost_dt <= @.p_asofdate
and b.apt_unposted_fg = 1)
or (a.str_1 <= @.p_asofdate
and a.apph_unpost_dt > @.p_asofdate)
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck = (select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp
and apph_vend = xapt_vend
and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GO
--Scalability Experts.
Any ideas on how to combine records into one
Here is what I have,
select id, name from rss_user
gives me this
r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four
I would like to be able to pipe this into a @.temp table so it looks like this,
r604738 one,two,three
r604739 one,two,three,four
Any ideas, so far I am drawing a blank.Sure, the search feature is your friend! Check out this thread (http://www.dbforums.com/t989683.html) from a couple of days ago.
This is MUCH better if done on the client, but it can also be done on the server too.
-PatP
select id, name from rss_user
gives me this
r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four
I would like to be able to pipe this into a @.temp table so it looks like this,
r604738 one,two,three
r604739 one,two,three,four
Any ideas, so far I am drawing a blank.Sure, the search feature is your friend! Check out this thread (http://www.dbforums.com/t989683.html) from a couple of days ago.
This is MUCH better if done on the client, but it can also be done on the server too.
-PatP
Any ideas how to connect to an Informix DB?
I need to use the an OLE DB Provider to create a Linked Server using a ODBC-Connection (Informix). I know that I cannot see many things in the Providers drop-down list. Has anyone created a linked server like this one? I mean SQL 2005 (Standard Edition) x64 and Informix. 10x in advance.
|||
Hi sageata2002,
I've got the same problem, and still searching the solution.
Perhaps we have to downgrade to SQL-Server 2005 x86 (32bit), where the MSDASQL-Provider (OLEDB Bridge for ODBC) is available....
|||Hello.
I am waiting for an answer from a company named Openlink, since they told me they will provide the solution in couple of days (their driver is still in beta). I will keep anyone posted about this, and, if successful, I will provide the details (my understanding is that the same procedure will apply to Oracle, for example).
|||Can you inform me, as soon as the Openlink-Solution is available?
That would be greatful!
Thank you very much.
Heiko Stahl
Systemadministrator
TQ-Systems GmbH
Heiko.Stahl@.tqs.de
http://www.tq-group.com
|||In the meantime I have contacted OpenLink, too.
I got a evaluation-version of an OLEDB-Provider for ODBC.
(download here:
http://download.openlinksw.com/uda/open60/wil6zole.msi ;; IA64
http://download.openlinksw.com/uda/open60/wal6zole.msi ;; AMD64
)
Unfortunately this Bridge works only with 64bit ODBC drivers, but our Informix SE is only 32 bit.
I must downgrade the SQL-Server to 32bit, no other way can help.
Regards,
Heiko
Any ideas ?
Unable to encrypt or decrypt data managed by the Report Server instance.
Please refer to the Reporting Services online help for guidance in enabling
this functionality.
...In help there is no help...If I understand you corrrectly, one way to do it would be to use a custom
assembly to encrtpy and decrypt data.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> wrote in message
news:O3XEwo9oEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Unable to encrypt or decrypt data managed by the Report Server instance.
> Please refer to the Reporting Services online help for guidance in
enabling
> this functionality.
> ...In help there is no help...
>
Please refer to the Reporting Services online help for guidance in enabling
this functionality.
...In help there is no help...If I understand you corrrectly, one way to do it would be to use a custom
assembly to encrtpy and decrypt data.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> wrote in message
news:O3XEwo9oEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Unable to encrypt or decrypt data managed by the Report Server instance.
> Please refer to the Reporting Services online help for guidance in
enabling
> this functionality.
> ...In help there is no help...
>
Any ideas
I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??My first idea (and I like this one the best) was to shake up a margarita...
Then I read your question
I would have a column in a table for Renewal Date. When they get renewed, update it with that date. Since the renewal is for 1 year anything that is not renewed would be earlier than today - 1 year
I still like my first idea|||How are you doing?? Nice to hear from you.
Well they have to be renewed every 2 years, there is a column in the table with the renewal date on it already, so your saying that should be their indication that they have been renewed already??|||USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myEmployee99(LastName varchar(30), FirstName varchar(30), RenewalDate datetime)
GO
INSERT INTO myEmployee99(LastName, FirstName, RenewalDate)
SELECT 'Johnson', 'Arnie' , '10/31/2001' UNION ALL
SELECT 'Kaiser' , 'Brett' , '10/31/2002' UNION ALL
SELECT 'Lynn' , 'Connie' , '10/31/2003' UNION ALL
SELECT 'More' , 'Desiree', '10/31/2004'
GO
SELECT 'Employees that Need to Renew: ' + LastName + ', ' + FirstName
+ ' --> ' + CONVERT(varchar(5), DATEDIFF(dd,RenewalDate,GetDate())-730) + ' Days Late'
FROM myEmployee99
WHERE DATEDIFF(dd,RenewalDate,GetDate())-730 > 0
GO
SET NOCOUNT ON
DROP TABLE myEmployee99
GO|||Was that what you were looking for?|||I dont think thats what I am looking for Brett but thank you anyways, I do appreciate it :)
You see I have a query that runs off the team members Final suit date. (by the way all the dates data types are DATETIME). I need something that tells the users as soon as they go in the database looking for whether a renewal has been completed or not. YOu see they get a report every month that tells them what renewals need to be pulled (files from filing cabinet) and renewed but I need something that tells them if they have already completed the renewal or not. Does that make sense??|||Is a renewal in your case a proces or is it enough to have it all on the 'final suit' field? How does the field 'final suit' work (when is it set the first time) and how does it change? If the 'final suit' can indicate when a license is renewed, why can it not tell if a license has been renewed in the past?|||If you post some DDL, sample data and the expected results you have (like the code I posted). I'm sure we can figure something out.
It's easier to see with samples...|||Hi there,
This all sounds extremely odd.
To me, it sounds as though you want to notify the customer as soon as the renewal happens. This can be done with a trigger.
You can create an update trigger that will use the SQL sendmail to send a email to the customer perhaps?
Even better - write a VB app for Outlook that will check the SQL table and send a mail to the customer if there renewal was done in the last 24 hours|||I use to have a drop down box that said yes or no to indicate if the renewal was complete or not??
Then I read your question
I would have a column in a table for Renewal Date. When they get renewed, update it with that date. Since the renewal is for 1 year anything that is not renewed would be earlier than today - 1 year
I still like my first idea|||How are you doing?? Nice to hear from you.
Well they have to be renewed every 2 years, there is a column in the table with the renewal date on it already, so your saying that should be their indication that they have been renewed already??|||USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myEmployee99(LastName varchar(30), FirstName varchar(30), RenewalDate datetime)
GO
INSERT INTO myEmployee99(LastName, FirstName, RenewalDate)
SELECT 'Johnson', 'Arnie' , '10/31/2001' UNION ALL
SELECT 'Kaiser' , 'Brett' , '10/31/2002' UNION ALL
SELECT 'Lynn' , 'Connie' , '10/31/2003' UNION ALL
SELECT 'More' , 'Desiree', '10/31/2004'
GO
SELECT 'Employees that Need to Renew: ' + LastName + ', ' + FirstName
+ ' --> ' + CONVERT(varchar(5), DATEDIFF(dd,RenewalDate,GetDate())-730) + ' Days Late'
FROM myEmployee99
WHERE DATEDIFF(dd,RenewalDate,GetDate())-730 > 0
GO
SET NOCOUNT ON
DROP TABLE myEmployee99
GO|||Was that what you were looking for?|||I dont think thats what I am looking for Brett but thank you anyways, I do appreciate it :)
You see I have a query that runs off the team members Final suit date. (by the way all the dates data types are DATETIME). I need something that tells the users as soon as they go in the database looking for whether a renewal has been completed or not. YOu see they get a report every month that tells them what renewals need to be pulled (files from filing cabinet) and renewed but I need something that tells them if they have already completed the renewal or not. Does that make sense??|||Is a renewal in your case a proces or is it enough to have it all on the 'final suit' field? How does the field 'final suit' work (when is it set the first time) and how does it change? If the 'final suit' can indicate when a license is renewed, why can it not tell if a license has been renewed in the past?|||If you post some DDL, sample data and the expected results you have (like the code I posted). I'm sure we can figure something out.
It's easier to see with samples...|||Hi there,
This all sounds extremely odd.
To me, it sounds as though you want to notify the customer as soon as the renewal happens. This can be done with a trigger.
You can create an update trigger that will use the SQL sendmail to send a email to the customer perhaps?
Even better - write a VB app for Outlook that will check the SQL table and send a mail to the customer if there renewal was done in the last 24 hours|||I use to have a drop down box that said yes or no to indicate if the renewal was complete or not??
订阅：
博文 (Atom)