I wonder if I could write a dynamic where statement in a store procedure.
I have a web page that will pass following information to the store procedur
e.
Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1 or
2 for either. My store procedure looks like this
create select_apartment_details
(
@.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
)
AS
SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
GO
By the way typeid field in the tbl_apartments is integer.
However, this doesnt work. Can anyone help me pls?If the values in the TypeID Column in the tbl_apartments table are indeed
limited to
1 or 2, then I suggest the following
Pass in @.TypeID = 1 When you want only the 1s,
@.TypeID = 2 When you want only the 2s,
and @.TypeID = 3, when you want 1s and 2s
Then in your where clause, you can do this
Where TypeID ^ @.Type <> 0
This will do a bitwise And comparison such that
Database TypeID
@.TypeID | 1 2
-- | -- --
1 | 1 0
2 | 0 2
3 | 1 2
"regaliel" wrote:
> I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store proced
ure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>|||Sorry Memory tricked me... The bitwise operator yu want is bitwise AND, and
the operator symbol is an Ampersand as in
Where TypeID & @.TypeID <> 0
"regaliel" wrote:
> I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store proced
ure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>|||Try this:
SELECT * FROM tbl_apartments WHERE CHARINDEX(CAST(typeid AS CHAR(1)), @.test)
> 0
"regaliel" <regaliel@.discussions.microsoft.com> wrote in message
news:9B49F533-89B9-492D-B556-96B2199585C9@.microsoft.com...
>I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store
> procedure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
> or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Formatting Issues
Hi all,
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>
Monday, March 19, 2012
Formatting Database Server Need Procedure for Restoring Database
Hello,
I have a database server running windows 2000 server sp4, sql server 2000
sp3. I want to format this server and start fresh with an install of windows
2003 server standard edition and reload sql server 2000. My sql server
knowledge is very basic and I need to have a procedure for bringing the
database back to its current state after the format.
I have set up a test server. I loaded Win 2003 server, sql server 2000, and
sp3 for sql. I don't know if I have to restore my master,model, ect. backups
or if I can just restore my production database?
I copied a .bak file of my database to the test server and tried restoring
it using enterprise manager. Of course the data and log file locations are
going to be on one disk instead of two like the production server but I made
the neccearry change under options. When I click ok on the restore it comes
up with the restore progress windows but its already been and hour and no
progress. I do notice a created .mdf file in the restore path but no
indication of it doing anything else.
Your help is greatly apprecaited. Please contact me at
chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
Thanks,
ChrisHi Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of windows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, and
> sp3 for sql. I don't know if I have to restore my master,model, ect. backups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations are
> going to be on one disk instead of two like the production server but I made
> the neccearry change under options. When I click ok on the restore it comes
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> Chris
I have a database server running windows 2000 server sp4, sql server 2000
sp3. I want to format this server and start fresh with an install of windows
2003 server standard edition and reload sql server 2000. My sql server
knowledge is very basic and I need to have a procedure for bringing the
database back to its current state after the format.
I have set up a test server. I loaded Win 2003 server, sql server 2000, and
sp3 for sql. I don't know if I have to restore my master,model, ect. backups
or if I can just restore my production database?
I copied a .bak file of my database to the test server and tried restoring
it using enterprise manager. Of course the data and log file locations are
going to be on one disk instead of two like the production server but I made
the neccearry change under options. When I click ok on the restore it comes
up with the restore progress windows but its already been and hour and no
progress. I do notice a created .mdf file in the restore path but no
indication of it doing anything else.
Your help is greatly apprecaited. Please contact me at
chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
Thanks,
ChrisHi Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of windows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, and
> sp3 for sql. I don't know if I have to restore my master,model, ect. backups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations are
> going to be on one disk instead of two like the production server but I made
> the neccearry change under options. When I click ok on the restore it comes
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> Chris
Formatting Database Server Need Procedure for Restoring Database
Hi Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running Windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of wind
ows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, a
nd
> sp3 for sql. I don't know if I have to restore my master,model, ect. back
ups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations ar
e
> going to be on one disk instead of two like the production server but I ma
de
> the neccearry change under options. When I click ok on the restore it com
es
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> ChrisHello,
I have a database server running Windows 2000 server sp4, sql server 2000
sp3. I want to format this server and start fresh with an install of window
s
2003 server standard edition and reload sql server 2000. My sql server
knowledge is very basic and I need to have a procedure for bringing the
database back to its current state after the format.
I have set up a test server. I loaded Win 2003 server, sql server 2000, and
sp3 for sql. I don't know if I have to restore my master,model, ect. backup
s
or if I can just restore my production database?
I copied a .bak file of my database to the test server and tried restoring
it using enterprise manager. Of course the data and log file locations are
going to be on one disk instead of two like the production server but I made
the neccearry change under options. When I click ok on the restore it comes
up with the restore progress windows but its already been and hour and no
progress. I do notice a created .mdf file in the restore path but no
indication of it doing anything else.
Your help is greatly apprecaited. Please contact me at
chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
Thanks,
Chris|||Hi Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running Windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of wind
ows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, a
nd
> sp3 for sql. I don't know if I have to restore my master,model, ect. back
ups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations ar
e
> going to be on one disk instead of two like the production server but I ma
de
> the neccearry change under options. When I click ok on the restore it com
es
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running Windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of wind
ows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, a
nd
> sp3 for sql. I don't know if I have to restore my master,model, ect. back
ups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations ar
e
> going to be on one disk instead of two like the production server but I ma
de
> the neccearry change under options. When I click ok on the restore it com
es
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> ChrisHello,
I have a database server running Windows 2000 server sp4, sql server 2000
sp3. I want to format this server and start fresh with an install of window
s
2003 server standard edition and reload sql server 2000. My sql server
knowledge is very basic and I need to have a procedure for bringing the
database back to its current state after the format.
I have set up a test server. I loaded Win 2003 server, sql server 2000, and
sp3 for sql. I don't know if I have to restore my master,model, ect. backup
s
or if I can just restore my production database?
I copied a .bak file of my database to the test server and tried restoring
it using enterprise manager. Of course the data and log file locations are
going to be on one disk instead of two like the production server but I made
the neccearry change under options. When I click ok on the restore it comes
up with the restore progress windows but its already been and hour and no
progress. I do notice a created .mdf file in the restore path but no
indication of it doing anything else.
Your help is greatly apprecaited. Please contact me at
chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
Thanks,
Chris|||Hi Chris
When restoring a new database there is usually a period at the beginning
when no bars appear. The larger the database is the longer it takes.
Did you check that the disc was not fragmented before you restored the
database, also did you check there was ample space for the database?
The following should give you a good idea of what you will need to do
http://support.microsoft.com/kb/314546
You need to service pack SQL Server on the new host to the same level as the
existing machine.
John
"Chris Witthoft" wrote:
> Hello,
> I have a database server running Windows 2000 server sp4, sql server 2000
> sp3. I want to format this server and start fresh with an install of wind
ows
> 2003 server standard edition and reload sql server 2000. My sql server
> knowledge is very basic and I need to have a procedure for bringing the
> database back to its current state after the format.
> I have set up a test server. I loaded Win 2003 server, sql server 2000, a
nd
> sp3 for sql. I don't know if I have to restore my master,model, ect. back
ups
> or if I can just restore my production database?
> I copied a .bak file of my database to the test server and tried restoring
> it using enterprise manager. Of course the data and log file locations ar
e
> going to be on one disk instead of two like the production server but I ma
de
> the neccearry change under options. When I click ok on the restore it com
es
> up with the restore progress windows but its already been and hour and no
> progress. I do notice a created .mdf file in the restore path but no
> indication of it doing anything else.
> Your help is greatly apprecaited. Please contact me at
> chris.witthoft@.durexproducts.com or christopherwitthoft@.hotmail.com
> Thanks,
> Chris
Monday, March 12, 2012
Formatted Values in Stored Procedures
Hello,
I have to put some data from an SQL Server 2000 DB to a
Word document containing some numeric values (money).
I use a stored procedure like this
select field1 + ' ' + field2 + ' ' + field3 ...
from table1 where ...
So I get back one long string. How can I get all numeric
values in this string formatted? There is no format
function like in VB/A.
Second: I'd like to have a tab (Ascii 9) instead of ' ':
How can I get this?
Klaus
www.trappdata.deFormatted like what?
Take a look at this
DECLARE @.v MONEY
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,@.v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@.v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@.v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function.
The work around is to cast it to money
DECLARE @.v2 DECIMAL (36,10)
SELECT @.v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@.v2),1) --13,243,543.57 Formatted
with commas
http://sqlservercode.blogspot.com/
I have to put some data from an SQL Server 2000 DB to a
Word document containing some numeric values (money).
I use a stored procedure like this
select field1 + ' ' + field2 + ' ' + field3 ...
from table1 where ...
So I get back one long string. How can I get all numeric
values in this string formatted? There is no format
function like in VB/A.
Second: I'd like to have a tab (Ascii 9) instead of ' ':
How can I get this?
Klaus
www.trappdata.deFormatted like what?
Take a look at this
DECLARE @.v MONEY
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,@.v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@.v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@.v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function.
The work around is to cast it to money
DECLARE @.v2 DECIMAL (36,10)
SELECT @.v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@.v2),1) --13,243,543.57 Formatted
with commas
http://sqlservercode.blogspot.com/
formatted outputs
Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Regards
dba_sybaseYou can Use Enterprise manager to script them in formatted way .. unless you really not able to use Em .. or try using
exec sp_helptext 'sp_name'|||Thanks AAshu. I already knew that but it seems that the third pary application created the SP as a single line. So I couldnot find a way to formatt them other than formatting them by hand.
Thanks
Jaideep|||Try this trick for a select statement (not updates or deletes!):
Copy your line to the clipboard.
Open select a table (any table) in your database in Enterprise Manager, right click, and select show all rows (or show data, or whatever).
When the window opens up and displays the data, look for an icon near the top left that will display the SQL code applied (likely just a select from statement),
Replace the SQL Statement with what you have on the clipboard and requery by clicking the red "!" icon.
EM should reformat your code before displaying the results. If it doesn't, try adding a dummy table to the display and then removing it.
Sorry if this isn't step-by-step clear, but I'm going from memory.
Personally, I don't like the way EM formats statements, so I usually do some touch-up afterward, but its better than one long line of code.
blindman|||Thanks
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Regards
dba_sybaseYou can Use Enterprise manager to script them in formatted way .. unless you really not able to use Em .. or try using
exec sp_helptext 'sp_name'|||Thanks AAshu. I already knew that but it seems that the third pary application created the SP as a single line. So I couldnot find a way to formatt them other than formatting them by hand.
Thanks
Jaideep|||Try this trick for a select statement (not updates or deletes!):
Copy your line to the clipboard.
Open select a table (any table) in your database in Enterprise Manager, right click, and select show all rows (or show data, or whatever).
When the window opens up and displays the data, look for an icon near the top left that will display the SQL code applied (likely just a select from statement),
Replace the SQL Statement with what you have on the clipboard and requery by clicking the red "!" icon.
EM should reformat your code before displaying the results. If it doesn't, try adding a dummy table to the display and then removing it.
Sorry if this isn't step-by-step clear, but I'm going from memory.
Personally, I don't like the way EM formats statements, so I usually do some touch-up afterward, but its better than one long line of code.
blindman|||Thanks
formatted output
Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Regards
SubhasSubhas (dba_sybase2003@.yahoo.com) writes:
> Is there a way to get stored procedure texts out in a formatted way
> like defncopy in Sybase??? The way they print it out really
> sucks.........
There is no command-line utility, but there are scripting facilities
both in Query Analyzer and Enterprise Manager.
In QA click F8 to get an Object Brower. Right-click a procedure and you
find the scripting option. In EM, right-click a database and pick
All Tasks and then Generate SQL Scripts.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
formatted file using stored procedure
how do i create a formatted file using a stored procedure?
for eg. if i have the following...
date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
Place: London : should occupy 20 spaces (text).
Country: United Kingdom should occupy 20 spaces (text)
EOL Terminator: (1 character only)
:
:
:
the sample final output should be like:
050403London United Kingdom
050403Washington DC USA
050403Delhi India
any quick help is appreciated,earlier post discarded the spaces between the sample records... it should reas as
050403London^^^^^^^^^^^^^^United Kingdom^^^^^^
:
:
where ^ denotes a blank space.
regards|||In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed
C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
(char(10),crdate,103) from sysobjects where type='S'" -h-1
-2147483645 sysobjects 13/11/1998
-2147483643 sysindexes 13/11/1998
-2147483645 syscolumns 13/11/1998
-2147483645 systypes 13/11/1998
-2147483647 syscomments 13/11/1998
-2147483648 sysfiles1 13/11/1998
-2147483647 syspermissions 13/11/1998
Substitue the SELECT with your stored procedure. Or even better use BCP.
for eg. if i have the following...
date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
Place: London : should occupy 20 spaces (text).
Country: United Kingdom should occupy 20 spaces (text)
EOL Terminator: (1 character only)
:
:
:
the sample final output should be like:
050403London United Kingdom
050403Washington DC USA
050403Delhi India
any quick help is appreciated,earlier post discarded the spaces between the sample records... it should reas as
050403London^^^^^^^^^^^^^^United Kingdom^^^^^^
:
:
where ^ denotes a blank space.
regards|||In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed
C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
(char(10),crdate,103) from sysobjects where type='S'" -h-1
-2147483645 sysobjects 13/11/1998
-2147483643 sysindexes 13/11/1998
-2147483645 syscolumns 13/11/1998
-2147483645 systypes 13/11/1998
-2147483647 syscomments 13/11/1998
-2147483648 sysfiles1 13/11/1998
-2147483647 syspermissions 13/11/1998
Substitue the SELECT with your stored procedure. Or even better use BCP.
formating dates
I have a date being input from a stored procedure. It come in as 11/22/2005.
I tried to format this date using:
=Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
00/22/05. What am I doing wrong?Hi Donna,
You need to capital M's for month as follows:
Format(Fields!DATE_RECEIVED.Value,"MM/dd/yy")
"DONNA" wrote:
> I have a date being input from a stored procedure. It come in as 11/22/2005.
> I tried to format this date using:
> =Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
> 00/22/05. What am I doing wrong?|||What I did to fix this, under the properties of the text box or field
display change the format code to "d".
I tried to format this date using:
=Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
00/22/05. What am I doing wrong?Hi Donna,
You need to capital M's for month as follows:
Format(Fields!DATE_RECEIVED.Value,"MM/dd/yy")
"DONNA" wrote:
> I have a date being input from a stored procedure. It come in as 11/22/2005.
> I tried to format this date using:
> =Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
> 00/22/05. What am I doing wrong?|||What I did to fix this, under the properties of the text box or field
display change the format code to "d".
Friday, March 9, 2012
Format Telepone Number
I am using a stored procedure to query data in a SQL backend. I have the telephone number stored as 9999999999. I am wondering how I can get that number formatted and displayed in a list box (Access 2000). I want standard formatting, (999) 999-9999. How can I do this? Thanks.create function dbo.fn_FormatUSPhone (
@.PhoneString varchar(10) ) returns char(14)
as begin
declare @.RetVal char(14), @.Area char(3), @.Prefix char(3), @.Nbr char(4)
if isnumeric(@.PhoneString) = 0
set @.PhoneString = replicate('0', 10)
if datalength(@.PhoneString) < 10
set @.PhoneString = replicate('0', 10-datalength(@.PhoneString))+@.PhoneString
set @.Area = cast(@.PhoneString as char(3))
set @.Prefix = substring(@.PhoneString, 4, 3)
set @.Nbr = reverse(cast(reverse(@.PhoneString) as char(4)))
set @.RetVal = '(' + @.Area + ') ' + @.Prefix + '-' + @.Nbr
return @.RetVal
end
go
select field1, field2, dbo.fn_FormatUSPhone(phone_number_field) from your_table|||If you are sure your table inserts are 10 digits all the time
select '('+left(phone,3)+') '+substring(phone,4,3)+'-'+right(phone,4) from PhoneBook
This will return exactly the same result as the last reply|||Thank you very much. That's got it.|||Okay, I thought that had it. I would like to store my telephone numbers as datatype bigint, but now the code no longer works. Apparently you cannot use the substring function on a bigint datatype. Is it possible to use a bigint datatype and still have the format I am looking for. Please help one more time.|||Do NOT store your phone number as bigint. Are you going to add phone numbers? Are you going to divide phone numbers? Are you going to average phone numbers?
Phone numbers are character data that happen to be numerals.
blindman|||yeah, bigint is kinda odd|||I assume then that Social Security Numbers should also be stored as varchar?|||you betchya, just set up check constraints with [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] and you'll be all set.|||Actually, there are more consideration to choose the one or the other datatype.
Most importantly, your data type is part of your data model, especially your integrity rules. If in your model your telephone number are ten digits, you may use bigint, char(10) or three smallints; none of them are fitting your requirement completely, but you will have to add some check constraints like the SQL_DBA showed for char(10) or < 10000000000 and >= 1000000000 for bigint.
When you ensured that your data is correct, there is also the aspect of usage. Than numeric operators are not applicable, isn't really an argument not to choose a numeric data type. However, the possibility of proper formatting may be an reason to choose the char(10) option. Another aspect is storage, in some case it may be important that bigint needs 2 bytes less than char(10). An index will also be smaller, and maybe the engine will also faster compare two numbers than two strings.
However, I was wondering whether you model is fine. Is the part (999) your net number? In this case, you model would be correcter if you could ensure that you only have valid net numbers.|||Use bigint, and you will eventually wish you hadn't.
You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.
blindman|||Originally posted by blindman
Use bigint, and you will eventually wish you hadn't.
You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.
blindman
As I tried to explain, this is just one aspect. Optimized for presentation, you would even split the telefone number into three alphanumeric portions. Sematically, however, I would propose to model a separate net number, don't you agree?|||I can see a reason for splitting off the net number (I think you are referring to "area code" in U.S.) into a separate field in some applications. To handle all possible international phone formats, I'm not sure if it could be broken apart in a consistent and reliable manner.
blindman|||The advantage of using varchar outweighs the space savings in using bigint? That was my only question. I know all telephone numbers will be stored as 9999999999, so I thought I should use bigint and enjoy the space savings. Regardless, noone has posted indicating I can format the bigint number as (999) 999-9999, so I am stuck with varchar either way. Is that correct?|||You can format it that way after you cast your bigint value as char.
'(' + Left(cast(YourValue as varchar(10)), 3) + ') ' + Mid(cast(YourValue as varchar(10), 4, 3) + '-' + right(cast(YourValue as varchar(10), 4)
It is not a matter of can, but a matter of should. Any performance boost or space-savings you get from using bigint will be offset by added processing and administration time.
blindman|||I just saw that you are working with an Access 2000 front-end. Why don't you format your number at the front-end, using the format function:
format(<Your BigInt Number>, "(###)####-###")|||Apparently because I am using a stored procedure to call the data, and then displaying it in a list box, I have to format it at the stored procedure level. I assume this to be correct.|||No, I don't think so. Let your stored proc return the numeric value with a recordset, and make sure that you fill your listbox within a procedure, looping your recordset and formatting your number during adding to the list.|||Before I waste any more of your time, I think I will keep the phone number stored as varchar. The posts I have seen seem opposed to using bigint as the datatype. The space savings would be nice, but I've already got the code working for varchar and knowing that a lot of people are opposed to bigint makes me think I should stick with varchar. Thanks for your help and everyone else's in the group. You all have been extremely helpful.
@.PhoneString varchar(10) ) returns char(14)
as begin
declare @.RetVal char(14), @.Area char(3), @.Prefix char(3), @.Nbr char(4)
if isnumeric(@.PhoneString) = 0
set @.PhoneString = replicate('0', 10)
if datalength(@.PhoneString) < 10
set @.PhoneString = replicate('0', 10-datalength(@.PhoneString))+@.PhoneString
set @.Area = cast(@.PhoneString as char(3))
set @.Prefix = substring(@.PhoneString, 4, 3)
set @.Nbr = reverse(cast(reverse(@.PhoneString) as char(4)))
set @.RetVal = '(' + @.Area + ') ' + @.Prefix + '-' + @.Nbr
return @.RetVal
end
go
select field1, field2, dbo.fn_FormatUSPhone(phone_number_field) from your_table|||If you are sure your table inserts are 10 digits all the time
select '('+left(phone,3)+') '+substring(phone,4,3)+'-'+right(phone,4) from PhoneBook
This will return exactly the same result as the last reply|||Thank you very much. That's got it.|||Okay, I thought that had it. I would like to store my telephone numbers as datatype bigint, but now the code no longer works. Apparently you cannot use the substring function on a bigint datatype. Is it possible to use a bigint datatype and still have the format I am looking for. Please help one more time.|||Do NOT store your phone number as bigint. Are you going to add phone numbers? Are you going to divide phone numbers? Are you going to average phone numbers?
Phone numbers are character data that happen to be numerals.
blindman|||yeah, bigint is kinda odd|||I assume then that Social Security Numbers should also be stored as varchar?|||you betchya, just set up check constraints with [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] and you'll be all set.|||Actually, there are more consideration to choose the one or the other datatype.
Most importantly, your data type is part of your data model, especially your integrity rules. If in your model your telephone number are ten digits, you may use bigint, char(10) or three smallints; none of them are fitting your requirement completely, but you will have to add some check constraints like the SQL_DBA showed for char(10) or < 10000000000 and >= 1000000000 for bigint.
When you ensured that your data is correct, there is also the aspect of usage. Than numeric operators are not applicable, isn't really an argument not to choose a numeric data type. However, the possibility of proper formatting may be an reason to choose the char(10) option. Another aspect is storage, in some case it may be important that bigint needs 2 bytes less than char(10). An index will also be smaller, and maybe the engine will also faster compare two numbers than two strings.
However, I was wondering whether you model is fine. Is the part (999) your net number? In this case, you model would be correcter if you could ensure that you only have valid net numbers.|||Use bigint, and you will eventually wish you hadn't.
You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.
blindman|||Originally posted by blindman
Use bigint, and you will eventually wish you hadn't.
You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.
blindman
As I tried to explain, this is just one aspect. Optimized for presentation, you would even split the telefone number into three alphanumeric portions. Sematically, however, I would propose to model a separate net number, don't you agree?|||I can see a reason for splitting off the net number (I think you are referring to "area code" in U.S.) into a separate field in some applications. To handle all possible international phone formats, I'm not sure if it could be broken apart in a consistent and reliable manner.
blindman|||The advantage of using varchar outweighs the space savings in using bigint? That was my only question. I know all telephone numbers will be stored as 9999999999, so I thought I should use bigint and enjoy the space savings. Regardless, noone has posted indicating I can format the bigint number as (999) 999-9999, so I am stuck with varchar either way. Is that correct?|||You can format it that way after you cast your bigint value as char.
'(' + Left(cast(YourValue as varchar(10)), 3) + ') ' + Mid(cast(YourValue as varchar(10), 4, 3) + '-' + right(cast(YourValue as varchar(10), 4)
It is not a matter of can, but a matter of should. Any performance boost or space-savings you get from using bigint will be offset by added processing and administration time.
blindman|||I just saw that you are working with an Access 2000 front-end. Why don't you format your number at the front-end, using the format function:
format(<Your BigInt Number>, "(###)####-###")|||Apparently because I am using a stored procedure to call the data, and then displaying it in a list box, I have to format it at the stored procedure level. I assume this to be correct.|||No, I don't think so. Let your stored proc return the numeric value with a recordset, and make sure that you fill your listbox within a procedure, looping your recordset and formatting your number during adding to the list.|||Before I waste any more of your time, I think I will keep the phone number stored as varchar. The posts I have seen seem opposed to using bigint as the datatype. The space savings would be nice, but I've already got the code working for varchar and knowing that a lot of people are opposed to bigint makes me think I should stick with varchar. Thanks for your help and everyone else's in the group. You all have been extremely helpful.
Sunday, February 26, 2012
Format number in stored procedure
I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.
com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.
com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
Format number in stored procedure
I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>
FORMAT ISSUE... what to do?
Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon
Friday, February 24, 2012
Format Decimal number conditionally
My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
place.
(ie: 2.0 hours, 3.5 hours)
I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
the ".0".
So far I have this solution:
=IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
Abs(Fields!Hours.Value) )
Is there a better solution? thanks!I found an even simpler solution:
I changed the Custom Format of my table cell to:
=Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> place.
> (ie: 2.0 hours, 3.5 hours)
> I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> the ".0".
> So far I have this solution:
> =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> Abs(Fields!Hours.Value) )
> Is there a better solution? thanks!
>|||Apology.
Do not use the Custom Format.
Change the Text Value Property to, ie: =Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> I found an even simpler solution:
> I changed the Custom Format of my table cell to:
> =Format(Cdbl(Fields!Hours.Value))
>
> "SQT" wrote:
> >
> > My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> > place.
> > (ie: 2.0 hours, 3.5 hours)
> >
> > I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> > the ".0".
> > So far I have this solution:
> > =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> > Abs(Fields!Hours.Value) )
> >
> > Is there a better solution? thanks!
> >
place.
(ie: 2.0 hours, 3.5 hours)
I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
the ".0".
So far I have this solution:
=IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
Abs(Fields!Hours.Value) )
Is there a better solution? thanks!I found an even simpler solution:
I changed the Custom Format of my table cell to:
=Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> place.
> (ie: 2.0 hours, 3.5 hours)
> I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> the ".0".
> So far I have this solution:
> =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> Abs(Fields!Hours.Value) )
> Is there a better solution? thanks!
>|||Apology.
Do not use the Custom Format.
Change the Text Value Property to, ie: =Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> I found an even simpler solution:
> I changed the Custom Format of my table cell to:
> =Format(Cdbl(Fields!Hours.Value))
>
> "SQT" wrote:
> >
> > My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> > place.
> > (ie: 2.0 hours, 3.5 hours)
> >
> > I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> > the ".0".
> > So far I have this solution:
> > =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> > Abs(Fields!Hours.Value) )
> >
> > Is there a better solution? thanks!
> >
Format date in a Stored Procedure
Dear friends,
I have a stored procedure that returns some fiels. One of the fields is a datetime type.
The field return in the follow format : 2006-11-13 0:00:00
How can I return only 2006-11-13? How can I use the format function?
regards!!!
declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)
result: 2006-11-13
|||Or...
Select Convert(varchar(10), GetDate(), 120)
Lookup the convert function in Books On Line for more formats.
|||Here is another method:
select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.
|||Dear friens,
First, let me thank for all your support.
And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)
Thanks
|||You should not care about formatting the date on the server this is a thing for the presentation layer.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
For example: I want to create the follow stored procedure:
CREATE PROCEDURE TEST
@.ID INT
AS
UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID
Understood?
I want to save in my database th system time...
Thanks!!
|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00
Thanks!!
Format Date
I have a stored procedure that selects several fields from a table,
including a date field. I'd like to format the date field in the stored
proc so that the result reads mmddyy or mmddyyyy - without any slashes
or dashes, and without the time (06/26/2006 06:53:06.373 would read
06262006). Any ideas?4 digit year
Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
2 digit year
Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
Tom
<birdbyte@.gmail.com> wrote in message
news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...
>I have a stored procedure that selects several fields from a table,
> including a date field. I'd like to format the date field in the stored
> proc so that the result reads mmddyy or mmddyyyy - without any slashes
> or dashes, and without the time (06/26/2006 06:53:06.373 would read
> 06262006). Any ideas?
>|||How handy! Worked like a charm! Thanks!
Tom Cooper wrote:
> 4 digit year
> Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
> 2 digit year
> Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
> Tom
> <birdbyte@.gmail.com> wrote in message
> news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...
including a date field. I'd like to format the date field in the stored
proc so that the result reads mmddyy or mmddyyyy - without any slashes
or dashes, and without the time (06/26/2006 06:53:06.373 would read
06262006). Any ideas?4 digit year
Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
2 digit year
Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
Tom
<birdbyte@.gmail.com> wrote in message
news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...
>I have a stored procedure that selects several fields from a table,
> including a date field. I'd like to format the date field in the stored
> proc so that the result reads mmddyy or mmddyyyy - without any slashes
> or dashes, and without the time (06/26/2006 06:53:06.373 would read
> 06262006). Any ideas?
>|||How handy! Worked like a charm! Thanks!
Tom Cooper wrote:
> 4 digit year
> Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
> 2 digit year
> Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
> Tom
> <birdbyte@.gmail.com> wrote in message
> news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...
Subscribe to:
Posts (Atom)