How to manage all the SqlNK versions at the same time?

Hi everyone,

Nowadays, we've got four sql versions running around: 6.5, 7.0, 2000 and 2005.

If you try attach from Enterprise Manager 2005 servers it doesn't allow you because of 2005 uses SMO instead of DMO.

If you try attach from Management Studio 6.5 or 7.0 servers it doesn't allow you too.

We'd like to have from the same place a tool for all of them. Is it possible?

Thanks in advance,

I have client tools installed for SQL 6.5, 2000 & 2005 in order to manage our environment that is a mixture of SQL 6.5 to 2005 versions, I have no problem in having 3 of them and using at same time.

Could you please explain what you mean by 'if you try to attach....'.

|||

Hi Staya,

I meant, when you do this action: "New Sql Server Registration" from your client.

Yeah, I know, I can open three clients in my own workstation without problems.

I'm talking about that you can't attach from your Management Studio (2005) 7.0 or 6.5 clients.

Beyond of this, from Enterprise Manager (2000) you can attach any Sql Server 2005 because of sql2k uses DMO library instead of SMO (which is native for 2005).

Ok, you could do it from Query Analyzer.

|||I can connect to any of SQL 2k instances at my end using SSMS and check what is the service pack of those SQL instances that are in version 2000. Even the query analyzer in SQL 2005 should be able to connect to SQL 2000 instnaces.|||

Yeah, don't worry.

Idea was that we might have available an unique tool for see all of them.

My idea is maybe something crooked.

|||I believe SSMS is good one to go, but due to the limitations on backward compatibility you might not get what you want. So in this case you need to have 2 or 3 versions of SQL tools to continue the work. I'm sure there is a opportunity for a third party company to develop such tool.

How to make this DATE format

im having some trouble getting this specifik date format:

dd-MM-yyyy hh:mm

where the time is in 24 hour format...

Date and time formatting seems a lot harder in sql server 2005 than in access

This:

SELECT convert( varchar(16), getdate(), 120 )

Returns:

-

2007-05-07 13:04

Refer to Books Online, Topic: 'Cast and Convert' for the 'style' (120, above) values.

How to make SQL Server fully consume CPUs on a dedicated box

SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu no
matter what I do -- indexing & heavy queries at the same time. Is there
something I need to set to get/allow SQL Server to be greedier with the
CPUs?
Thx,
TomYou probably have I/O contention... Run performance monitor and watch your
disc drives.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu no
> matter what I do -- indexing & heavy queries at the same time. Is there
> something I need to set to get/allow SQL Server to be greedier with the
> CPUs?
> Thx,
> Tom
>|||Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
used). I'm not sure why you want SQL to use all the CPU. Do you have
performance complaints? In every system there is always a'bottleneck' in
yours it's not CPU appearently.
--
regards,
Mario
http://www.sqlinternals.com
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> You probably have I/O contention... Run performance monitor and watch your
> disc drives.
>
> "Tom Ryan" <tryan@.comscore.com> wrote in message
> news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu
no
> > matter what I do -- indexing & heavy queries at the same time. Is there
> > something I need to set to get/allow SQL Server to be greedier with the
> > CPUs?
> >
> > Thx,
> > Tom
> >
> >
>|||Well, I have ASE running on a similar box, with a similar disk set up.
Performance is good in ASE, but you definately see the cpu's jump when
processes (especially concurrently running) are running. Performance isn't
great w/the SQL server and the database is fairly small (especially compared
to the ASE box). Since the disk arrays are comparable, my conclusion is that
SQL server isn't using the CPUs aggressively enough.
Had the problem one time in ASE. There was a config setting to tweak how
dedicated the box is for the data server. It was set too low, so ASE
frequently gave up the CPUs for general use by the OS.
Why I'm running a couple large queries and indexing at the same time, seems
like the CPU usage should be greater than 25% on a four way box.
"Mario" <nospam@.sqlinternals.com> wrote in message
news:3fab54ca$0$58706$e4fe514c@.news.xs4all.nl...
> Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
> used). I'm not sure why you want SQL to use all the CPU. Do you have
> performance complaints? In every system there is always a'bottleneck' in
> yours it's not CPU appearently.
> --
> regards,
> Mario
> http://www.sqlinternals.com
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > You probably have I/O contention... Run performance monitor and watch
your
> > disc drives.
> >
> >
> > "Tom Ryan" <tryan@.comscore.com> wrote in message
> > news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu
> no
> > > matter what I do -- indexing & heavy queries at the same time. Is
there
> > > something I need to set to get/allow SQL Server to be greedier with
the
> > > CPUs?
> > >
> > > Thx,
> > > Tom
> > >
> > >
> >
> >
>|||It's nice that you're drawing conclusions, but the only way to find out
what's REALLY happening is to run perfmon and find out.
I can tell you that the SQL Servers I administrate certainly have no problem
consuming CPU cycles.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.|||By the way, indexing, especially building clustered indexes, is very IO
intensive. It will pin most disc drives long before the CPU.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.|||Ah I see. You mean 'control panel'->system->advanced->performance options
and then click 'background services'. If I remember correctly that will
increase the time quantum threads get on the cpu. There's another one
somnewhere in network settings/file&printer sharing where you can specify
'optimize for network apps' which will trim workingsets less aggresively.
These will help somehow, probably/ But still if there are cpu available and
SQLserver wants them, it'll get them.
Even if there was such a thing as 'general use by the OS', and there are
cycles left: there are still cycles to grab for sqlserver, so I don't think
it makes sense to look at cpu.
If sqlserver is not using cpu 100% it's waiting on something(or simply
waiting for work to come): check the wait stuff in sysprocesses, and you'll
find out what it's waiting on/for. Also check dbcc sqlperf(Waitstats): if
you see high pageio times (compared to elapsed and cpu used times) it's
probably I/O bound. If you see high networkio times (again compared etc)
there is a lot of roundtrips (or slow depending on the count) between
clients and server.
Or you can also rely on perfmon as Adam suggest, which is probably the
'easy' and most used way to go.
Apart from that I fully agree with Adam: don't guess: measure!
good luck!
--
regards,
Mario
http://www.sqlinternals.com
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.
> "Mario" <nospam@.sqlinternals.com> wrote in message
> news:3fab54ca$0$58706$e4fe514c@.news.xs4all.nl...
> > Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
> > used). I'm not sure why you want SQL to use all the CPU. Do you have
> > performance complaints? In every system there is always a'bottleneck' in
> > yours it's not CPU appearently.
> >
> > --
> > regards,
> > Mario
> >
> > http://www.sqlinternals.com
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > > You probably have I/O contention... Run performance monitor and watch
> your
> > > disc drives.
> > >
> > >
> > > "Tom Ryan" <tryan@.comscore.com> wrote in message
> > > news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > SQL Server 2000 on windows 2000 never seems to consume 100% of the
cpu
> > no
> > > > matter what I do -- indexing & heavy queries at the same time. Is
> there
> > > > something I need to set to get/allow SQL Server to be greedier with
> the
> > > > CPUs?
> > > >
> > > > Thx,
> > > > Tom
> > > >
> > > >
> > >
> > >
> >
> >
>

how to make report parameter value change dynamic when run time?

Dear all,

i have 2 parameter [startDate] [endDate].
startDate will basic on the day run report to get the start date from system(follow the store procedure) as below.

CREATE PROCEDURE [dbo].[CFSRep_spGetLastWeekMonDate]
AS
BEGIN
DECLARE @.dtStart DATETIME
-- SET @.dtStart = GetDate() --Set Date to Todays Date
--Set Start Date to 6 days before. If its a Sunday it will get this weeks Monday Date, else any day before will get the past weeks Monday Date
SELECT @.dtStart = dateadd(dd, -6, Getdate())
WHILE (datepart(dw, @.dtStart) != 2) --While its not Day 2 (Monday)
BEGIN
SELECT @.dtStart = dateadd(dd, -1, @.dtStart) --Set the Start Date to a day before
END

SET @.dtStart = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.dtStart))) --Set the time to 00:00:00 AM

SELECT @.dtStart as LastWeekFirstDay
--

END
GO


endDate will get the date after the startDate is process. let say the answer as below.
SELECT DATEADD(dd, 4, @.startDate) as endDate

when i get the startDate = 30th Jan 2006, then the endDate = 3rd Feb 2006

then when i preview report, and i change the parameter of the date on start date to 23rd Jan 2006 but the endDate still the same 3rd Feb 2006.

anyway for me to refesh the endDate to 27th Jan 2006?

Hi Terence,

first of all you can use visual basic functions like Today etc. to get the startdate. In case of that you must not go to the database.

then you can try the following steps:

1. define 2 parameters (startdate + enddate)

2. in the enddate-dataset define a parameter @.startDate and set it to =Parameters!StartDate.Value.

3. make your 'SELECT DATEADD(dd, 4, @.startDate) as endDate' statement.

Now the reportservice should set in the endate-parameter the startdate-param +4 days.

hope this will work for you

sql

How to make aspnet DB use local time zone?

I created aspnet DB on SQL Server 2K using aspnet_regsql utility. Everything works fine except the DateTime fields in all tables are using a wrong time zone. How do I set it to use my local time zone?

Any help will be greatly appreciated.

All stored procedures in aspnetdb use UTC-based datetime fields. That's pretty normal practice for any database that might need to be accessed from multiple time zones.

how to lock the store procedure and allow one process to acces it at a time

Hello:

I run one process that calls the following the store procedure and
works fine.

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

END
GO

If I run two processes that call the above store procedure, I might
occasionally get the dirty data of int_parameter_dbt. I guess that is
caused by two processes accessing to the same resource simultaneously.
Is there any way to lock the store procedure call from MSSQL Server
and allow only one process to access it at a time?

Thanks for help.

Best Jin"Jin" <texlqj@.hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@.posting.google.c om...
> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

Here is one possible approach, using an UPDATE syntax specific to MSSQL:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

declare @.val int

UPDATE system_parameter_dbt
SET @.val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

select @.val

END
GO

Alternatively, you can use a locking hint:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

begin tran

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

commit

END
GO

Simon|||Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

How to lock a set of tables??

Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnxWhy not begin a transaction before changing the first table and then commit or rollback after changing the last table?|||You can do it by 2 ways

1) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2)By Giving Lock hints on target tables (HOLDLOCK,TABLOCKX) --which is equivalent to SERIALIZABLE

more information on BOL for "lock hints"

vish

Originally posted by armen_gg
Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnx

How to load database name at runtime?

I m creating a report using data view.
its working fine. BUt i want to load db name at run time, I tried a sample and which does it very fine.

But when i use the same code in my project, its not loading the database name at runtime, old name is being used.

What i m doing is like this in visual basic.net

rptCustomersOrders.Load("..\CustomerOrders.rpt")

' Set the connection information for all the tables used in the report
' Leave UserID and Password blank for trusted connection
For Each tbCurrent In rptCustomersOrders.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

ReportViewer.ReportSource = rptCustomersOrders

Plz. tell me ; what property i have to set, to load the database name at runtime.

Thanks in adavancehttp://www.dev-archive.com/forum/archive/index.php/t-293276.html|||try adding

.location = .name

after

.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"

so that it will erase previous location details ....

if u have any other methords ... plzz let me know too dude .........|||Here's my code which you can use to change Database Name, User Name, Password and SQL Server Name at run time. This code is written in VB6 and works with Crystal Reports 10.

Copy this code in a Module in VB and I used frm Report where I have placed crystal report viewer control.

Public Sub DisplayReport(ReportFileName As String)
Dim app2 As CRAXDRT.Application
Dim rap As CRAXDRT.Report

Set app2 = New CRAXDRT.Application
Set rap = New CRAXDRT.Report
Set rap = app2.OpenReport(ReportFileName)

rap.EnableParameterPrompting = False

For Each CRXDatabaseTable In rap.Database.Tables
STORED_DATABASE_NAME = CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") ''JUST TO READ DATABASE NAME STORED IN CRYSTAL REPORT FILE
Exit For
Next

For Each CRXDatabaseTable In rap.Database.Tables
CRXDatabaseTable.ConnectionProperties("Data Source") = SQLServerName
CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") = DatabaseName
CRXDatabaseTable.ConnectionProperties("USER ID") = UserName
CRXDatabaseTable.ConnectionProperties("PASSWORD") = LoginPassword
If Not CRXDatabaseTable.TestConnectivity Then
MsgBox "Error connecting to database table." & vbCrLf & "Please contact Adminstrator to validate Report Database"
Exit Sub
End If
Next

rap.EnableParameterPrompting = True
On Error Resume Next
Ret = rap.SQLQueryString 'THIS WILL CALL USER TO INPUT PARAMETERS FOR REPORT (IF ANY)
On Error GoTo 0

If Err.Number = -2147206395 Then 'CHECK IF USER PRESSED CANCEL
Err.Clear
Exit Sub
ElseIf Err.Number <> 0 Then 'CHECK IF ANY OTHER ERROR OCCURED
MsgBox Err.Number & " : " & Err.Description
Err.Clear
Exit Sub
End If

If UCase(STORED_DATABASE_NAME) <> UCase(DatabaseName) Then
''IF DATABASE WHILE CREATING REPORT WAS DIFFERENT THEN THE CURRENT DATABASE.
''UPDATE DATABASE NAME IN CONNECTION STRING, ELSE CONNECTION STRING READS DATA FROM DATABASE USED WHILE CREATING REPORT
DoEvents
rap.EnableParameterPrompting = False
Ret = rap.SQLQueryString
Ret = Replace(Ret, STORED_DATABASE_NAME, DatabaseName)
rap.SQLQueryString = Ret
rap.SQLQueryString = Ret ''IT DOESNOT WORK IF I SET THIS ONCE (PARAMETERS ARE NOT SET). :-( CRYSTAL BUG
DoEvents
End If

frmReport.Show
frmReport.CrystalActiveXReportViewer1.ReportSource = rap
frmReport.CrystalActiveXReportViewer1.ViewReport
frmReport.CrystalActiveXReportViewer1.Refresh
End Sub|||Sorry forget to add this declaration in my above function:

Dim CRXDatabaseTable As CRAXDRT.DatabaseTable

How to List information by date

Hi,

I want to track the lifecycle of a request(requestno), the time it was submitted to accepted/denied.

The information is captured as :

Audit

auditid
adminid
auditdate
requestno
taskid

auditid adminid auditdate requestno taskid
1 1 12/06/2007 1 1
2 3 13/06/2007 2 1
3 12 12/06/2007 3 1
4 2 13/06/2007 1 2
5 2 16/06/2007 1 3
6 4 14/06/2007 2 2
7 4 7/06/2007 2 4

Task
taskid
taskname

taskid taskname
1 Submitted
2 InProgress
3 Accepted
4 Denied
5 Transferred

I want a query that will list down the following. There should be only one row for each request :

RequestNo DtSubmit DtProgress DtAccepted DtDenied Admin
1 12/06/2007 13/06/2007 16/06/2007 null 2
2 13/06/2007 14/06/2007 null 17/06/2007 4
3 12/06/2007 null null null 12

Thanks,

Vidya.

here it is,

Code Snippet

Create Table #audit (

[auditid] int ,

[adminid] int ,

[auditdate] datetime ,

[requestno] int ,

[taskid] int

);

SET DATEFORMAT DMY

Insert Into #audit Values('1','1','12/06/2007','1','1');

Insert Into #audit Values('2','3','13/06/2007','2','1');

Insert Into #audit Values('3','12','12/06/2007','3','1');

Insert Into #audit Values('4','2','13/06/2007','1','2');

Insert Into #audit Values('5','2','16/06/2007','1','3');

Insert Into #audit Values('6','4','14/06/2007','2','2');

Insert Into #audit Values('7','4','7/06/2007','2','4');

Create Table #task (

[taskid] int ,

[taskname] Varchar(100)

);

Insert Into #task Values('1','Submitted');

Insert Into #task Values('2','InProgress');

Insert Into #task Values('3','Accepted');

Insert Into #task Values('4','Denied');

Insert Into #task Values('5','Transferred');

Select

RequestNo

,Max(Case When T.[taskid]=1 Then [auditdate] End) DtSubmit

,Max(Case When T.[taskid]=2 Then [auditdate] End) DtInProgress

,Max(Case When T.[taskid]=3 Then [auditdate] End) DtAccepted

,Max(Case When T.[taskid]=4 Then [auditdate] End) DtDenied

,Max(Case When T.[taskid]=5 Then [auditdate] End) DtTransferred

,Max(Adminid)

From

#audit A

Join #task T On A.[taskid] = T.[taskid]

Group By

RequestNo

|||

Bingo!!

Thanks.

How to link new primary key column instead of old primary key column on existing

The earlier developer, developed reports very complex formatting reports, may be he was spend lot of time on formatting. Each report is fetching data from 7 to 8 tables. All are working fine. He did very good job. He didnt used Stored Procedures in it. For Complex reports SP are giving better performance as of my knowledge. Recently I joined in the organization they assigned me reports part.

Now the problem is

Recently the database was changed accordingly their convenience. He was developed reports using old database. In the old database one table they added one new column and assigned primary key, old primary key column as set to non-key column. Now I need to link the new primary column instead of old primary key column. I dont know how to change this one, I tried verify database command. But no luck. I check report export. I am confusing on that. Can you please help me to solve this one?



Thanks in advance

NadaI believe you have been answered on another forum.

How to limit the time Dimension to 24 months

MSAS Time Dimension Question

Well here it goes...I am trying to create a date dimension that will only pull in 24 months worth of date information.

Example: I want the end date to be the current date and the start date to be 24 months prior...as the current date changes (End date) the Start date will also change to reflect the date changes. I'm thinking something similar to a rolling 12 or 24 month time dimension.

NOTE: My time Dimension needs to be similar to that of a Bank account (NOT Accumulative)

Ex:

May 1 =$ 1.50

May 2 =$ 1.42

ect..

May 31 =$0.65

1-1 relationship on Date to Amount

NO rollup within the date dimension. I would love to have folder structures built into the Date Dimension Year/Quarter/Month but not rollup...more of an external rollup.

Just a side note...the date field within my SQL DB has Ten years worth of date fields but I want to limit it to only 2 years.

Anyone have any good ideas?

lochew

In this case, you should create your dimenison manually using the dimension wizard (Server Time dimenison). You can specify exactly the starting and end date of your dimension.

If you want to avoid the rollup you can siply associate the NONE additive aggregation funciton to the measure.

|||

Thanks tdhers,

Is this option available within MSAS/SQL 2000? I haven't seen the (Server Time Dimension) but I will take another look. I'll also look into the NONE additive aggregation funciton.

Thanks again

|||No it is AS2005 only

How to limit the time Dimension to 24 months

MSAS Time Dimension Question

Well here it goes...I am trying to create a date dimension that will only pull in 24 months worth of date information.

Example: I want the end date to be the current date and the start date to be 24 months prior...as the current date changes (End date) the Start date will also change to reflect the date changes. I'm thinking something similar to a rolling 12 or 24 month time dimension.

NOTE: My time Dimension needs to be similar to that of a Bank account (NOT Accumulative)

Ex:

May 1 =$ 1.50

May 2 =$ 1.42

ect..

May 31 =$0.65

1-1 relationship on Date to Amount

NO rollup within the date dimension. I would love to have folder structures built into the Date Dimension Year/Quarter/Month but not rollup...more of an external rollup.

Just a side note...the date field within my SQL DB has Ten years worth of date fields but I want to limit it to only 2 years.

Anyone have any good ideas?

lochew

In this case, you should create your dimenison manually using the dimension wizard (Server Time dimenison). You can specify exactly the starting and end date of your dimension.

If you want to avoid the rollup you can siply associate the NONE additive aggregation funciton to the measure.

|||

Thanks tdhers,

Is this option available within MSAS/SQL 2000? I haven't seen the (Server Time Dimension) but I will take another look. I'll also look into the NONE additive aggregation funciton.

Thanks again

|||No it is AS2005 only

How to know when it's time to goto sleep.

07:33 in the morning on a Sunday and I'm reading AND replying to this stuff,
and arguing with my BIND server.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiI don't know if this is the right time to inform you.. but www.vbSensei.Com
is down.
"Mike Labosh" <mlabosh_at_vbsensei_dot_com> wrote in message
news:OVCttEFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 07:33 in the morning on a Sunday and I'm reading AND replying to this
> stuff,
> and arguing with my BIND server.
> --
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||RE:
<< How to know when... >>
Now Mike; now would be a good time.
-HTH
"Mike Labosh" <mlabosh_at_vbsensei_dot_com> wrote in message
news:OVCttEFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 07:33 in the morning on a Sunday and I'm reading AND replying to this
> stuff,
> and arguing with my BIND server.
> --
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||:) For me its 3:10 AM IST...
So ur 7:33 AM is far better :))...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Mike Labosh" wrote:

> 07:33 in the morning on a Sunday and I'm reading AND replying to this stuf
f,
> and arguing with my BIND server.
> --
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
>

how to know time of insertion of a row

Hi
Is there a way to know time of insertion of a row without using,
timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives me
the date of insertion of a row.
--
Thanks in Advance
Regards
SuparichituduNo, there isn't
However , you are be able to create a column with GEDATE() as a DEFAULT
CONSTRAINT.
CREATE TABLE Test (col1 INT,col2 DATETIME DEFAULT GETDATE())
GO
INSERT INTO Test (col1) VALUES (100)
GO
SELECT * FROM Test
"Suparichithudu" <Suparichithudu@.discussions.microsoft.com> wrote in message
news:42420237-454F-49EE-B756-21326EAADE0C@.microsoft.com...
> Hi
> Is there a way to know time of insertion of a row without using,
> timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives
> me
> the date of insertion of a row.
> --
> Thanks in Advance
> Regards
> Suparichitudu
>|||if you're in the unenviable situation where you need to retrospectively
find this out, one thing that might be able to help you is if you can
get hold of the transaction logs over the period that you know it was
inserted. However that would require you to not be using a simple
recovery model.
There are some tools you can get free trials of which will query a
logfile, but I'm not sure of their reliability.

How to know that which table i have updated?

Hi,
I have one sql server and many users access the sql
server in the same time. How i can know which table has
been recently updated? How i can know someone has update
the some data into which table?
Can i keep track what tables or data have been
updated to sql server today? The data inside which table
has been modified?
Thank you very much.
regards,
florenceEasiest to implement would be setup a SQL Profiler Trace.
Alternitavly you could create UPDATE triggers on the tables in question
--
HTH
Ryan Waight, MCDBA, MCSE
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:0f5101c3a863$91345320$a501280a@.phx.gbl...
> Hi,
> I have one sql server and many users access the sql
> server in the same time. How i can know which table has
> been recently updated? How i can know someone has update
> the some data into which table?
> Can i keep track what tables or data have been
> updated to sql server today? The data inside which table
> has been modified?
> Thank you very much.
> regards,
> florence
>