List availables tables: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

Load dts2000 fails

Sometime ago i had to edit a dts package in ssis. To do that i've instaled the sql2005 dts. It worked fine at the time. But now i'm trying to open a dts in ssis after installing sp2 and it doesn't work. Every time i try to open a dts package i got the following error.

Error HRESULT E_FAIL has been returned from a call to a COM component. (Microsoft Visual Studio)

Program Location:

at DTS.CDTSLegacyDesignerClass.ShowDesigner()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.GeneralView.btnEdit_Click(Object sender, EventArgs args)

I dont understand why this is happening. Just one thing i use to have windows server 2003 now i've xp sp2. I don′t know if this can be important but.

Thanks in advance.

I believe you can't open/edit DTS files in SSIS. You can execute DTS files from SSIS though.|||Of course you can edit them. But it's not even opening the packages, that's the problem. When you put the execute 2000 dts package task you can click the edit buton to see the dts. But i got the error i've wirted above.|||It's hard to tell for sure from the error, but it's possible that the DTS 2000 designer installation is corrupt. Have you tried installing the DTS Designer Components from the SQL 2005 Feature Pack download site?|||

Not sure, but try removing the curent feature pack and installing the Feb 2007 feature pack, as that may have been updated for SP2 - http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

I didn't think it changed but at any rate the re-install may help.

|||yeah i've installed the latest version of the pack but it still doesn't work. I really would like to know if there is somebody using winXPsp2 and have this component working. Because i use to have the 2003server and it was with the sp2 of sql2005 and it was working fine. So i really would like to know if it is the OS.

thanks|||

We do have an issue related to the use of teh DTS Designer with different versions of the common control dll found on different OS's. Since we've ruled out a corrupt install of the designer components, then I think it's worthwhile to spend the time to diagnose for the problem I mentioned. See this KB:

http://support.microsoft.com/default.aspx/kb/917406/en-us

|||That was a good idea but it didn't worked. I'm going to try some new approaches. I will keep everybody up to date. I only hope that this just work like it did before.

Thanks

Wednesday, March 28, 2012

Load Balancing

How much traffic/load can a database server running MS SQL server take
before it can't handle it anymore? And when that time comes, what are the
recourses? Am I able to load balance it between separate servers?Jibba Jabba wrote:
> How much traffic/load can a database server running MS SQL server take
> before it can't handle it anymore? And when that time comes, what are the
> recourses? Am I able to load balance it between separate servers?

Depends on the server hardware. And based on Microsoft's federated
architecture ... while you can offload to multiple servers ... you must
do a lot of duplication and mean time between failures goes down ...
not up.

If you get to the point that SQL Server can no longer handle the load
... you are most likely looking at Sybase, Informix, Oracle, DB2 or
waiting hopefully for some future version.

--
Daniel Morgan
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||"Jibba Jabba" <dontemailme@.dontemailme.com> wrote in message news:<UsEWb.893$tL3.845@.newsread1.news.pas.earthlink.net >...
> How much traffic/load can a database server running MS SQL server take
> before it can't handle it anymore? And when that time comes, what are the
> recourses? Am I able to load balance it between separate servers?

As with most performance-related questions, there's no simple answer.
If you have a well-designed application, and well-written code, then
the answer is 'a lot'. If you don't, then the answer is 'not a lot'.
The only way to get a real, quantifiable answer is for you to do some
load testing using your own code and data. In many cases, scaling up
the database server is pointless, because the application code is the
real bottleneck, but again, this is something that only you can
investigate properly.

Simon|||"Jibba Jabba" <dontemailme@.dontemailme.com> wrote in message
news:UsEWb.893$tL3.845@.newsread1.news.pas.earthlin k.net...
> How much traffic/load can a database server running MS SQL server take
> before it can't handle it anymore? And when that time comes, what are the
> recourses? Am I able to load balance it between separate servers?

"It depends"

What type of load. How fast are your disks, your CPU, etc.

You can't do load-balancing per-se, but you can do various things that can
help.

We publish data to Box A and then push that to Boxes B and C and uses those
for reading. Works well.

Monday, March 26, 2012

Load balace issue

Hi everyone, I am running into an issue that the SQL server CPUs are all the
time busy serving request [between 80-100%] , and users are now complaining
of slow performance of the application even the server is a dual xeon 2.8
processors and 6GB of ram and set as high priority, I run SQL 2000
Enterprise.
some have suggested to use a load balancing, where I can add more servers
and they all will act as one server.
This sounds very interesting if the load can be distributed among several
servers.
can anyone give me a suggestion or guides me to where I get more info and
how to do it.
I got some info on the failover which look close but does not solve the
problem of high CPU usage.
I greatly appreciate any feed back
Thanks in advance.Hi
Unless you have an intricate application design that supports load balancing
(with replication behind the scenes etc), you can't do it.
SQL Server does not support load balancing.
You should not run SQL Server in high priority as you may starve the OS of
CPU cycles, resulting in an actual system slowdown.
Have you looked at what is causing your CPU's to run so high? Bad indexing
jumps to my mind. Are you sure that SQL Server is using the 4.7GB of the 6Gb
it could? Run profiler and see which queries are the problematic ones and
tune those. Check with Performance monitor what the SQL Server Target Memory
setting is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"aherzallah" <ah@.herz.dk> wrote in message
news:uITvd1OAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hi everyone, I am running into an issue that the SQL server CPUs are all
> the time busy serving request [between 80-100%] , and users are now
> complaining of slow performance of the application even the server is a
> dual xeon 2.8 processors and 6GB of ram and set as high priority, I run
> SQL 2000 Enterprise.
> some have suggested to use a load balancing, where I can add more servers
> and they all will act as one server.
> This sounds very interesting if the load can be distributed among several
> servers.
> can anyone give me a suggestion or guides me to where I get more info and
> how to do it.
> I got some info on the failover which look close but does not solve the
> problem of high CPU usage.
> I greatly appreciate any feed back
> Thanks in advance.
>|||Have you run a profile to see what queries are actually running? Ok, it
could well be that you are running at capcity, but don't overlook the fact
that your application could have design flaws, or in need of some
maintenance. How about your database design, indexes etc...Just broad
suggestions, certainly worth checking out before you spend your bosses money
on a server farm! :)
HTH.
"aherzallah" <ah@.herz.dk> wrote in message
news:uITvd1OAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hi everyone, I am running into an issue that the SQL server CPUs are all
> the time busy serving request [between 80-100%] , and users are now
> complaining of slow performance of the application even the server is a
> dual xeon 2.8 processors and 6GB of ram and set as high priority, I run
> SQL 2000 Enterprise.
> some have suggested to use a load balancing, where I can add more servers
> and they all will act as one server.
> This sounds very interesting if the load can be distributed among several
> servers.
> can anyone give me a suggestion or guides me to where I get more info and
> how to do it.
> I got some info on the failover which look close but does not solve the
> problem of high CPU usage.
> I greatly appreciate any feed back
> Thanks in advance.
>

load all data without knowing old one was load in the previous time?

I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run?

I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?

Thank

Hi Cao Van,

Ofcourse when you run the package every time it means you are inserting duplicate rows. To avoid this, I have two familiar methods

1) Truncate the destination table, everytime when you load the data from source and populate the detination table. (This is always time consuming process)

2) Use Look up concept to filter out the duplicates.

Thanks

Subhash Subramanyam

|||

In other words, you are the responsible of putting enough logic to avoid loading duplicate rows in your destination. Subhassh suggestions are very valid. For the first one you can use an execute sql task in control flow, right before the data flow to perform the TRUNCATE. For the second option, make sure you search the forum; there have been many discussions around that, actually in the first page of the forum, there is a sticky post that talks about it.

sql

Live Webcast tomorrow Essential Team System for Database Developers

Live Webcast tomorrow

Essential Team System for Database Developers

1 PM Central time 12/28

https://www.clicktoattend.com/invitation.aspx?code=112602

Do we know if there will be any SSIS topics covered in this Webcast?|||I don't know, but I thought it would be of general interest and on another thread someone was asking about test data generation and that will be covered.

Friday, March 23, 2012

Little SQL icon gone in SQL Server 2005?

Gurus,
Just installed SQL Server 2005 for the first time. Now, whenever I have
installed SQL Server 2000 in the past, I am used to seeing a little gray
server icon in the System tray which has either a green arrow on it if SQL
services are running or a red square on it if SQL services are stopped. I
no longer see this icon did Microsoft take it away?
--
Spin> did Microsoft take it away?
Yes. You can download a 3:rd party from sqldbatips.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Spin" <Spin@.spin.com> wrote in message news:4ua59oF177e4mU1@.mid.individual.net...
> Gurus,
> Just installed SQL Server 2005 for the first time. Now, whenever I have installed SQL Server 2000
> in the past, I am used to seeing a little gray server icon in the System tray which has either a
> green arrow on it if SQL services are running or a red square on it if SQL services are stopped.
> I no longer see this icon did Microsoft take it away?
> --
> Spin
>|||I can't believe Microsoft takes away stuff in newer software versions.
--
Spin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6SR6kqHHHA.420@.TK2MSFTNGP06.phx.gbl...
>> did Microsoft take it away?
> Yes. You can download a 3:rd party from sqldbatips.com.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Spin" <Spin@.spin.com> wrote in message
> news:4ua59oF177e4mU1@.mid.individual.net...
>> Gurus,
>> Just installed SQL Server 2005 for the first time. Now, whenever I have
>> installed SQL Server 2000 in the past, I am used to seeing a little gray
>> server icon in the System tray which has either a green arrow on it if
>> SQL services are running or a red square on it if SQL services are
>> stopped. I no longer see this icon did Microsoft take it away?
>> --
>> Spin
>>
>|||The functionality is included in SQL Server Configuration Manager. You also find it in SQL Server
Management Studio.
But, I agree that Service Manager is sometimes nice to have, which is why I use the one from
sqldbatips.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Spin" <Spin@.spin.com> wrote in message news:4ua8bsF17aifsU1@.mid.individual.net...
>I can't believe Microsoft takes away stuff in newer software versions.
> --
> Spin
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O6SR6kqHHHA.420@.TK2MSFTNGP06.phx.gbl...
>> did Microsoft take it away?
>> Yes. You can download a 3:rd party from sqldbatips.com.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Spin" <Spin@.spin.com> wrote in message news:4ua59oF177e4mU1@.mid.individual.net...
>> Gurus,
>> Just installed SQL Server 2005 for the first time. Now, whenever I have installed SQL Server
>> 2000 in the past, I am used to seeing a little gray server icon in the System tray which has
>> either a green arrow on it if SQL services are running or a red square on it if SQL services are
>> stopped. I no longer see this icon did Microsoft take it away?
>> --
>> Spin
>>
>>
>

Little SQL icon gone in SQL Server 2005?

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

LISTEN TO SEXY FREE NOW

English / Chinese / Franch / Germany / Japan / More
World Largest, LIMITED TIME FREE TO LISTEN!!!
24-Hrs +91 9810577227mediapuri wrote:
> English / Chinese / Franch / Germany / Japan / More
> World Largest, LIMITED TIME FREE TO LISTEN!!!
> 24-Hrs +91 9810577227
>
http://www.swd.gov.hk/tc/index/site...w.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
^ ^ 21:25:01 up 14 days 4:48 0 users load average: 1.01 1.02 1.00
news://news.3home.netnews://news.hkpcug.orgnews://news.newsgroup.com.hk

Monday, March 19, 2012

List when Changes occur

Hi I am a newbie to SQL.

I have a historical list of digatal points listed by time.ie: 3 fields
PointName;Date/Time;State.

I need to return a list of When a specific point chsnges state.

For example a list everytime Point A transitions to State 1.

Any help is appreciated.

--
Posted via http://dbforums.comAssuming your table looks like this:

CREATE TABLE PointStates (pointname CHAR(1), dt DATETIME, state INTEGER NOT
NULL, PRIMARY KEY (pointname,dt))

You can use this query:

SELECT DISTINCT
MIN(B.dt)
FROM PointStates AS A
JOIN PointStates AS B
ON A.pointname = B.pointname
AND A.dt < B.dt
AND A.state<>B.state
GROUP BY A.pointname, A.dt

--
David Portas
----
Please reply only to the newsgroup
--

Wednesday, March 7, 2012

list of clients connected to a database

quote:

> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>

|||Thanks, strange enough, under client name, it lists the server name running
the sql server rather than the name of the client connecting to it.. this is
weird.
Lisa
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...

quote:

> sysprocesses table in master database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>
>

|||Note that this can be controlled with the connection
string, and is therefore not totally reliable. The client
program can specify whatever name it fancies.
Linchi

quote:

>--Original Message--
>Thanks, strange enough, under client name, it lists the

server name running

quote:

>the sql server rather than the name of the client

connecting to it.. this is

quote:

>weird.
>Lisa
>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote

in message

quote:

>news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
around in system[QUOTE]
were connected to[QUOTE]
connected to a[QUOTE]
>
>.
>

|||HOST_NAME() depens on the ODBC connection string?
How? Is it the WSID parameter?
See, I always wondered what the difference was between WSID and SERVER as
they seemed to be the same, but obviously that was because I was working on
the same machine running the server locally.
Where can I find a list of ODBC connection string parameters and their
meaning?
I just kind of figured these things out by GetConnect() from within C++.
Lisa
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:168a01c3df99$df95c270$a601280a@.phx.gbl...[QUOTE]
> Note that this can be controlled with the connection
> string, and is therefore not totally reliable. The client
> program can specify whatever name it fancies.
> Linchi
>
> server name running
> connecting to it.. this is
> in message
> around in system
> were connected to
> connected to a

list of clients connected to a database

I remember some time ago, coincidenteally, by browsing around in system
tables, I saw a place where I could see which clients were connected to
which database, allowing me to select all client names connected to a
particular database.
But I forgot where I found this (in SQL7).
Can someone tell me?
Lisasysprocesses table in master database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
I remember some time ago, coincidenteally, by browsing around in system
tables, I saw a place where I could see which clients were connected to
which database, allowing me to select all client names connected to a
particular database.
But I forgot where I found this (in SQL7).
Can someone tell me?
Lisa|||Try EXEC sp_who or EXEC sp_who2
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>|||Thanks, strange enough, under client name, it lists the server name running
the sql server rather than the name of the client connecting to it.. this is
weird.
Lisa
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
> sysprocesses table in master database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>
>|||Note that this can be controlled with the connection
string, and is therefore not totally reliable. The client
program can specify whatever name it fancies.
Linchi
>--Original Message--
>Thanks, strange enough, under client name, it lists the
server name running
>the sql server rather than the name of the client
connecting to it.. this is
>weird.
>Lisa
>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote
in message
>news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> sysprocesses table in master database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> http://vyaskn.tripod.com/
>> Is .NET important for a database professional?
>> http://vyaskn.tripod.com/poll.htm
>>
>>
>> "Lisa Pearlson" <no@.spam.plz> wrote in message
>> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
>> I remember some time ago, coincidenteally, by browsing
around in system
>> tables, I saw a place where I could see which clients
were connected to
>> which database, allowing me to select all client names
connected to a
>> particular database.
>> But I forgot where I found this (in SQL7).
>> Can someone tell me?
>> Lisa
>>
>>
>
>.
>|||HOST_NAME() depens on the ODBC connection string?
How? Is it the WSID parameter?
See, I always wondered what the difference was between WSID and SERVER as
they seemed to be the same, but obviously that was because I was working on
the same machine running the server locally.
Where can I find a list of ODBC connection string parameters and their
meaning?
I just kind of figured these things out by GetConnect() from within C++.
Lisa
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:168a01c3df99$df95c270$a601280a@.phx.gbl...
> Note that this can be controlled with the connection
> string, and is therefore not totally reliable. The client
> program can specify whatever name it fancies.
> Linchi
> >--Original Message--
> >Thanks, strange enough, under client name, it lists the
> server name running
> >the sql server rather than the name of the client
> connecting to it.. this is
> >weird.
> >
> >Lisa
> >
> >"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote
> in message
> >news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
> >> sysprocesses table in master database.
> >> --
> >> HTH,
> >> Vyas, MVP (SQL Server)
> >> http://vyaskn.tripod.com/
> >> Is .NET important for a database professional?
> >> http://vyaskn.tripod.com/poll.htm
> >>
> >>
> >>
> >>
> >> "Lisa Pearlson" <no@.spam.plz> wrote in message
> >> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> >> I remember some time ago, coincidenteally, by browsing
> around in system
> >> tables, I saw a place where I could see which clients
> were connected to
> >> which database, allowing me to select all client names
> connected to a
> >> particular database.
> >>
> >> But I forgot where I found this (in SQL7).
> >> Can someone tell me?
> >>
> >> Lisa
> >>
> >>
> >>
> >>
> >
> >
> >.
> >

list of cities across the globe with time zones

Is there any managed website which maintains the list of
cities across the globe and their time zones in it.
Thanks,You might want to check out a company called Melissa Data. They sell all
sorts of data.
I've gotten a Phone Number to Zip code database from them, and I was very
pleased.
Michael
"DBA" <dbarchitec@.hotmail.com> wrote in message
news:87c901c4329f$f3ad8280$a401280a@.phx.gbl...
> Is there any managed website which maintains the list of
> cities across the globe and their time zones in it.
> Thanks,

list of cities across the globe with time zones

Is there any managed website which maintains the list of
cities across the globe and their time zones in it.
Thanks,
You might want to check out a company called Melissa Data. They sell all
sorts of data.
I've gotten a Phone Number to Zip code database from them, and I was very
pleased.
Michael
"DBA" <dbarchitec@.hotmail.com> wrote in message
news:87c901c4329f$f3ad8280$a401280a@.phx.gbl...
> Is there any managed website which maintains the list of
> cities across the globe and their time zones in it.
> Thanks,

Friday, February 24, 2012

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!
select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!
|||Thank you Jasper!
"Jasper Smith" wrote:

> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
>
>

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!|||Thank you Jasper!
"Jasper Smith" wrote:

> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
>
>

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!|||Thank you Jasper!
"Jasper Smith" wrote:
> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> > Is there a way to list the owners of all databases? Right clicking in Ent
> > Manager and selecting properites is TOO time consuming!
>
>

Monday, February 20, 2012

list all of a set of rows when only one row is true

This is stupid, I used to be able to do this all the time by mistake now I can't do it on purpose

I want to be able to return a full list of matching records when only one is true

Like
Row 1, ID_1, false
Row 2, ID_1, false
Row 3, ID_1, true
Row 4, ID_2, false
Row 5, ID_2, true
Row 6, ID_2, false

I currently get
Row 3, ID_1, true
Row 5, ID_2, true

In order for us to help you, you need to give us better information. We dont know what you are trying to do, what query you are using/what version of SQL you are using etc. Please post all the relevant information.|||

I think I have got it, using a derived table

Using the results from the derived table to join to the main table and substituting the ClosedDate in the main table with the ClosedDate from the Derived table
Of course I have no idea if this is the best solution

SELECT

derivedEvents.ClosedDate, derivedEvents.IRef
FROM(SELECTClosedDate, IRef
FROMdbo.tblOCompEvents
WHERE(ClientID = ClientID)AND(IRef = IRef)AND(closed = 1))ASderivedEventsINNER JOIN
dbo.tblOCompEventsAStblOCompEvents_1ONderivedEvents.IRef = tblOCompEvents_1.IRef
ORDER BYderivedEvents.IRef|||

I have postedmy solution but any thing better... thanks for your interest

In my table I have events for client activity eventualy each series of events will be closed and a date inserted
the user wants to review all events for each client where the series has been closed, previously I could only return the closed row not the whole series

This is for a report which lists all events for all clients where the series of events has been closed

List a city only once

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

<asp:SqlDataSource ID="DataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

You can't select distinct on "text" datatypes.

Not only that, but text, ntext, and image datatypes are depricated and should no longer be used. Perhaps a datatype change is in order for your Location table.

http://msdn2.microsoft.com/en-us/library/ms187993.aspx|||So what should I do....I just want one of every city....and it must be of the type "text"....Am I right?|||You should probably use varchar or nvarchar datatypes.

You could probably cast your text column to varchar and then execute your select distinct. You could do that on the fly.

See if this works:
select distinct(cast(city as varchar(50))) from location|||

I changed the City from text to VarChar(50) in the database and then I used:

SelectCommand="SELECT DISTINCT(cast(City as varchar(50))) FROM [Location]">

Got the following message:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'City'

|||Alias the output.

select distinct(cast(city as varchar(50))) as city from location|||

Thanx it works!

What is the code doing, because I am gonna describe it for someone else?

|||It's converting the datatype of city from "text" to "varchar."

You can't select distinct on a column datatype of text, and as a result your column should have its datatype updated in the table, permanently.

So in the SQL I gave you, we are simply converting on-the-fly instead of making a permanent change.|||

Okey...sorry, but I thaught that I should change it to varchar(50) in the database too....And it was therefore it worked

Now I changed it back to text and I got the following:

Erroemassage:

The data types text and nvarchar are incompatible in the equal to operator

(but what is the different between text and varchar(50)?....)

|||Change it in the database, and then you don't need to execute my SQL. You're first "select distinct city from location" will work just fine then.

You should really change the text field to varchar (or nvarchar if you desire) in the database because the text datatype is no longer supported and is removed from the next version of SQL Server.

So, change it in the database, and ignore everything else I've written here.

As far as your latest error message, I presume you're doing something other than a select distinct (cast......) from location.

Phil|||...|||

Okey....So everything I should do is to change text to varchar(50) in the database....

What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

|||What?

For information on data types, please read: http://msdn2.microsoft.com/en-us/library/ms187752.aspx|||

Tigers21 wrote:
Okey....So everything I should do is to change text to varchar(50) in the database....
What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

Yes, make sure that the length of the varchar field is big enough to hold your data. I wouldn't use max... You aren't going to have a city name 8,000 characters long, are you?|||

Ok...Thank you so much!

(I saw the link you gave me now!).....thanx!

Friday, March 30, 2012

Load dts2000 fails

Wednesday, March 28, 2012

Load Balancing

Monday, March 26, 2012

Load balace issue

load all data without knowing old one was load in the previous time?

Live Webcast tomorrow Essential Team System for Database Developers

Friday, March 23, 2012

Little SQL icon gone in SQL Server 2005?

Little SQL icon gone in SQL Server 2005?

Little SQL icon gone in SQL Server 2005?

Wednesday, March 21, 2012

Listing jobs that ran last hour

LISTEN TO SEXY FREE NOW

Monday, March 19, 2012

List when Changes occur

Wednesday, March 7, 2012

list of clients connected to a database

list of clients connected to a database

list of cities across the globe with time zones

list of cities across the globe with time zones

Friday, February 24, 2012

List Database ownership

List Database ownership

List Database ownership

Monday, February 20, 2012

list all of a set of rows when only one row is true

List a city only once

List availables tables

Blog Archive

About Me