changing the time format

Hi, We are using an application .It 's database is on SQL server 2000. One of its module is getting the information from pbx and writing the cost to customer's folios.But sometimes it cannot calculate the right cost because of signalization.Database has a table with column writing the time in mm:ss format.I want to write a trigger that will calculate and insert the right cost if it is wrong.But i have to change the time format from mm:ss to seconds only,for calculating .How can i do it.(It must convert to seconds only while calculating but it musn't change the format on the table else program won't run properly)Thanxis the column a datetime datatype or is the data stored as mm:ss in, I guess a varchar datatype?

if you have a datetime datatype then use DATEPART to extract the minutes and seconds and do the math.

select (DATEPART(mi,<column>) * 60) + DATEPART(ss,<column>)

if you have a varchar datatype then you follow the same idea but convert the strings into ints.

select cast(left(<column>,2) as int) * 60) + cast(right(<column>,2) as int)

changing the RDL at run time

Does the RS need RDL once the report is deployed. I need to change the RDL at
run time.Although there is no rdl file on the webserver, when you deploy it the rdl
gets stored in the database. The database is where RS retrieves the report.
If you change the rdl at runtime you have to deploy the report before it can
be viewed. This is possible but keep in mind, you would need to uniquely
name the report for the current user you are modifying the rdl for otherwise
if two people try to view the same report they will see whichever was the
latest you modified and deployed. Most people that do this are using web
services to accomplish this. Although doable it is non-trivial. I would make
sure that there is not some other way around your problem before going this
route.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vcs" <vcs@.discussions.microsoft.com> wrote in message
news:00D56442-ABC3-48E9-9BE7-285198BA14C2@.microsoft.com...
> Does the RS need RDL once the report is deployed. I need to change the RDL
at
> run time.

Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
Steve
If you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>
|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
SteveIf you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.
Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx
.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing system time and Next run time for Jobs

IF you change the System time to say 1 year or even a
couple days ahead, the SQL Server Job's Next Run Time
doesn't get updated. So the jobs never run again as an
affect. The only way to fix this is to stop and restart
SQL Agent or reboot the machine.
Does anyone know what to do with this?That is expected behavior. Changing the system time has no effect on the
entries stored in tables within SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Changing source filename in DTS

Hi!
Is there a way I can make the source file in my DTS dynamic so that every time I run it using ASP I can tell it which file to use?Hi!

Is there a way I can make the source file in my DTS dynamic so that every time I run it using ASP I can tell it which file to use?

Check out the Dynamic Properties Task.

Regards,

hmscott

changing sa password

hi everyone
i am planning to change sa password in my Development server as too many
people know the password. I am trying this first time.
Can someone please let me know any impacts of changing sa password?
or I can change the password as normal way to changing password.
Thank you
syed
*** Sent via Developersdex http://www.codecomments.com ***Hi
If you have an application that connected to the SQL Server wit a Mixed Mode
, so you will have to change the password there. As well as Linked Servers
that was defined by providing username and password. And finally , if you
was connected to the EM by an old password the error will be thrown.
"Syed Hussain" <syedkhalidhussain@.gmail.com> wrote in message
news:Ofr8Ux%23mFHA.420@.TK2MSFTNGP09.phx.gbl...
> hi everyone
> i am planning to change sa password in my Development server as too many
> people know the password. I am trying this first time.
> Can someone please let me know any impacts of changing sa password?
> or I can change the password as normal way to changing password.
> Thank you
> syed
> *** Sent via Developersdex http://www.codecomments.com ***|||Hi,
To Add on to Uri;
Execute sp_who in frequent intervals and identify the users / application
who are using SA user to access sql server. Then send out an
email to all of them intimating that you are going to change the password
for SA.
Give them some time to change and after that do below:-
1. Change the Applcation connection strings to use new user
2. Change the Linked server security credentials if SA is used
3. EM registrations if you store password ( Avoid storing passwords)
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZGrB0%23mFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you have an application that connected to the SQL Server wit a Mixed
> Mode , so you will have to change the password there. As well as Linked
> Servers that was defined by providing username and password. And finally ,
> if you was connected to the EM by an old password the error will be
> thrown.
>
>
> "Syed Hussain" <syedkhalidhussain@.gmail.com> wrote in message
> news:Ofr8Ux%23mFHA.420@.TK2MSFTNGP09.phx.gbl...
>|||And once you've changed the password, stop using sa to log in to SQL Server.
You
should create logins for people who really need sysadmin privileges so that
you
don't get this problem again.
Don
On Mon, 8 Aug 2005 16:46:40 +0530, "Hari Prasad" <hari_prasad_k@.hotmail.com>
wrote:

>Hi,
>To Add on to Uri;
>Execute sp_who in frequent intervals and identify the users / application
>who are using SA user to access sql server. Then send out an
>email to all of them intimating that you are going to change the password
>for SA.
>Give them some time to change and after that do below:-
>1. Change the Applcation connection strings to use new user
>2. Change the Linked server security credentials if SA is used
>3. EM registrations if you store password ( Avoid storing passwords)
>Thanks
>Hari
>SQL Server MVP
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:eZGrB0%23mFHA.3288@.TK2MSFTNGP09.phx.gbl...
>|||If you have scripts which hardcoded sa password, then it will be a problem.
Eric King
AgileInfoSoftware
http://www.agileinfollc.com
"Syed Hussain" <syedkhalidhussain@.gmail.com> wrote in message
news:Ofr8Ux%23mFHA.420@.TK2MSFTNGP09.phx.gbl...
> hi everyone
> i am planning to change sa password in my Development server as too many
> people know the password. I am trying this first time.
> Can someone please let me know any impacts of changing sa password?
> or I can change the password as normal way to changing password.
> Thank you
> syed
> *** Sent via Developersdex http://www.codecomments.com ***

Changing order of columns at run time

Here's the scenario:
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE

Changing machine name & IP number on SQL Server 7.

Hi all!

We run a NT4 server machine running SQL 7 which contains some vital data on older databases. This machine was PDC for a long time, untill we got a new win2k server, which got the same machine name and IP number as the old NT4 machine which isn't yet connected to the AD. This new server does SQL 2000.

Since it's still important for us to have access to the SQL 7 data which cannot be used on SQL2000 server, I want to put the NT4 machine in the Win2k domain, but when I change the server's name and IP adress into something free (machine name and IP number are both taken over by new server) the SQL 7 server won't start anymore.

Is there anyone out there that knows how to solve this problem?

Thanks!!!! A LOT! :D :DI think you can run SQL 7 on the same machine as SQL 2000 and they won't bump into eachother.

blindman|||You can have SQL 2000 and one of the previous versions on the same machine. See if you want to do this. If not, see if the following article resolves your issues.

http://www.databasejournal.com/features/mssql/print.php/10894_2194711_3

All the best

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I get a new year. For instance if I run it this year I'll get 2004.txt for my file name and next year I'll get 2005.txt for my file name. Is this possible? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDate desc"' , no_outpu
t
You can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt for
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I
get a new year. For instance if I run it this year I'll get 2004.txt for my
file name and next year I'll get 2005.txt for my file name. Is this possib
le? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set noco
unt on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orde
rs O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by
OrderDate desc"' , no_outpu
tYou can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.c
md) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate fro
m Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.
CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o
'
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd)
+
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt fo
r
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

Changing date and keeping time

Hi! I'm using MS SQL server 2000. How can I change date in date field keepin
g
time the same (datetime field)? I'm using SQL Enterprise Manager for that.
Please help with syntax. More thanks, Alar. PS! Can You suggest some book or
other source I can find hints about MS SQL syntax?
> Hi! I'm using MS SQL server 2000. How can I change date in date field
> keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
That wont work, if you edit the date via EM you always will (implicit)
issue a command like:
UPDATE Sometable SET Somecolumn '01/01/2004 00:00:00' Where ...
even if you type in '01/01/2004'. The function DATEADD or if you prior cut
out the time and put it on the changed column (time 00:00:00) would work
fine.
PS! Can You suggest some book or
> other source I can find hints about MS SQL syntax?
Did you try BOL ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||> Hi! I'm using MS SQL server 2000. How can I change date in date field
keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax.
See function CONVERT in BOL.
Example:
update table1
set c1 = '2005-05-09' + right(convert(varchar(25), c1, 126), 13)
where c1 >= '20050501' and c1 < '20050508'
AMB

> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?
MS SQL Server 2000's Books Online
AMB
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||The DateAdd() function can add arbitrary number of days, ws, months, or
whatever, to a given date. If you choose any time increment greater than a
day, the time portion of the value will remain the same...
Otherwise, you need to update the column to a new date with the same time as
the the datetime that's in there...
Update TablleName Set
DTColumn = 'NewDate as CCYYMMDD ' +
convert(VarCHar(12), DTColumn, 14)
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||I don't recommend using Enterprise Manager for this, and
I suggest you run the update query in Query Analyzer
instead.
If @.newDate is the new date, T is your table, myDate is the
column you want to change, and rowKey = @.rowKey identifies
the row you want to change, this will work (not tested - watch
for typos)
update T set
myDate = dateadd(day, datediff(day, myDate, @.newDate), myDate)
where rowKey = @.rowKey
This will add a whole number of days to myDate, the number
it adds being exactly the number of whole days from myDate
to @.newDate.
Steve Kass
Drew University
Alar Pandis wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?

Changing Database Structures Through Code

I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.

Hi, I wrote a stored procedure to alter table, share with you:)

create proc sp_alterTbl @.TblName sysname,@.ColName sysname,@.ColType varchar(50)=null,
@.ColLen int=null,@.Op varchar(6)='Add'
as
begin
declare @.cmd varchar(500),@.TypeLenStr varchar(50)
if object_id(@.TblName) is null
Raiserror('Table does not exist!',16,1)
else
if not exists (select * from systypes where name= @.ColType)
Raiserror('Type does not exists!',16,1)
else
begin

select @.TypeLenStr=CASE @.ColLen WHEN null Then ' '
Else '('+convert(varchar(6),@.ColLen)+')'
END
select @.cmd= CASE @.Op when 'Add' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
+@.ColType+ @.TypeLenStr
WHEN 'Drop' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
END
EXEC(@.cmd)
END
END

Changing Database Server Locale Date time settings ?

Hi There

We currently have the following scenario:

4 app servers with regional date and time settings of locale A.

1 database server with locale settings B.

What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.

It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?

In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?

Thanx

As long as date/time values are stored in datetime datatypes, changing the locale/regional settings 'should' not have any impact on the data.

If ServerA uses the form of 'dd/mm/yyyy' and attempts to pass that string value to serverB (and ServerB uses the form of 'mm/dd/yyyy', there is confusion and often failure. Is '06/12/2007' June 12th, or Dec 6th?

However, if you were to make sure that any time values passed to SQL procedures and functions was in the form of 'yyyymmdd' or 'yyyy/mm/dd' (standard ISO format), there would not be a problem for one server to interpret the date from a different server.