Hello!
Please, help with the subject. SMO generates only this:
USE [DB1]
GO
/****** Object: DatabaseRole [VIP_RDR] Script Date: 05/28/2007 03:48:05 ******/
CREATE ROLE [VIP_RDR] AUTHORIZATION [dbo]
Securables are not included for some reason.
Thank you.
Well, SMO does not do that.
So - "manually", using GRANT (together with select from sys.objects if you can distinct database objects using select clause)
Good luck!
sqlHello!
Please, help with the subject. SMO generates only this:
USE [DB1]
GO
/****** Object: DatabaseRole [VIP_RDR] Script Date: 05/28/2007 03:48:05 ******/
CREATEROLE [VIP_RDR] AUTHORIZATION [dbo]
Securables are not included for some reason.
Thank you.
Well, SMO does not do that.
So - "manually", using GRANT (together with select from sys.objects if you can distinct database objects using select clause)
Good luck!
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.
if exists
(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure[HSP_GetDateFormat]
create procedure
HSP_GetDateFormat(
@.strDateFormat nvarchar(64) out,
@.iErr
intout)
as
begin
set nocount on
set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))
set@.iErr = @.@.Error
set nocount off
end
Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.
Hi,
From your description, it seems that you want to get the current date time in stored procedure, right?
TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))
Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.
Thanks.
.ToString.ToString.ToStringI'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date
I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.
I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of
select * from table where (MONTH([AD ENDS]) = @.month)
MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?
Diane
HI Diane,
It think there are two options that might be better than having two objectdatasources.
1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}
2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)
Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0
But I would try to use Select * from table where last=ISNULL(@.last,last).
For the month query,please try something like this:
WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))
|||Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?
select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?
or
select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?
Diane
|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||Thank you Dinakar! That worked!
Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.
Diane
Quote:
Originally Posted by pritisarode
Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.
Code Snippet
hr=theApp.m_cs.Open(theApp.m_ds);
if(SUCCEEDED(hr)) {
theApp.m_cs.StartTransaction();
theApp.m_cs.Commit();
CCommand< CDynamicAccessor > cmd;
CComBSTR query(_T("SELECT NumContrato, NumClie, FechaC, FechaCob, Inversion, NoCobrador, NoVendedor, Total, Plazo, Pagos FROM Contrato"));
CString string(query.m_str);
cmd.Open(theApp.m_cs,string);
hr = cmd.MoveFirst();
query=static_cast< BSTR >(cmd.GetValue(1));
CString csres(query.m_str);
this->m_numc=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(2));
m_numcte=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(3));
//m_fecc=(int)*(query.m_str);
MessageBox(csres);
theApp.m_cs.Close();
}
--
Me!
I'm not experienced in templates, but it looks strange for me and you should check the type of the returned value.
If your field is of the type Date/Time then I'm not sure that simple casting is correct, since I would expect GetValue to return the pointer into the buffer with the actual data and I presume that the datatype there should be DBTYPE_DATE. Perhaps you need to create a specific accessor and explicitly request conversion to a string type.
|||Thanks A lot this is what i've done:
Code Snippet
DATE *d=(DATE*)(cmd.GetValue(3));
COleDateTime D(*d);
m_fec=D;
Sorry for the such a noob question you've been helpful! :)!
well i'm using java to connect to SQL 2005 server. i need to retrieve it's current time and date on the sql 2005 server and use it on my remote desktop
thanks.
Hi,
You can use the following query :
SELECT getdate() as myDate
wich will give you somthing like "2006-10-27 11:29:15.373"
Regards