I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring
up the properties, click on Server Options, then check the Data Access box.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In Enterprise Mgr, right click on the =linked server - CNS-SFO-A46\QA. Bring up the properties, =click on Server Options, then check the Data Access box.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Sandra" wrote in message news:00db01c3d620$c2=708f40$a601280a@.phx.gbl...I need to load data from a table in sqlserver to a table in an =instance of the same server. The query need to be run in the SQL server itself not =in the instance. So...insert into [servername\instancename].[dbname].[dbo].table1 (col1,col2)selectt2.col1,t2.col2from table2 as =t2 I keep getting this message:Server 'CNS-SFO-A46\QA' is =not configured for DATA ACCESS.Thanks in advance for you =help.
--=_NextPart_000_038B_01C3D5F9.27781520--|||I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you find the linked server under Security->Linked Servers?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:058101c3d631$7e232580$a101280a@.phx.gbl...
I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Did you find the linked server under Security->Linked Servers?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Sandra" wrote in message news:058101c3d631$7e=232580$a101280a@.phx.gbl...I'm affraid I don't see a check box for Data Access. Is it possible I'm =looking in the wrong place?>--Original Message-->In =Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring>up the properties, click on Server Options, then check =the Data Access box.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Sand=ra" wrote in message>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...>=I need to load data from a table in sqlserver to a table>in an instance =of the same server. The query need to be>run in the SQL server itself =not in the instance. So...>>insert into [servername\instancename].[dbname].>[dbo].table1 (>col1,>col2)>select>t2.col1,>t2.col2>from table2 as t2>>I keep getting this =message:>>Server 'CNS-SFO-A46\QA' is not configured for DATA =ACCESS.>>Thanks in advance for you help.>
--=_NextPart_000_0038_01C3D60C.8C53A730--|||Yes, I did and I don't find the name of the server listed
but when I try to create New Linked Server it says the
server already exist. The same response I get when I try
to run EXEC sp_addlinkedserver
@.server = 'servername\instancename'
Also, I see the name of the server in the sysservers table.
>--Original Message--
>Did you find the linked server under Security->Linked
Servers?
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:058101c3d631$7e232580$a101280a@.phx.gbl...
>I'm affraid I don't see a check box for Data Access. Is it
>possible I'm looking in the wrong place?
>>--Original Message--
>>In Enterprise Mgr, right click on the linked server -
CNS-
>SFO-A46\QA. Bring
>>up the properties, click on Server Options, then check
>the Data Access box.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>>I need to load data from a table in sqlserver to a table
>>in an instance of the same server. The query need to be
>>run in the SQL server itself not in the instance. So...
>>insert into [servername\instancename].[dbname].
>>[dbo].table1 (
>>col1,
>>col2)
>>select
>>t2.col1,
>>t2.col2
>>from table2 as t2
>>I keep getting this message:
>>Server 'CNS-SFO-A46\QA' is not configured for DATA
ACCESS.
>>Thanks in advance for you help.
>|||The server might already be listed under "remote servers". This would happen
for example if you were doing replication from one instance to another. You
can allow data access by running the procedure
sp_serveroption 'server' ,
'data access',
'true'
Hope this helps,
Joe Lax
<anonymous@.discussions.microsoft.com> wrote in message
news:06be01c3d641$bbad9740$a101280a@.phx.gbl...
> Yes, I did and I don't find the name of the server listed
> but when I try to create New Linked Server it says the
> server already exist. The same response I get when I try
> to run EXEC sp_addlinkedserver
> @.server = 'servername\instancename'
> Also, I see the name of the server in the sysservers table.
>
>
> >--Original Message--
> >Did you find the linked server under Security->Linked
> Servers?
> >
> >--
> > Tom
> >
> >----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >..
> >"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:058101c3d631$7e232580$a101280a@.phx.gbl...
> >I'm affraid I don't see a check box for Data Access. Is it
> >possible I'm looking in the wrong place?
> >
> >>--Original Message--
> >>In Enterprise Mgr, right click on the linked server -
> CNS-
> >SFO-A46\QA. Bring
> >>up the properties, click on Server Options, then check
> >the Data Access box.
> >>
> >>--
> >>Tom
> >>
> >>----
> -
> >--
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com/sql
> >>
> >>
> >>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
> >>I need to load data from a table in sqlserver to a table
> >>in an instance of the same server. The query need to be
> >>run in the SQL server itself not in the instance. So...
> >>
> >>insert into [servername\instancename].[dbname].
> >>[dbo].table1 (
> >>col1,
> >>col2)
> >>select
> >>t2.col1,
> >>t2.col2
> >>from table2 as t2
> >>
> >>I keep getting this message:
> >>
> >>Server 'CNS-SFO-A46\QA' is not configured for DATA
> ACCESS.
> >>
> >>Thanks in advance for you help.
> >>
> >sql
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Load data from text file into some table implemented in stored procedure.
Hello, I want to load data from text file to MS SQL DB table.
In MySQL, it is the "LOAD DATA INFILE..." query statement.
What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?
I think you need some third party tool, which will do the needed convertions. Executing query form a file you need to read the SQLCMD form BOL, this is command line utility.
|||hi remedios,
you can use the bcp(Bulk Copy Program) to basically do the same as in MySql. or if not why not use DTS in Enterprise Manager and select the text file as the Source
hth
Load Balancing SSAS Servers
Hello,
We are trying to load balance a pair of SQL Server 2005 Analysis Services query servers using hardware load balancing (Foundry switches) but we don't seem to be able to get it to work.
The main problem as far as I can tell is that we can't get the SSAS instances to listen on the IP address of the loopback adapter which is configured on both servers.
The netstat -ano command shows that the SSAS instances are only listening on the IP address of the physical NIC in each machine.
We are using a custom port and I can see where this custom port is defined in the msmdsrv.ini file but I can't see anywhere where you can tell SSAS the IP addresses on which to listen.
Should SSAS listen on all available IP addresses when it starts up ? We have re-booted both boxes but this hasn't made any difference ?
Can anyone shed some light on this please ?
Regards,
Paul Lynch
Hi Paul, I have not done anything like this before, so this is a bit of a long shot. I was wondering if the security setting that you need to change in order to use application names might need to be changed. see http://support.microsoft.com/kb/888526 - this article is for AS2000, but I think the same approach still applies for AS2k5.
I think by default it checks that the name/ip address match each other, so this may help.
|||Hi Darren,
Thanks for your reply.
We actually found the problem - the network guy had forgotten to configure something called Direct Server Return (DSR) on the Foundry so the traffic wasn't being forwarded properly to the SSAS boxes. When I looked into it again I realised that the SSAS process was actually listening on the loopback adapter address as well as the IP address of the physical NIC so the problem wasn't what I originally thought it was.
We are now able to connect to the VIP address on the port specified in SSAS and it appears to connect OK (I can connect from the front-end web server using SQL Management Studio so I'm assuming its working)
However, the KB article you posted has got me thinking that maybe we need to configure an application name for it to work - I'll need to speak to the DBA's because I'm out of my depth with this already - I'm just an IIS guy ;-)
Thanks again.
Paul
Wednesday, March 28, 2012
Load balancing for read-only databases
We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
Load balancing for read-only databases
We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
Load balancing for read-only databases
We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql
Monday, March 26, 2012
Llimit on Lines of code in Query Builder
Does anyone know if there is a limit on characters or lines of code that can be used to create your dataset?
I cut and pasted some sql code of a report in there and it's cutting off the last 100 lines or so.
If you select the report in the solution explorer, right click and 'view code', you can then find your dataset in the rdl and insert the sql directly in there.
Friday, March 23, 2012
little problem..
hey guys, i have a little problem:
i use ASP with MSSQL and i have a query that adds these values to the database:
values(' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
when a value is empty, the fields are not filled
but when a value contains a single quote ('), that quote ends the value:
values(' hel'lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
so my question is, how can i make the quote (') in hel'lo .. invisible or something, so MSSQL wont see it as the end of the value.
(im MYSQL, you can just place a (\) in front of the (') but that doenst work in MSSQL...)use like this :
values(' hel''lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
-Rohit|||Use one more single quote as the escape character.|||thanx lads! :)
i use ASP with MSSQL and i have a query that adds these values to the database:
values(' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
when a value is empty, the fields are not filled
but when a value contains a single quote ('), that quote ends the value:
values(' hel'lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
so my question is, how can i make the quote (') in hel'lo .. invisible or something, so MSSQL wont see it as the end of the value.
(im MYSQL, you can just place a (\) in front of the (') but that doenst work in MSSQL...)use like this :
values(' hel''lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
-Rohit|||Use one more single quote as the escape character.|||thanx lads! :)
Listing tables in a database
Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
Listing permissions on Stored Procedures via query.
How do I list the permissions on a stored procedure with a query?
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:
> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:
> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:
> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:
> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>
listing only owners with more than one car query help
Hi, here is the problem I have to resolve, I'll try to be very clear about the situation (using MS Access):
- I have 2 tables (well, I have more but only two are relevant to the query) one table stores owner information (owner #, name, surname, phone number...) and the other stores car info (car #, brand, type, color and owner #)
- I want to make a query that gives me the owner info but only for owners that have MORE than one car (ie 2 or more). I tried many approaches but without any success, so far..
- It doesn't matter if I get the same user information 2,3,4 times, I will take the query and build a report with it, and I'll group the cars by users.
Any ideas for me? This really bothers me because I really don't have a clue and I'm sure I missed something easy... :(I'd use:SELECT ownerID
FROM cars
GROUP BY ownerID
HAVING 1 < Count(*);
-PatP|||Thanks, Pat, this correctly lists people with more than one car :) But if I want to da a report with the car info for each car that each owner of more than 1 car (err.. I wanna do a report with the user info + the car info in a report, grouped by the user), I must have the info of each car also.. Your query works like I want, but only list the users and I can't add car info on one line for each car.. Well, I know that sounded pretty bad.. Any idea?
Thanks for the quick answer there, Pat!|||Picky, picky, picky! Ok, at least the logic is good. Now let's try:SELECT *
FROM cars AS a
WHERE 1 < (SELECT Count(*)
FROM cars AS z
WHERE z.ownerID = a.ownerID);...and see if that am more gooder yet even. (How many ways can I butcher the English language... Is there a finite limit?)
-PatP|||All I want to say is: "Thanks alot, this is exactly what I wanted.. I'll be trying to add informations about the owners from the owner's table now.. But this is exactly what I meant!
Thanks for this, Pat, you are a real pal!|||No problemo! Always glad to "stir the pot" any way I can.
-PatPsql
- I have 2 tables (well, I have more but only two are relevant to the query) one table stores owner information (owner #, name, surname, phone number...) and the other stores car info (car #, brand, type, color and owner #)
- I want to make a query that gives me the owner info but only for owners that have MORE than one car (ie 2 or more). I tried many approaches but without any success, so far..
- It doesn't matter if I get the same user information 2,3,4 times, I will take the query and build a report with it, and I'll group the cars by users.
Any ideas for me? This really bothers me because I really don't have a clue and I'm sure I missed something easy... :(I'd use:SELECT ownerID
FROM cars
GROUP BY ownerID
HAVING 1 < Count(*);
-PatP|||Thanks, Pat, this correctly lists people with more than one car :) But if I want to da a report with the car info for each car that each owner of more than 1 car (err.. I wanna do a report with the user info + the car info in a report, grouped by the user), I must have the info of each car also.. Your query works like I want, but only list the users and I can't add car info on one line for each car.. Well, I know that sounded pretty bad.. Any idea?
Thanks for the quick answer there, Pat!|||Picky, picky, picky! Ok, at least the logic is good. Now let's try:SELECT *
FROM cars AS a
WHERE 1 < (SELECT Count(*)
FROM cars AS z
WHERE z.ownerID = a.ownerID);...and see if that am more gooder yet even. (How many ways can I butcher the English language... Is there a finite limit?)
-PatP|||All I want to say is: "Thanks alot, this is exactly what I wanted.. I'll be trying to add informations about the owners from the owner's table now.. But this is exactly what I meant!
Thanks for this, Pat, you are a real pal!|||No problemo! Always glad to "stir the pot" any way I can.
-PatPsql
Wednesday, March 21, 2012
Listing jobs that ran last hour
Hello...
I want to write a query that will list for me the name, date and time of
jobs that ran from the previous hour. I am using SQL Server 2000.
Thank you,
Brett>> ...a query that will list for me the name, date and time of jobs that ran
The data you are looking for is available in msdb..sysjobhistory table. You
can do:
SELECT job_name, run_time
FROM ( SELECT CAST( CAST( run_date AS CHAR( 9 ) ) +
STUFF( STUFF( RIGHT( REPLICATE( '0', 6 ) +
CAST( run_time AS VARCHAR ), 6 ) , 3, 0 , ':' ) , 6, 0 ,
':' )
AS DATETIME ),
( SELECT name
FROM msdb..sysjobs s2
WHERE s2.job_id = s1.job_id )
FROM msdb..sysjobhistory s1 ) D ( run_time, job_name )
WHERE run_time >= DATEADD( hour, -1, CURRENT_TIMESTAMP ) ;
Perhaps, someone else might post a more simplified string format routine.
Anith
I want to write a query that will list for me the name, date and time of
jobs that ran from the previous hour. I am using SQL Server 2000.
Thank you,
Brett>> ...a query that will list for me the name, date and time of jobs that ran
The data you are looking for is available in msdb..sysjobhistory table. You
can do:
SELECT job_name, run_time
FROM ( SELECT CAST( CAST( run_date AS CHAR( 9 ) ) +
STUFF( STUFF( RIGHT( REPLICATE( '0', 6 ) +
CAST( run_time AS VARCHAR ), 6 ) , 3, 0 , ':' ) , 6, 0 ,
':' )
AS DATETIME ),
( SELECT name
FROM msdb..sysjobs s2
WHERE s2.job_id = s1.job_id )
FROM msdb..sysjobhistory s1 ) D ( run_time, job_name )
WHERE run_time >= DATEADD( hour, -1, CURRENT_TIMESTAMP ) ;
Perhaps, someone else might post a more simplified string format routine.
Anith
Listing all tables and their columns?
Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql
Listbox to only appear if there are records returned from the SQL select query
I would like to make a listbox only appear if there are results returned by the SQL select statement.
I want this to be assessed on a click event of a button before the listbox is rendered.
I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
hi,
there must be datasource(dataset or reader) for that listbox i believe.
if its dataset then use dataset.tables[0].Rows.Count, if its reader then use reader.hasrows.
hope it helps.
regards,
satish.
|||Thanks Satish,
I used an if statement to assess if rows > 0
Cheers,
Ben.
|||cheers Ben
satish.
sqlMonday, March 19, 2012
List up-coming birthdays
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !
that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!
Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.
Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function
CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin
return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)
very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail
or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30
This may not work at the end of the year. It will skip the people born on January.|||your right
he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right
he should test on the month and
add an OR for a test on the next year
I guess this should work. BETWEEN is the best way to go
DOB = The field Name
BDAYS = The table name
====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates
The DateDiff function would return minus values also.
By the way Toronto is nice city. I used live for a year and half. I love that city
Originally posted by r937
of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong
i withdraw my solutions
not enough coffee, i guess
i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax
see http://www.dbforums.com/showthread.php?threadid=659590
let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant
it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works
that formula is fabulous
did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.
I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub
it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.
That way I'll be able to find it if I ever need it!
Cheers!|||thanks, i will
:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman
or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!
Blindman could you help me out plz?
I have applied your coding as:
Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
I get an error message saying,
Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.
What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.
-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it?|||Originally posted by hali99
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.
If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.
-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)
i have tested it and have confirmed that it's broken
create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000
it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on
create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')
declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'
select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates
drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.
Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !
that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!
Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.
Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function
CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin
return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)
very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail
or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30
This may not work at the end of the year. It will skip the people born on January.|||your right
he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right
he should test on the month and
add an OR for a test on the next year
I guess this should work. BETWEEN is the best way to go
DOB = The field Name
BDAYS = The table name
====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates
The DateDiff function would return minus values also.
By the way Toronto is nice city. I used live for a year and half. I love that city
Originally posted by r937
of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong
i withdraw my solutions
not enough coffee, i guess
i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax
see http://www.dbforums.com/showthread.php?threadid=659590
let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant
it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works
that formula is fabulous
did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.
I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub
it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.
That way I'll be able to find it if I ever need it!
Cheers!|||thanks, i will
:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman
or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!
Blindman could you help me out plz?
I have applied your coding as:
Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
I get an error message saying,
Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.
What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.
-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it?|||Originally posted by hali99
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.
If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.
-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)
i have tested it and have confirmed that it's broken
create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000
it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on
create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')
declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'
select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates
drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.
List Tables In A DataBase
hi everybody
If anybody knows how to list the tables in a database in Sql Server Query Analyser .
I would like to know is there any commands or SP 's like sp_help
Thank you for all the support
thanks & regards
Hi,
Simplest way would be to query the sysobjects table:
select name from sysobjects where xtype = 'U'
'U' = User table
Checkout sysobjects in BOL.
Jamie
|||Another alternative is to use something more like:
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE'
The advantage of this is that it does not use the system objects which are subject to change.
|||Hi, nevincm
Code Snippet
select * from sysobjects where xtype = 'u' /*This will list all tables and its properties*/
select name from sysobjects where xtype = 'u' /*This will list table name as Jamie mentioned*/
Monday, March 12, 2012
List of users and user rights
Does anyone have a query that will list all the users and user rights for a sqlserver database.
Thanks so much!sp_helpuser will list all the users.
sp_helplogins gives all the login users.
these procedures might help you
List of tables used in query
Hi all,
Is there any way to get the list of the tables used
in the select statment ?
I want to create an application in which when user fires
any query i want to store tables used in that query in
other table.
for e.g if user execute
--
select * from table mytablea join mytableb on id = id
--
then i want to get mytablea and mytableb
as my list of tables.
thanks..
--
Thanks & Regards
MalkeshHi
Is it just simple a SELECT statement or a stored procedures,views as well?
"Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
> Hi all,
> Is there any way to get the list of the tables used
> in the select statment ?
> I want to create an application in which when user fires
> any query i want to store tables used in that query in
> other table.
> for e.g if user execute
> --
> select * from table mytablea join mytableb on id = id
> --
> then i want to get mytablea and mytableb
> as my list of tables.
> thanks..
> --
> Thanks & Regards
> Malkesh|||Hi,
It's just a select statment, but may involve complex joins, subqueries etc..
.
--
Thanks & Regards
Malkesh
"Uri Dimant" wrote:
> Hi
> Is it just simple a SELECT statement or a stored procedures,views as well?
>
>
> "Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
> news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
>
>
Is there any way to get the list of the tables used
in the select statment ?
I want to create an application in which when user fires
any query i want to store tables used in that query in
other table.
for e.g if user execute
--
select * from table mytablea join mytableb on id = id
--
then i want to get mytablea and mytableb
as my list of tables.
thanks..
--
Thanks & Regards
MalkeshHi
Is it just simple a SELECT statement or a stored procedures,views as well?
"Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
> Hi all,
> Is there any way to get the list of the tables used
> in the select statment ?
> I want to create an application in which when user fires
> any query i want to store tables used in that query in
> other table.
> for e.g if user execute
> --
> select * from table mytablea join mytableb on id = id
> --
> then i want to get mytablea and mytableb
> as my list of tables.
> thanks..
> --
> Thanks & Regards
> Malkesh|||Hi,
It's just a select statment, but may involve complex joins, subqueries etc..
.
--
Thanks & Regards
Malkesh
"Uri Dimant" wrote:
> Hi
> Is it just simple a SELECT statement or a stored procedures,views as well?
>
>
> "Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
> news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
>
>
List of synchs?
Which query gives me the no of synchs in Merge replication? If a user is
synchronizing 10 times I need the information of all the 10 synchs.
I am using the following query,
SELECT a.subscriber_db
,runstatus
,start_time
,end_time
,duration [Synch Duration]
FROM dbo.MSmerge_sessions s
LEFT OUTER JOIN MSmerge_agents a
ON a.id=s.agent_id
This query is not giving me consistent result. Every time I run this query
I get some of the records missing.
Can someone put some light?
This should work. Is it possible that there are no rows synchronized during
these missing syncs?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:07DC40EB-9386-4A41-82B8-027D80FDA4F9@.microsoft.com...
> Which query gives me the no of synchs in Merge replication? If a user is
> synchronizing 10 times I need the information of all the 10 synchs.
> I am using the following query,
> SELECT a.subscriber_db
> ,runstatus
> ,start_time
> ,end_time
> ,duration [Synch Duration]
> FROM dbo.MSmerge_sessions s
> LEFT OUTER JOIN MSmerge_agents a
> ON a.id=s.agent_id
> This query is not giving me consistent result. Every time I run this
> query
> I get some of the records missing.
> Can someone put some light?
>
synchronizing 10 times I need the information of all the 10 synchs.
I am using the following query,
SELECT a.subscriber_db
,runstatus
,start_time
,end_time
,duration [Synch Duration]
FROM dbo.MSmerge_sessions s
LEFT OUTER JOIN MSmerge_agents a
ON a.id=s.agent_id
This query is not giving me consistent result. Every time I run this query
I get some of the records missing.
Can someone put some light?
This should work. Is it possible that there are no rows synchronized during
these missing syncs?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:07DC40EB-9386-4A41-82B8-027D80FDA4F9@.microsoft.com...
> Which query gives me the no of synchs in Merge replication? If a user is
> synchronizing 10 times I need the information of all the 10 synchs.
> I am using the following query,
> SELECT a.subscriber_db
> ,runstatus
> ,start_time
> ,end_time
> ,duration [Synch Duration]
> FROM dbo.MSmerge_sessions s
> LEFT OUTER JOIN MSmerge_agents a
> ON a.id=s.agent_id
> This query is not giving me consistent result. Every time I run this
> query
> I get some of the records missing.
> Can someone put some light?
>
Friday, March 9, 2012
List of SQL table that have data in it
I want to create a query to return all table name in my SQL database that ha
s
more than 0 records how can I do that ?
Thanks
FREDTry,
use northwind
go
create table #t (
tname sysname,
rcnt int
)
declare @.tn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)
from
information_schema.tables
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', count(*) from ' + @.tn
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t
where
rcnt > 0
drop table #t
go
AMB
"FRED" wrote:
> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||Is this useful for you?
select object_name(si.id), rows
from sysindexes si
where si.id = (select object_id(so.name) from sysobjects so where so.type =
'u' and si.id = so.id)
and si.indid < 2
and si.rows > 0
order by 1
"FRED" <FRED@.discussions.microsoft.com> wrote in message
news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||My problem is solved
Thanks
FRED
"Armando Prato" wrote:
> Is this useful for you?
> select object_name(si.id), rows
> from sysindexes si
> where si.id = (select object_id(so.name) from sysobjects so where so.type
=
> 'u' and si.id = so.id)
> and si.indid < 2
> and si.rows > 0
> order by 1
> "FRED" <FRED@.discussions.microsoft.com> wrote in message
> news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> has
>
>
s
more than 0 records how can I do that ?
Thanks
FREDTry,
use northwind
go
create table #t (
tname sysname,
rcnt int
)
declare @.tn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)
from
information_schema.tables
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', count(*) from ' + @.tn
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t
where
rcnt > 0
drop table #t
go
AMB
"FRED" wrote:
> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||Is this useful for you?
select object_name(si.id), rows
from sysindexes si
where si.id = (select object_id(so.name) from sysobjects so where so.type =
'u' and si.id = so.id)
and si.indid < 2
and si.rows > 0
order by 1
"FRED" <FRED@.discussions.microsoft.com> wrote in message
news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||My problem is solved
Thanks
FRED
"Armando Prato" wrote:
> Is this useful for you?
> select object_name(si.id), rows
> from sysindexes si
> where si.id = (select object_id(so.name) from sysobjects so where so.type
=
> 'u' and si.id = so.id)
> and si.indid < 2
> and si.rows > 0
> order by 1
> "FRED" <FRED@.discussions.microsoft.com> wrote in message
> news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> has
>
>
Subscribe to:
Posts (Atom)