Hi All,
My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...
regards,
Anas
Hi
I have Try to Create Stored Procedure in C# with the following structure
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)
{
}
But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature
@ImageID uniqueidentifier
@ImageFileName nvarchar(4000)
@Image varbinary(8000)
But I don€™t want that Data types .. I want it to be in the following format
@ImageID uniqueidentifier
@ImageFileName nText
@Image Image
How Can I Control the data type for each parameter ??
Or
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Or
How Can I defined the new Data type ??
Or
What's the solution to this problem ??
Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP
ALTER PROCEDURE [dbo].[sp_AddImage]
@ImageID [uniqueidentifier],
@ImageFileName nText,
@Image Image
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
GO
And thanks with my best regarding
Fraas
I have 900000 rows of data in a table and a sample of it is as shown below.Now I need to convert data into the format shown.
View 6 Replies View RelatedDear 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!!!
Hi,
I have a schedule task which call one of my stored procedure,
In this stored procedure, I need to change db owner of one of
database, but I find sp_changedbowner do not allow me to specify
db name,it only change current db,so I have to open a db before
call sp_changedbowner,but it is invalid..
CREATE PROC demo
as
begin
...
use demo_db //it is invalid
exec sp_changedbowner 'scott'
end
Can anyone give me ideas?
Thanks
Hello. I am trying to change the owner of a stored procedure from a user ID to dbo. How do i do this. I have tried sp_changeobjectowner but I get an error message. Can I do this from Enterprise Manager? btw, I am using SQL 7.0. Any help would be appreciated. Thanx
View 2 Replies View RelatedCan someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname
View 4 Replies View RelatedHow can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies View RelatedI am using a web application using asp.net 1.1 vs 2003.
i am using sql server authentication where users are using their sql server user id and sql server saved passwords.
how can i change their passwords inside the sql server?
is there a system sp that allows one to pass one's user id and then change password.
I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server.
thanks
I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mike
help
i need to create stored procedures that run once month
change value (UPDATE) once a month in one table the values from 1,2,3,4 next month 2,3,4,1 ..... 3,4,2,1 .......4,1,2,3 ..... 1,2,3,4
i have this code
Code Snippet
SELECT empID, location, ISNULL(NULLIF (( location + DATEDIFF(mm, location_date, GETDATE())) % 4, 0), 4) AS new_location
FROM dbo.empList
like this the employee go from one location once amonth to new location
table on this month (5)
empid location location_date
--------------------------------------------------------------
1111 1
222 2
333 3
444 4
5555 3
666 2
777 3
888 4
table on next month (6) (after a month)
empid location location_date
-------------------------------------------------------------------
1111 2
222 3
333 4
444 1
5555 4
666 3
777 4
888 1
TNX
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies View RelatedHi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?
View 4 Replies View Relatedone of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies View RelatedClient/Server machine: Windows Xp Pro (SP2) (latest patches)
Office Software: Access 2003 (latest patches)
Database S/W: SQL Server 2005 (latest patches)
The following error message is displayed when trying to modify a stored procedure.
This version of Microsoft Access doesn't support design changes to the
version of Microsoft SQL Server your project is connected to. See the
Microsoft Office Update Web site for the latest information and downloads
(on the Help menu, click Office on the Web). Your design changes will not be
saved.
However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.
Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?
Have I provided enough detail as this is my first post ?
Philip
Hi all,
I have to change the date format for one database in a group of databases in my sql server 2000,Can you please tell me how to change the date format.
thanks in advance
Dear friends,
In SQLServer, it seems to make data stored in Unicode, you need to go to each column type and change it from varchar to nvarchar.
Apart from this, is there a direct way of Setting Unicode for the whole database at once, which would set all coulns to the unicode equivalent.
It seems there is such an option in Oracle
Please comment on this..
Regards
Benny
I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.
At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.
Any ideas?
Thanks
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?
Something like this:
CREATE PROCEDURE TestHardDisk
AS
BEGIN
DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source
OPEN CURSOR HardDisk_Cursor
FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space
WHILE @@FETCH_STATUS = 0
BEGIN
...
END
END
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies View RelatedWhat I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
View 4 Replies View Related
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.
That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.
Thanks.
Hi, I have this query string:
SELECT distinct [KioskID], MAX([ServerDate])FROM [report].[dbo].[tbKiosksLog]Where kioskid like 'ids%' group by kioskid
order by kioskid
I'm using Ms SQL Server 2000 Enterprise Edition.
After I've process a OLAP cube, for the dimension with the data type DateTime, the date format will come out as YYYY/MM/DD hh:mm:ss.
How do I change that date format to just DD/MM/YYYY?
want to insert a format date "dd/MM/yy" into a MS SQL table, But the fields default format is American "MM/dd/yy"
How I can change this default format.
Hi,
I imported data into a fact table in sql with SSIS.
The fact table looks like this:
Question Answer Type
q1 a1 t1
q2 a2 t2
q3 a3 t3
The problem is that i have to insert the data from the fact table into another table that looks like this:
art_id template_id Body
23 1 q1
23 2 a1
23 3 t1
25 1 q2
25 2 a2
25 3 t2
How can i insert the data correctly?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
how to change datetime format.
i want to store datetime in my table as yyyy-mm-dd.
can it be done??