Time Part Being Dropped By Procedure

Oct 19, 2013

I have writen a nested stored procedure when i am passing a datetime paramter from the first procedure into the internally called procedure it is dropping the time part.

EX;
Declare @pdate datetime
Exec sp1 @pDate<this is datetime> = '2013-10-19 14:00:00'

Create proc sp1
(@pdate datetime)
as
Begin try

Exec sp2 @pdate <this is the same parameter for sp1>
End try
Begin Catch
Select 'error'
end catch

View 1 Replies


ADVERTISEMENT

Global Temp Tables Getting Dropped Form Time To Time

Apr 10, 2007

Hi all,

I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?

Thanks,
Ning

View 5 Replies View Related

Stored Procedure Getting Dropped In SQL 7.0

Apr 2, 2001

Hi
ONLY ONE of our many stored procedure is getting dropped in SQL 7.0. We were not able to find thru SQL Profiler. Let me know any alternate methods to find it or the possible reasons why/how the sp is getting dropped.


Thanks

View 1 Replies View Related

Drop Stored Procedure Causing Dropped Tables

Aug 29, 2006

Hey guys, has anyone ever seen this happen:

Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.

Three tables referenced by that stored proc have been dropped and re-created with the same table structure.

I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?

View 3 Replies View Related

Time Part Of Field

Sep 24, 2004

Hi,

I have a very beginer's question. I am trying to compare datetime field with time only. how can i just compare with time?

Select start_time,end_time from tblvouchers as sv,tblvoucherlines as svl
where sv.voucher_no = svl.voucher_no and
sv.equip_no = '01325' and svl.line_date ='03/14/2004'
and svl.start_time = '18:00' and svl.end_time = '00:00'

this query is not displaying any data.

Any help will be highly appreciated.

View 4 Replies View Related

How To Take Off The Time Part Of The Parameter

Dec 15, 2006

Hello,

How can I display data that only compare the date part (ignoring the time part) between the value of the date parameter and the database?. It displays data only if I pass the whole date (date and time)

I tried with the following query in the report services 2000, but it even didn't pass the query parse

SELECT * FROM table

WHERE (CONVERT(CHAR(8), table.PRODUCTION_DATE, 112)
= CONVERT(CHAR(8), @parameterDate, 112))



Any ideas will be appreciated.

Thanks,

Marco


View 3 Replies View Related

Remove Time Part

Jul 17, 2007

Does anybody knows how to remove the time part of the datetime format?

some tables in my database's using datetime format, and the time's not default (12:00:00 AM) but it save the exact time.
Example : 7/17/2007 11:02:06 AM

when i use datetime for parameters, there's no value returned. While it's impossible for users to entered the exact time when they want to preview reports.

So, how to remove the time part in datetime format?

Thanks!

View 13 Replies View Related

Removing Time Part Of GetDate()

Nov 7, 2000

I've been using the GetDate() function to populate a column in a table.
But it populates it with both the date and time: 2000-11-08 11:22:28

I'd like it to just put in the date: 2000-11-08

I've tried the Convert function as well as other functions, to no avail.
Some attempts have reversed month and day (I'm in Australia, so all PCs and Servers are set with a dd-mm-yy date format)

Any suggestions please?

View 1 Replies View Related

Time Part Of Smalldatetime Field

Nov 14, 2001

I'm trying to extract the time part of a smalldatetime field in a usprocedure to return just the time. I've used Datepart() to get the hours or minutes but you can't get both without getting messy. Anyone know a way to keep it neat & simple?

Thanks, archie

View 2 Replies View Related

Year Part From A Date And Time

Nov 9, 2006

Hi,
I have a table with field Msg_Time. In this field some records are there with date and time.

For eg:-
11/7/2006 2:35:00 AM,
11/1/2006 2:35:00 AM,
10/15/2006 2:35:00 AM,
11/7/2005 2:35:00 AM,
11/7/2005 2:35:00 AM,
8/26/2005 2:35:00 AM,
3/2/2005 2:35:00 AM etc

I wrote the query like this
"select datepart(year, Msg_Time) from C20_Messages"
This query result is:-
2006
2006
2006
2005
2005
2005
2005
Here I want to get only the year part without repeating.
I mean
2005
2006

How to do pls help me
with regards
shaji

View 3 Replies View Related

Inserting Just The Time Part Of The Date?

Mar 1, 2007

In the code below why does it not enter 10:00 AM but instead it enters: 1900-01-01 10:00:00.000 ?

Create Table MyDateTest
(
DateColumn datetime
)

insert into MyDateTest values ('10:00 AM')
select DateColumn from MyDateTest

--PhB

View 3 Replies View Related

Point In Time Restore Part II

Jul 20, 2005

In the hereunder written message I talk about point in time restore.It is now based upon the fact that there are no hardware problems or what soever.I just would like to roll back to a situation of some time (minutes, hoursor what ever) ago.Used to the ingres database a point in time restore can take place UP toany, any, any time since the last FULL backup. (any time up to now !!!)I can't understand why a point in time restore can only be done based upontransaction log backups. The current transaction log is also available in myopinion. (Turn off the power, turn on the power and you will notice that theautomatic recovery is based upon this transaction log file; so in that casethis file is used)That's what my question is about. Is it correct that a point in time restorein a SQL server environment can only be done up to the last transaction logbackup.ByeArno de Jong,The Netherlands.

View 1 Replies View Related

Access To SQL Server Via WCF Works Only Part Time

May 16, 2007

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.



In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.



When we access Guider we have no problems getting data.

When we access Talker we have a login failure:



Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'AcornCommunicationServices'.



The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.



Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!



Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

View 1 Replies View Related

How To Test The Time Part Of A DateTime Field

Apr 30, 2008



Dear all,

I have an application where I need to implement a Team Shift management.

The team shift table need to be defined as follow :

Shift table
=========

Id DateTime IsCurrent
1 08:00:00 True
2 16:00:00 False
3 00:00:00 False


From the table above the current running shift is Id = 1

Based on that I have following question:

1 - Is there a way to specified for a DateTime field that it will recieved only Time part ?

2 - When a shift change occurs, I need to compare the Current time with the time shift in the table. Then according to that I set the IsCurrent flag to proper new Shift ID.. how to perform this ?

Thnaks for your help
regards
serge

View 5 Replies View Related

Run Packages As Part Of Procedure

May 4, 2006



I have created SQL code that is contained in different procedures. I have a procedure that then runs all of the pieces that I wish to run. My last step in my process is to export the data to my c: drive. During this process I created a package to be rerun. How do I include the rerunning of my package automatically as part of the procedural process?

View 1 Replies View Related

Stored Procedure - Can I Use 4-part Name With A &#34;VIEW&#34;?

Aug 3, 2001

Joe, thank you for the answer to my post.

Can I also use a

SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?

Also, in my sp, I have the following:

SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO

ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?



Thanks,
John Wilson

View 1 Replies View Related

Stored Procedure Only Part Running

Dec 10, 2001

I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-

Public Function CheckDigitCalc()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command

On Error GoTo TryAgain

conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn

cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute

MsgBox "Numbers created OK.", vbOKOnly

Exit Function

TryAgain:

MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"

End Function

The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.

View 2 Replies View Related

SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?

Jun 11, 2015

Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.

View 5 Replies View Related

Stored Procedure Query With A Conditional Part?

Apr 23, 2005

Hi,

I'm converting some direct SQL queries into stored procedures. The
original queries have conditional parts appended depending on certain
parameters like this:

Sql = "First part"
If certain condition then
   Sql &= "Second part"
Endif

How could I do this in a stored procedure? So far I have made them like this:

IF @condition > 0
Fist part Second part
ELSE
First part

You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?

Thanks!

  Noc

View 3 Replies View Related

Web Part Deserialization Error When Trying To Change Report Viewer Web Part Programmatically.

Oct 29, 2007



I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"

If someone has a solution, please respond at your earlist.

Thanks

Shankar

View 1 Replies View Related

Split A Decimal Number Into The Integer Part And The Fraction Part

Dec 7, 2007

I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View 3 Replies View Related

Display Only The Date Part Of A Date And Time Field?

Mar 16, 2014

I want to display only the date part of a date field which contains both date & time information.

For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.

I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.

View 3 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?

Aug 21, 2007

View 8 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)

Jan 23, 2008

Hi Jonathan Kehayias, Thanks for your valuable response.

I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.

Here is my response to the last code/instruction you gave me:

I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.

I got the following debug error message:

1) A Box appeared and said: String or binary data would be truncated.

The statement has been terminated.

|OK|

2) After I clicked on the |OK| button, the following message appeared:

This "SqlException was unhandled

String or binary data would be truncated.

The statement has been terminated."

is pointing to the "Throw" code statement in the middle of

.......................................

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

..........

Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.



Thanks,
Scott Chang

The code of my Form1.vb is listed below:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7

command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"

command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"

command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"

command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"

command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"

command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"

command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"

Dim resulting As String = command.ExecuteNonQuery

MessageBox.Show("Row inserted: " + resulting)

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

InsertNewFriend()

End Sub

End Class

View 6 Replies View Related

Date Part Of Date Time

May 5, 2004

SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))

This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..

Thanks.

View 14 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Can SA Be Dropped

Mar 24, 2004

Is it possible at all to remove the SA login or deny "System Administrator" from it by loggin as another user who has sa previledges

thanks

View 1 Replies View Related

Dropped Table

Jan 25, 2001

I am helping another division with a problem of a dropped table. They have no backup and after dropping the table, the server has been re-booted 3 times. Is there a way to recover the table with no backup?

Help anyone,
Thank you.

View 2 Replies View Related

Dropped Connections

Apr 6, 1999

Here's a poser.
I run a VB application to import data from a text file into a SQL 6.5 database. Typically, there'll be 300,000 records, all of which have to be processed. Time taken will be in excess of 2 hours.

When the application finishes, I try to go into Enterprise Manager and run a Select Count(*) to verify that the data's been imported. Invariably, I get an error reading:
"A connection could not be established to <server> - [SQL Server] Could not allocate a new object descriptor for required system catalog in database '1'. Another database must be closed or objects in another database dropped in order to open this database."
I get the same error whether I try to view Current Activity, open the Query Tool or simply expand the server in the Server Manager area.
Usually, it means I have to re-boot the server, which is not popular with other users. Does anybody know what's causing this and what might be the solution?
Thanks.

View 1 Replies View Related

Dropped Job Failed

Apr 17, 2007

I believe I deleted a maintenance plan that was attached to a job and therefore have received the following message:



Drop failed for Job 'Shrink DSS Database.Subplan_1'. (Microsoft.SqlServer.Smo)

An execption occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlSserver.connectionInfo)

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)



Is there a T-SQL or other means I can use to remove this constraint and delete the job show it does not show up in SQL Server Management Studio any longer?

View 5 Replies View Related

Who Has Dropped SQL Table?

Jan 16, 2007

Somebody has dropped my SQL table in (multiuser environment),now I want
to know who has done that. Is there any way by which I can retrieve
that(user name or machine name)?
I am using SQL 2005.

View 3 Replies View Related

The Dropped Table Does Not Have A Primary Key And Cannot Be Used

Feb 25, 2004

I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".

I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?

Thanks.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved