Tuesday, March 27, 2012
Failed to notify 'DBA' via email
notify us via email in case of job fails. However, Some time we get this
error "Failed to notify 'DBA' via email." Initially I thought it may be b/c
of mail server was not available but our exchnage server admin has looked at
exchange logs and not found any thing at that time. I also looked at the SQL
server logs and did not find any issues. My question is: is there any thing
can go wrong in the SQL server that can cause this probelm?
Help is appreciated.
Thanks,
MohMoh,
FWIW - SQL Server MAPI will only wait a certain amount of time to deliver a
message. If it cannot get out, then the mail will 'fail' even though
Exchange is up and running and healthy. It might be Exchange, networking,
domain issues, etc. that cause the problem.
If you Google on "sql mail Failed to notify" you will see a few comments on
this, without any real help that I could find.
(Unless xp_smtp_sendmail would work for you.)
RLF
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:eiv3swh3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> We have some SQL jobs those run on a schedule basis. These jobs are setup
to
> notify us via email in case of job fails. However, Some time we get this
> error "Failed to notify 'DBA' via email." Initially I thought it may be
b/c
> of mail server was not available but our exchnage server admin has looked
at
> exchange logs and not found any thing at that time. I also looked at the
SQL
> server logs and did not find any issues. My question is: is there any
thing
> can go wrong in the SQL server that can cause this probelm?
> Help is appreciated.
> Thanks,
> Moh
>|||"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ePA64pj3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Moh,
> FWIW - SQL Server MAPI will only wait a certain amount of time to deliver
a
> message. If it cannot get out, then the mail will 'fail' even though
> Exchange is up and running and healthy. It might be Exchange, networking,
> domain issues, etc. that cause the problem.
> If you Google on "sql mail Failed to notify" you will see a few comments
on
> this, without any real help that I could find.
Generally I find if you don't absolutely need to use Exchange, setup a local
SMTP server right on teh SQL box and send email through that directly.
Eliminates timeouts, etc.
> (Unless xp_smtp_sendmail would work for you.)
> RLF
> "Moh Abb" <mabbas@.aligntech.com> wrote in message
> news:eiv3swh3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > We have some SQL jobs those run on a schedule basis. These jobs are
setup
> to
> > notify us via email in case of job fails. However, Some time we get this
> > error "Failed to notify 'DBA' via email." Initially I thought it may be
> b/c
> > of mail server was not available but our exchnage server admin has
looked
> at
> > exchange logs and not found any thing at that time. I also looked at the
> SQL
> > server logs and did not find any issues. My question is: is there any
> thing
> > can go wrong in the SQL server that can cause this probelm?
> >
> > Help is appreciated.
> >
> > Thanks,
> > Moh
> >
> >
>
>
Friday, March 23, 2012
failed to connect to the server
When I have a subscription built for a report it runs at the time expected, but the report send via e-mail fails. I can run the report from a client workstation just fine. I can also run it from the admin tool fine too, it just will not run as part of a subscription. The full context of the error message is as follows:
Failure sending mail: The transport failed to connect to the server
This is not my first Reporting services report, but I am trying to replace all Crystal reports with Reporting services reports and this functionality is greatly needed.
Thanks! - Eric-
The error is telling you the RS server cannot connect to the Mail server. Make sure that the unattended execution account is setup and is a user that has access to the mail server.
Here is a link to help you.
http://msdn2.microsoft.com/en-us/library/ms155851.aspx
Wednesday, March 21, 2012
Failed to add Time dimension to the cube
HI, all experts here,
Thank you very much for your kind attention.
I am trying to add a time intelligence to the cube within my SQL Server 2005 Analysis Services. But first of all, I need to add the Time dimension as required. When I tried to add the time dimension, it failed and got the error message as :"Object referenced not set to an instance of an object (Microsoft.Analysis Services.Wizard)."
Why is that? And what probably is the cause of it? Please any experts here give me some help on it. I have strgguled ages for it.
Thank you very much and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hello Helen. Please tell the newsgroup of each step before this error appears!
Where, exactly did this error appear? Where you finished with creating the dimension?
Regards
Thomas
|||Hi, Thomas,
Thank you very much for your help.
I was not able to create the time dimension since I was trying to add the time dimension after the creation of the cube. I could only create the time dimension while during the process of the cube creation. Why is that? Is this the way SQL Server 2005 works? Or I have missed out something?
I am looking forward to hearing from you further shortly and thank you very much again.
With best regards,
Yours sincerely,
|||To answer my own question
The reason I failed to create the time dimension for my cube is that-I did not have a time table in the data source view. I added the time table there along with the relationships creation between the time table and other fact and dimension tables in the data source view, and then being able to create the time dimension.
Hope it is clear.
With best regards,
Yours sincerely,
Wednesday, March 7, 2012
Fact Table Design for Hours Worked
I am trying to design a cube with Date, Time and Employee dimension
which can answer following questions:
- how many employees were working at 10am on 15 June 2007? I
- how many man hours were pais between 10am -11am on 15 June 2007. If
somebody has punched in at 10:30 then he should be counted and .5 man
hours.
One solution i can think of is every minute of everyhour for every
employee entered as a row in fact table. In this case if a employees
works for 8 hours that will create 8*60= 480 records in fact table. So
this may not be a good solution.
Ideally it would be better if we can enter one record in fact table
with start time and stop time. But, how can we put corrective
transaction if we need to cancel or change the original transactions.
Any help will be appreciated.
Thanks
Maheshyou can use 2 hour dimension:
Start Time and End Time
to answer:
> - how many employees were working at 10am on 15 June 2007? I
you select the day and all the start times up to 10am and all the end times
after 10am and you have the result.
for the second one, you have to create some formula.
maybe you can create a new measure group which contains only the start time
/ end time , and, for each combination, the duration in minute of the
selected period (ie you select 10h30 and 11h45, the measure returns
75minutes) (call this measure NoMinutesInPeriod)
now, in the cube, you have to create a formula which apply a ratio if the
start time or the end time is between the selected period of time.
measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
the datediff formula will be more complicated then a simple datediff.
but maybe this idea is a good starting point for you.
or anybody else has a better solution... please share your ideas :-)
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
> Hi,
> I am trying to design a cube with Date, Time and Employee dimension
> which can answer following questions:
> - how many employees were working at 10am on 15 June 2007? I
> - how many man hours were pais between 10am -11am on 15 June 2007. If
> somebody has punched in at 10:30 then he should be counted and .5 man
> hours.
> One solution i can think of is every minute of everyhour for every
> employee entered as a row in fact table. In this case if a employees
> works for 8 hours that will create 8*60= 480 records in fact table. So
> this may not be a good solution.
> Ideally it would be better if we can enter one record in fact table
> with start time and stop time. But, how can we put corrective
> transaction if we need to cancel or change the original transactions.
> Any help will be appreciated.
> Thanks
> Mahesh
>|||Thanks Jeje. Your solution seems to be the best solution. The only
problem with this is the difficulty to add corrective transactions.
For example, if hours worked is entered as 8am - 5pm on June 5 for an
employee. On June 25 if it is changed to be 10am - 5pm, how can we
enter a new corrective transaction on June 25 without changing the
original transaction. We do not want to change the original
transaction to use Incrementa Processing on cubes.
Any response will be very much appreciated.
Mahesh
On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
> you can use 2 hour dimension:
> Start Time and End Time
> to answer:> - how many employees were working at 10am on 15 June 2007? I
> you select the day and all the start times up to 10am and all the end time
s
> after 10am and you have the result.
> for the second one, you have to create some formula.
> maybe you can create a new measure group which contains only the start tim
e
> / end time , and, for each combination, the duration in minute of the
> selected period (ie you select 10h30 and 11h45, the measure returns
> 75minutes) (call this measure NoMinutesInPeriod)
> now, in the cube, you have to create a formula which apply a ratio if the
> start time or the end time is between the selected period of time.
> measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
> the datediff formula will be more complicated then a simple datediff.
> but maybe this idea is a good starting point for you.
> or anybody else has a better solution... please share your ideas :-)
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||if you want to "cancel" a transaction already loaded in a cube.
you have to add another "transaction" with "negative values"
for example:
initially you load the cube using this:
June 25 / 8am - 5pm / +1 (+1 work)
you want to "cancel" this transaction, and replace by a new one:
June 25 / 8am - 5pm / -1 (-1 work)
June 25 / 10am - 5pm / +1 (+1 work)
so the total in the cube is:
June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
June 25 / 10am - 5pm / +1
but using this approach you can't use the "non empty" because the cell is
not empty, the cell contains a total of 0.
Also remember to do full processes at a regular basis because an incremental
loading reduce the cube performance.
good luck!
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
> Thanks Jeje. Your solution seems to be the best solution. The only
> problem with this is the difficulty to add corrective transactions.
> For example, if hours worked is entered as 8am - 5pm on June 5 for an
> employee. On June 25 if it is changed to be 10am - 5pm, how can we
> enter a new corrective transaction on June 25 without changing the
> original transaction. We do not want to change the original
> transaction to use Incrementa Processing on cubes.
> Any response will be very much appreciated.
> Mahesh
>
> On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
>|||Thanks you very much for your kind response, Jeje.
Mahesh
On Jun 25, 9:27 am, "Jeje" <willg...@.hotmail.com> wrote:
> if you want to "cancel" a transaction already loaded in a cube.
> you have to add another "transaction" with "negative values"
> for example:
> initially you load the cube using this:
> June 25 / 8am - 5pm / +1 (+1 work)
> you want to "cancel" this transaction, and replace by a new one:
> June 25 / 8am - 5pm / -1 (-1 work)
> June 25 / 10am - 5pm / +1 (+1 work)
> so the total in the cube is:
> June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
> June 25 / 10am - 5pm / +1
> but using this approach you can't use the "non empty" because the cell is
> not empty, the cell contains a total of 0.
> Also remember to do full processes at a regular basis because an increment
al
> loading reduce the cube performance.
> good luck!
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Sunday, February 26, 2012
Facing Problem
After some time my application stop working goes in to the not
responding stage. Then I have to KILL some session which are SPID > 51.
After this my problem is getting resolved after this I can able to
create new sessions. Please help me ot with this issues.
regards,
ShaileshYou've really told us nothing here. Have you determined if there is a
blocked process? what happens if you run sp_who?
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"VU2LOC" <vu2loc@.gmail.com> wrote in message
news:1137493777.837548.11200@.g47g2000cwa.googlegro ups.com...
Well I am the MS-SQL user I always face the problem in my application.
After some time my application stop working goes in to the not
responding stage. Then I have to KILL some session which are SPID > 51.
After this my problem is getting resolved after this I can able to
create new sessions. Please help me ot with this issues.
regards,
Shailesh|||Hi Shailesh,
Check out if there are any transactions not being committed that may be
causing blocks..
Check for open tran, DBCC OPENTRAN - look it up in books online to
understand its use.
Run sp_who2 and check for any spids that are blocked, a number in the blk
column.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"VU2LOC" <vu2loc@.gmail.com> wrote in message
news:1137493777.837548.11200@.g47g2000cwa.googlegro ups.com...
> Well I am the MS-SQL user I always face the problem in my application.
> After some time my application stop working goes in to the not
> responding stage. Then I have to KILL some session which are SPID > 51.
> After this my problem is getting resolved after this I can able to
> create new sessions. Please help me ot with this issues.
> regards,
> Shailesh
Extremely Slow Table
I have a table defined as
CREATE TABLE [SH_Data] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[TroubleshootId] [int] NOT NULL ,
[ReasonID] [int] NULL ,
[reason_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[maj_reason_id] [int] NULL ,
[maj_reason_desc] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[ActionID] [int] NULL ,
[action_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[WinningCaseTitle] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[Duration] [int] NULL ,
[dm_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[ConnectMethod] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[dm_motive] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[HnWhichWlan] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[RouterUsedToConnect] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[OS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WinXpSp2Installed] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[Login] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
,
[EnteredBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[Acct_Num] [int] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
CONSTRAINT [PK_SH_Data] PRIMARY KEY CLUSTERED
(
[TroubleshootId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Which contains 5.6 Million rows and has non clustered indexes on Date,
ReasonID, maj_Reason, Connection. Compared to other tables on the same
server this one is extremely slow. A simple query such as :
SELECT
SD.reason_desc,
SD.Duration,
SD.maj_reason_desc,
SD.[Connection],
SD.aolEnteredBy
FROM dbo.[Sherlock Data] SD
Where SD.[Date] > Dateadd(Month,-2,Getdate())
takes over 2 minutes to run ! I realise the table contains several
large columns which make the table quite large but unfortunately this
cannot be changed for the moment.
How can i assess what is causing the length of Query time ? And what
could i possibly do to speed this table up ? The database itself is
running on a dedicated server which has some other databases. None of
which have this performance issue.
Anyone have any ideas ?Do other queries which benefit of indexes also have bad performance ?
Where SD.[Date] > Dateadd(Month,-2,Getdate())
does a row based comparison, not using the indexes or what does the
query plan tells you about it ?
(Markt the query in QA and press CTRL+L) to see it.
Jens Suessmeyer.|||One possiblility is to change the PK to nonclustered and the index on
[Date] to clustered. If you often do range/grouping queries based on
[Date] then that should be useful, but it might also impact queries
using TroubleshootId, so you need to test any change with a number of
representative queries.
Other general advice would be to review the query plan in QA (Ctrl+K),
run UPDATE STATISTICS on the table, and also try tracing a typical
workload and running it through the Index Tuning Wizard to see what it
can recommend.
If you need more specific comments, you should post the query plan
(using SET SHOWPLAN_TEXT), and it might also be useful to know how many
rows are returned by the query.
Simon|||Yea ive tried a test on both and a query using the indexes take about
20 seconds less to run.
I tried the CTRL + L but its not making much sense to me.|||garydevstore (GaryDataStore@.gmail.com) writes:
> Which contains 5.6 Million rows and has non clustered indexes on Date,
> ReasonID, maj_Reason, Connection. Compared to other tables on the same
> server this one is extremely slow. A simple query such as :
Maybe some terminology is in order here. A road can be fast, but that
does not help you, if you car has a steering wheel out of order causing
you to zig-zag over the road. A car can be fast, but that does not help
if the road is in poor condition, so you cannot driver faster than 30 km/h
anyway.
In this case, the table is the road, and the query plan is the car. A
table itself does not move, but it can be badly fragmented in which case
it can be slow to drive through.
More likely, the query plan is not the best for the query. This is your
query:
> SELECT
> SD.reason_desc,
> SD.Duration,
> SD.maj_reason_desc,
> SD.[Connection],
> SD.aolEnteredBy
> FROM dbo.[Sherlock Data] SD
> Where SD.[Date] > Dateadd(Month,-2,Getdate())
There is a non-clustered index on Date. Assuming that rows are added
to this table regularly, there are presumably quite a few rows that
fits this condition. There are two ways for the optimizer to evaluate
this query: using the index, or scanning the table. The index is good
if only few rows are hit, but if many rows are hit the table scan is
faster. This is because, with the index you will need to read the same
page more than once.
The optimizer makes it choice of plan from the statistics SQL Server
has sampled about the table. The statistics may be out of date (even
if by default SQL Server auto-updates statistics). Try an UPDATE
STATISTICS WITH FULLSCAN, to see if this makes any difference.
But the road can also be in poor condition, that is the table can be
badly fragmented. This can be analysed with DBCC SHOWCONTIG and
remedied with DBCC DBREINDEX.
As suggested in other posts, you should look at the query plan, and see
if it says Clustered Index Scan or Index Seek + Bookmark Lookup.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simon Hayes wrote:
> One possiblility is to change the PK to nonclustered and the index on
> [Date] to clustered. If you often do range/grouping queries based on
> [Date] then that should be useful, but it might also impact queries
> using TroubleshootId, so you need to test any change with a number of
> representative queries.
A covering index might be an option, too, especially if there are several
queries with multiple criteria.
One question to the OP: why do you have Date and Time both as timestamp
columns? Other remarkable things: all char columns seem to be unicode
(nvarchar) and have length (255). You might save space by changing to
varchar (if possible) and / or reducing the length. Also, this doesn't
really look like a normalized schema. I would at least expect having ids
for EnteredBy and probably some others.
> Other general advice would be to review the query plan in QA (Ctrl+K),
> run UPDATE STATISTICS on the table, and also try tracing a typical
> workload and running it through the Index Tuning Wizard to see what it
> can recommend.
> If you need more specific comments, you should post the query plan
> (using SET SHOWPLAN_TEXT), and it might also be useful to know how
> many rows are returned by the query.
Plus complete index DDL.
robert
Extremely slow Excel MDX
Using Excel as a client is most of the time exceedingly slow. For example writing a simple query of the type:
SELECT [Measures].[Some Measure] ON 0,
[Product].[Product-Version].[Product] ON 1
FROM [Cubename]
in Management studio is in Excel transformed to:
SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product-Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Cubename] WHERE ([Measures].[Some Measure])
which takes several times longer to execute. As one starts drilling down it becomes increasingly worse with excel producing MDX that takes 100:s of times longer to execute then if I handwrite the mdx. This is with a very simple cube where Some Measure is not a calculated member. I can't even begin to imagine how slow it would be with a more complex cube. Is there anything to be done about this, any guidelines to follow to make it easer for Excel to generate "normal" mdx?
I had similar problem with Excel and OWC when accessing OLAP Cubes. Unfortunately, unless you optimize your cube, there is nothing can be done with how Excel generate mdx to retrieve data. Excel and OWC is closed code, and recently Microsoft announced that they will be stopping new releases for OWC. Howerver, Excel PivotTable has new version, take a look at Office 2007 in Beta version. It generates more efficient MDXs compared to Office 2003/2000.
Downside, it will take another 2-3 years for Office 2007 to be as popular as Office 2003, so, distribution of your solution in Office 2007 might be an issue if u decide to switch to Office 2007 in larger scale enterprise.
|||Thanks. Is there anything special you have in mind when you say "unless you optimize your cube"? Or do you mean the "ordinary" optimizations one does to make the server work decently fast? I will try to have a look at the 2007 beta though.|||As mentioned earlier, you can take a look at Office 2007 sending bit different MDX queries. You also take a look at the ProClarity recently aquired by Microsoft see if you get better performance using it.
Also make sure you install latest service pack - SP1. There has been some performance improvements in it. You will see event more performance improvements in upcoming service pack 2. Watch for announcements of Community Technology Preview (CTP) to get your hands on upcoming SP2.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Extremely Slow connection time
If absolutely nothing changed on the server and everyone=20
else is working fine...then its your desktop.
Have you looked the CPU, Memory usage on your desktop?=20
Your desktop connection could be dropping connection to=20
SQL Server, so it could be a network issue. Try pinging=20
the SQL Server with -t option.
If server and everyone else is OK, concentrate on your=20
desktop. Maybe there was a change in the network itself.
One thing you can try is maybe create a alias for the sql=20
server.=20
You can do that by :start\programs\microsoft sql=20
server\client network utility. Once the utility opens up,=20
go to the second tab (Alias) and click on 'add'. Enter the=20
server name and alias name and either use 'Named Pipes' or=20
TCP/IP. DO NOT change anything for the either of the=20
default setting unless you know for fact they have been=20
changed.
Try using 'named pipes', if performance does not increase,=20
try to use tcp/ip.
hth
DeeJay
hth
DeeJay
>--Original Message--
>Hi
>I have recently reinstalled XP Professional as well as=20
the MSSQL client tools.
>Our SQL 2000 server is running Win2K with 512MB RAM, and=20
has been doing so for quite a while, no changes where done=20
to the server while I reinstalled XP on my PC.
>Now my connection to the SQL server is extremely=20
sluggish, e.g. connecting via the Query Analyzer takes up=20
to 2 minutes, and so do any other subsequent actions, for=20
example opening the list of databases, tables or stored=20
procedures on the object browser.
>All my queries take ages to get going even for the=20
simplest queries.
>Previously the response was very reasonable, as are the=20
other developers PCs in the company, with similar set up.
>I have tried using both forms of security but have no joy=20
either way. I have used the IP address =E2?" no go.
>If I ping the NetBIOS name of the server the response is=20
instantaneous.
>
>Please help!!
>.
>Hi
Thanks for the response
I had already loaded the latest service packs and already set up the network
client utility, and tried bothTCP/IP as well as named pipes, all of these h
ad no effect.
Regards
Herbert|||Are other people able to connect to the server without any
problem?
If yes, then its your PC. Perhaps the XP re-install did
not work properly.
DeeJay
>--Original Message--
>Hi
>Thanks for the response
>I had already loaded the latest service packs and already
set up the network client utility, and tried bothTCP/IP as
well as named pipes, all of these had no effect.
>Regards
>Herbert
>.
>
Extremely Slow connection time
I have recently reinstalled XP Professional as well as the MSSQL client tool
s.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so f
or quite a while, no changes where done to the server while I reinstalled XP
on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting v
ia the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored procedu
res on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs
in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have
used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>
Extremely Slow connection time
If absolutely nothing changed on the server and everyone=20
else is working fine...then its your desktop.
Have you looked the CPU, Memory usage on your desktop?=20
Your desktop connection could be dropping connection to=20
SQL Server, so it could be a network issue. Try pinging=20
the SQL Server with -t option.
If server and everyone else is OK, concentrate on your=20
desktop. Maybe there was a change in the network itself.
One thing you can try is maybe create a alias for the sql=20
server.=20
You can do that by :start\programs\microsoft sql=20
server\client network utility. Once the utility opens up,=20
go to the second tab (Alias) and click on 'add'. Enter the=20
server name and alias name and either use 'Named Pipes' or=20
TCP/IP. DO NOT change anything for the either of the=20
default setting unless you know for fact they have been=20
changed.
Try using 'named pipes', if performance does not increase,=20
try to use tcp/ip.
hth
DeeJay
hth
DeeJay
>--Original Message--
>Hi
>I have recently reinstalled XP Professional as well as=20
the MSSQL client tools.
>Our SQL 2000 server is running Win2K with 512MB RAM, and=20
has been doing so for quite a while, no changes where done=20
to the server while I reinstalled XP on my PC.
>Now my connection to the SQL server is extremely=20
sluggish, e.g. connecting via the Query Analyzer takes up=20
to 2 minutes, and so do any other subsequent actions, for=20
example opening the list of databases, tables or stored=20
procedures on the object browser.
>All my queries take ages to get going even for the=20
simplest queries.
>Previously the response was very reasonable, as are the=20
other developers PCs in the company, with similar set up.
>I have tried using both forms of security but have no joy=20
either way. I have used the IP address =E2?" no go.
>If I ping the NetBIOS name of the server the response is=20
instantaneous.
>
>Please help!!
>.
>
Hi
Thanks for the response
I had already loaded the latest service packs and already set up the network client utility, and tried bothTCP/IP as well as named pipes, all of these had no effect.
Regards
Herbert
|||Are other people able to connect to the server without any
problem?
If yes, then its your PC. Perhaps the XP re-install did
not work properly.
DeeJay
>--Original Message--
>Hi
>Thanks for the response
>I had already loaded the latest service packs and already
set up the network client utility, and tried bothTCP/IP as
well as named pipes, all of these had no effect.
>Regards
>Herbert
>.
>
Extremely Slow connection time
I have recently reinstalled XP Professional as well as the MSSQL client tools.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so for quite a while, no changes where done to the server while I reinstalled XP on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting via the Query Analyzer takes up to 2 minutes, and so do any other subsequent actions, for example opening the list of databases, tables or stored procedures on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!
Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>
eXtremely Long Time in Execution Query
Hi all,
I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?
Thanks,
Ning
Reminds me of some hotfix descriptions - Have you checked knowledgebase articles for "slow query"? Here's an example that relates to fast forward-only queries:
"FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005": http://support.microsoft.com/default.aspx/kb/926024
|||I found the reason, but not sure why, either do I have a way to fix that.
In one of my select sentence, I have a field, nvarchar(2000), if I exclude that field in the select statement then everything is back to speed. Otherwise, we are cralwing like 1000 lines per minute, so 1 million lines will be 1000 minute ... (without that field we are at 100,000 per minute). I am not sure why a field can cause such a big deal 100 times in performance diff ...
Microsoft has to explain this ...
Thanks,
Ning
|||I am wondering if it is joining to the table containing this field early in the query and so having to carry the up to 2K a row of data round (for a million rows - that's up to 2G). This would probably show as an explosion in the size of tempdb (as this is where it is likely to be caching this data during the query).
Was it just the field or the table providing the field which you removed from the query. If it was the table you might try putting it at the bottom of the from clause and using the FORCE ORDER Query hint. However this will prevent the query planner rearranging any of the tables so you might want to put them in the order specified by the plan for the fast query without that field in.
Extremely long processing time
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?
is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
Extremely long processing time
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
Friday, February 24, 2012
Extremely high % Disk Time .. How is this possible?
SYSTEM:
Clustered SQL Server 2000 on Windows 2000 (2 servers), with RAID 1+0, 6
drives (Total of 24 20GB hard drives with mirroring/striping). 4 GB RAM /
Server. All fiber back end, with Fibre Channel Storage Hub 7 ( @. 100
MB/sec ). Our SQL servers have fiber cards @. 400 MB/sec connected to hub.
About 50 DBases, ranging in size from about 50-100 MB up to 6 GB. They are
shared by 15 clients (they are fairly balanced over a 24 hour period, when 1
client is busy, the other isn't). The total size of dbases is ~ 65 GB, Log
File ~ 35 GB. 100-250 connected users (to our application), with 300-500
SQL connections. Dual XEON 2.8Ghz Processors / Server.
PROBLEM:
We are experiencing disk problems but are having a very difficult time
diagnosing 'exactly' what is the cause and solution. On the data drives,
thru PerfMon, the % Disk Times are averaging between 250 and 1500, with
peaks of over 30,000 (How do I get 30,000 % disk use?). Average disk
queues are setting at about 100-250 during the problem times, but
occasionally see it spike well over 1000. The transactions/sec hover
between 50-100. Processor hovers between 10 and 40%, spiking to 70-80%.
The disk bytes/sec for the data drives average betwwen 1 and 6 MBytes/sec.
I know there are a lot of variables that can be at play here, but where
should I start? Is the system overall too small? Over worked? We did have
memory issues but increased the RAM and there no longer appears to be buffer
cache problems and we have not yet exceeded the available RAM. We have
removed most of our temp tables (in favor of table variables) and removed a
lot of the cursors that were in the system. We moved our reporting server
to it's own box (completely seperate from prod servers). We reduced the log
file backup intervals and staggered our database backup jobs. We converted
portions of our reporting to Analysis services. All with short term
marginal improvements. We have to speed our system up before we start
losing clients. Does it sound/appear as though the next option would be to
scale out? scale up? Goto a SAN environment? And info and advise would be
greatly appreciated!
If you got this far, thanks for the time in reading this long post!!! And am
anxious to read any responses. Thank you.
It sure sounds like your storage array is not able to handle the load.
Having a Raid 1+0 is great but only having 6 drives is on the low end.
Since you are not on a SAN you only have the cache on the controllers
themselves to help deal with all the writes. Make sure the cache on the
controllers is set to 100% write back and not read. It also sounds like you
only have a single drive array and everything is on it (logs , data and
tempdb). That also is not good. You should have at least a Raid1 or
Raid1+0 for just the log files. The spikes can be caused by lots of things
but you are probably seeing some of this due to checkpoints. You can use
the Checkpoint pages per second perfmon counter to correlate this to the
disk queue spikes. You might also try using connection pooling instead of
that many individual connections. You will need multiple pools to handle
the different dbs but it should cut down the actual number of connections
quite a bit.Have you ran any traces to see what kind of queries are being
run and who the worst offenders are? You may have a few really poorly tuned
queries that are hurting everything else.
Andrew J. Kelly SQL MVP
"Tim" <tim@.pds.notanaddress.ca> wrote in message
news:%23v7AhlsDFHA.2804@.TK2MSFTNGP14.phx.gbl...
> Hi Group
> SYSTEM:
> Clustered SQL Server 2000 on Windows 2000 (2 servers), with RAID 1+0, 6
> drives (Total of 24 20GB hard drives with mirroring/striping). 4 GB RAM /
> Server. All fiber back end, with Fibre Channel Storage Hub 7 ( @. 100
> MB/sec ). Our SQL servers have fiber cards @. 400 MB/sec connected to hub.
> About 50 DBases, ranging in size from about 50-100 MB up to 6 GB. They
> are
> shared by 15 clients (they are fairly balanced over a 24 hour period, when
> 1
> client is busy, the other isn't). The total size of dbases is ~ 65 GB,
> Log
> File ~ 35 GB. 100-250 connected users (to our application), with 300-500
> SQL connections. Dual XEON 2.8Ghz Processors / Server.
> PROBLEM:
> We are experiencing disk problems but are having a very difficult time
> diagnosing 'exactly' what is the cause and solution. On the data drives,
> thru PerfMon, the % Disk Times are averaging between 250 and 1500, with
> peaks of over 30,000 (How do I get 30,000 % disk use?). Average disk
> queues are setting at about 100-250 during the problem times, but
> occasionally see it spike well over 1000. The transactions/sec hover
> between 50-100. Processor hovers between 10 and 40%, spiking to 70-80%.
> The disk bytes/sec for the data drives average betwwen 1 and 6 MBytes/sec.
> I know there are a lot of variables that can be at play here, but where
> should I start? Is the system overall too small? Over worked? We did
> have
> memory issues but increased the RAM and there no longer appears to be
> buffer
> cache problems and we have not yet exceeded the available RAM. We have
> removed most of our temp tables (in favor of table variables) and removed
> a
> lot of the cursors that were in the system. We moved our reporting server
> to it's own box (completely seperate from prod servers). We reduced the
> log
> file backup intervals and staggered our database backup jobs. We
> converted
> portions of our reporting to Analysis services. All with short term
> marginal improvements. We have to speed our system up before we start
> losing clients. Does it sound/appear as though the next option would be
> to
> scale out? scale up? Goto a SAN environment? And info and advise would
> be
> greatly appreciated!
> If you got this far, thanks for the time in reading this long post!!! And
> am
> anxious to read any responses. Thank you.
>
|||I do have 5 logical drives, 1 for logs and 1 for dbases for each server, and
a quorum drive. Been evaluating the benefits of adding a dedicated array
for tempDB and indexes. Roughly 30-40 % of ransactions occur in tempDB
(still have a lot of tempDbases as it seems to be the most efficient way to
maintain/handle the passing of tables between stored procedures. In many
cases, there is no easy way to eliminate the passing of the tables as a
couple of stored procedures (the big offenders) require a large amount of
processing.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e65DCOtDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> It sure sounds like your storage array is not able to handle the load.
> Having a Raid 1+0 is great but only having 6 drives is on the low end.
> Since you are not on a SAN you only have the cache on the controllers
> themselves to help deal with all the writes. Make sure the cache on the
> controllers is set to 100% write back and not read. It also sounds like
you
> only have a single drive array and everything is on it (logs , data and
> tempdb). That also is not good. You should have at least a Raid1 or
> Raid1+0 for just the log files. The spikes can be caused by lots of
things
> but you are probably seeing some of this due to checkpoints. You can use
> the Checkpoint pages per second perfmon counter to correlate this to the
> disk queue spikes. You might also try using connection pooling instead of
> that many individual connections. You will need multiple pools to handle
> the different dbs but it should cut down the actual number of connections
> quite a bit.Have you ran any traces to see what kind of queries are being
> run and who the worst offenders are? You may have a few really poorly
tuned[vbcol=seagreen]
> queries that are hurting everything else.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Tim" <tim@.pds.notanaddress.ca> wrote in message
> news:%23v7AhlsDFHA.2804@.TK2MSFTNGP14.phx.gbl...
/[vbcol=seagreen]
hub.[vbcol=seagreen]
when[vbcol=seagreen]
300-500[vbcol=seagreen]
drives,[vbcol=seagreen]
MBytes/sec.[vbcol=seagreen]
removed[vbcol=seagreen]
server[vbcol=seagreen]
would[vbcol=seagreen]
And
>
|||As Andrew pointed out, it is the physical drives that appear to be limited.
Slicing them into logical drives may look better but it does not increase IO
capability. Separating data and logs onto different physical disks with the
correct RAID layouts will increase performance. Andrew also suggested
profiling for performance to find if there are just a few really ugly
queries that are killing your system. I agree completely.
As for the original question on Percent IO Time, that counter has been
largely irrelevent since SCSI Overlapped IO was introduced way back when.
Caching controllers really killed it. I prefer to look at disk queue length
and read bytes/write bytes per second as true measures of disk performance.
I believe that you will be running with write cache off in a non-SAN cluster
to prevent data loss during a cluster failover. This is a big performance
killer and one major reason I do not recommend a cluster without a SAN. All
indicators are that you need to modify your IO system. You may find other
performance bottlenecks as you remove the IO limit, but for right now, that
is the worst offender.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tim" <tim@.pds.notanaddress.ca> wrote in message
news:%23kwePe3DFHA.2608@.TK2MSFTNGP10.phx.gbl...
> I do have 5 logical drives, 1 for logs and 1 for dbases for each server,
and
> a quorum drive. Been evaluating the benefits of adding a dedicated array
> for tempDB and indexes. Roughly 30-40 % of ransactions occur in tempDB
> (still have a lot of tempDbases as it seems to be the most efficient way
to[vbcol=seagreen]
> maintain/handle the passing of tables between stored procedures. In many
> cases, there is no easy way to eliminate the passing of the tables as a
> couple of stored procedures (the big offenders) require a large amount of
> processing.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e65DCOtDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> you
> things
use[vbcol=seagreen]
of[vbcol=seagreen]
handle[vbcol=seagreen]
connections[vbcol=seagreen]
being[vbcol=seagreen]
> tuned
6[vbcol=seagreen]
RAM[vbcol=seagreen]
> /
> hub.
They[vbcol=seagreen]
> when
GB,[vbcol=seagreen]
> 300-500
> drives,
with[vbcol=seagreen]
70-80%.[vbcol=seagreen]
> MBytes/sec.
where[vbcol=seagreen]
did[vbcol=seagreen]
have[vbcol=seagreen]
> removed
> server
the[vbcol=seagreen]
be
> would
> And
>
|||Completely agree with Geoff.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%237$Gb13DFHA.2824@.tk2msftngp13.phx.gbl...
> As Andrew pointed out, it is the physical drives that appear to be
> limited.
> Slicing them into logical drives may look better but it does not increase
> IO
> capability. Separating data and logs onto different physical disks with
> the
> correct RAID layouts will increase performance. Andrew also suggested
> profiling for performance to find if there are just a few really ugly
> queries that are killing your system. I agree completely.
> As for the original question on Percent IO Time, that counter has been
> largely irrelevent since SCSI Overlapped IO was introduced way back when.
> Caching controllers really killed it. I prefer to look at disk queue
> length
> and read bytes/write bytes per second as true measures of disk
> performance.
> I believe that you will be running with write cache off in a non-SAN
> cluster
> to prevent data loss during a cluster failover. This is a big performance
> killer and one major reason I do not recommend a cluster without a SAN.
> All
> indicators are that you need to modify your IO system. You may find other
> performance bottlenecks as you remove the IO limit, but for right now,
> that
> is the worst offender.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Tim" <tim@.pds.notanaddress.ca> wrote in message
> news:%23kwePe3DFHA.2608@.TK2MSFTNGP10.phx.gbl...
> and
> to
> use
> of
> handle
> connections
> being
> 6
> RAM
> They
> GB,
> with
> 70-80%.
> where
> did
> have
> the
> be
>
Extracting Time from the SQL DateTime field.
database.
even though i had entered only the time part in the database when i
extract the field it gives me only the date part. im using Vb.net
datagrid as a front end.
any assistance appreciated!! :?:
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict254266.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989"v0lcan0" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_877989_087c9582517dd6a1f7813c2a746cac72@.dbf orumz.com...
> Any help on extracting the time part from the datetime field in SQL
> database.
> even though i had entered only the time part in the database when i
> extract the field it gives me only the date part. i'm using Vb.net
> datagrid as a front end.
> any assistance appreciated!! :?:
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/General-Dis...pict254266.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=877989
See CONVERT() in Books Online:
select convert(char(8), getdate(), 108)
For more general information about working with datetime data, see here:
http://www.karaszi.com/sqlserver/info_datetime.asp
Simon
Sunday, February 19, 2012
Extracting from a DATETIME data type
I can't figure out how to extract just the date or just the time from a DATETIME data type? Any suggestions?SELECT CONVERT(varchar(10),GetDate(),101)
SELECT CONVERT(varchar(10),GetDate(),108)|||Thanks.
Friday, February 17, 2012
extracting data from a varchar variable?
I have some data like this one:
'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'
P.S: the values may vary each time.
I need to programmatically extract from that kind of string only file1.doc
Ho can I dynamically do it? any function? statement? etc? please help.
The idea is getting only the string after the last "\"
Thanks
Frdeclare @.t varchar(256)
select @.t = 'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'
select reverse(substring(reverse(@.t),1,patindex('%\%',reverse(@.t))-1))