HELP format Page Heading

I dont usually use page headers for this reason. I usually put my headings
within the body of the report however it doesnt seem to be working properly
with this matrix so I am going to use a true page header and I am having
trouble because I need to reference fields from the database in the header.
Here is my expression:
=eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"Title") & Parameters!MetricCode.Label & vbcrlf &
eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"ReportPeriod") & format(CDate(Parameters!StartDate.Value), "MM-dd-yyyy")
& " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
It is complaining about the MAXDATE value however I need that value and it
comes from the query result. There is probably a way around this ... I just
dont deal with headers so I have never encountered this "rule" of fields
cannot be used in headers or footers.You can't directly put values from a dataset into the page header or footer,
but you can use report paramters to do this.
Create a report parameters for each query field that you want to put in the
header. Setup the parameter to be populated by the appriopriate field.
Then use textboxes that get their value form an expression to put the data
in the header.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:E0813CA6-F8FA-4EBA-80B6-EA86C613DB98@.microsoft.com...
>I dont usually use page headers for this reason. I usually put my headings
> within the body of the report however it doesnt seem to be working
> properly
> with this matrix so I am going to use a true page header and I am having
> trouble because I need to reference fields from the database in the
> header.
> Here is my expression:
> =eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"Title") & Parameters!MetricCode.Label & vbcrlf &
> eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"ReportPeriod") & format(CDate(Parameters!StartDate.Value),
> "MM-dd-yyyy")
> & " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
>
> It is complaining about the MAXDATE value however I need that value and it
> comes from the query result. There is probably a way around this ... I
> just
> dont deal with headers so I have never encountered this "rule" of fields
> cannot be used in headers or footers.sql

Help for date in insert query

i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.

Hi,

You should elaborate more about the problem here. Post your code and let us know the way you are inserting data into the database and the fields types

Regards

|||

i want to insert in a data base some thing using this query:

insert into tble1(field1,field2) values('this some thing or data','"& syatem.now &"');

which kind of date it will store like this 24/05/2007 11:16 AM or 24/05/2007 .i want to store 24/05/2007 11:16 AM this one.i using format function of vb.net like format(system.date,"dd/MM/yyyy hh:mm tt") but it give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".in the select statement also this problem occure. i also use thhe select query as

select * from tbl1 where pdate='"& format(variabledate,"dd/MM/yyyy hh:mm tt") &"'" and it also give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".this in acess work fine but in sql server give the above error.

|||

Hi,

please try something like following

Datetime dt = newDateTime(2007,6,18,13,56,30)); //year, month, day, hour, minute, seconds

thanks,

satish.

|||

Hi there,

Use this command:

INSERT INTO TABLENAME (field1,field2) VALUES ('jan',getdate())

hope it helps you out,

gonzzas

|||

This is one of those difficult to answer questions because what you are trying to do is inheritly wrong. Datetime fields do not have a format. They represent a particular point in time. You can convert a datetime to and from a string with a specific format, but then it's no longer a datetime. You are trying to store a string with a format that sql server doesn't understand into a datetime field. SQL Server is attempting to covert your string into a real datetime so it can be stored, but the format isn't one it understands. Regardless of any of the correct formats you feed it, so long as it means the same point in time, ultimately it will get converted to the exact same number (Datetimes are internally represented by the number of days since the epoch, with the time portion being a fraction of a day).

|||

Hi again,

Here is another example of a sql command that inserts a row in a table. One of the row fields has DATETIME datatype:

Dim strAsString ="insert into users (username,password,date) values ('username1','password1',convert(datetime,'18-06-2007 23:21:00',105))"

Dim commandAsNew SqlCommand(str, conn)

Dim result = command.ExecuteScalar()

Where you see 105 it's the format chosen by me for this test. This format stores the data as dd-mm-yyyy hh:mm:ss

You can use another formats (do some light research on the subject, it should be enough) to meet your requirements: dd/mm/yyyy hh:mm:ss

gonzzas

|||

actually i solve the insert by using the query like

"insert into table tblname(f1,datef1) values('abc','"& system.datetime.now.tostring("MM/dd/yyyy hh:mm:ss") &"')"

but i still receiveing errors in select query that char conversion.

this stuff is doing in vb.net and sqlserver.

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert Lie
Best thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>
|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help - Creating reports in pdf format

Hi all,

Greetings

I m using Sql Server 2000 for creating Reports. I like to display the ad-hoc generated report in pdf format. Do i have any pdf writter to display the report or i can able to do it in programmatically. If we can then plz give me the source code to write a pdf report.

Advance Thanks & Regards

Guna.

PDF creating is an outofthebox feature of Reporting Service. It is available as a rendering format in the report manager (Web interface) of each report, unless inactivated.

HTH, Jens K. Suessmeyer.

http//www.sqlserver2005.de

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
Pete
CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteTry this:
SELECT REPLICATE('0', 6 - LEN(cast(count(*) as varchar(10)))) +
cast(count(*) as varchar(10)) AS [Varchar Column]
from A
"peter" <peter@.discussions.microsoft.com> wrote in message
news:6C9199D5-320A-4064-80E1-3D71F6615EDE@.microsoft.com...
>I am runnig query select count(*) from A
> I like to display the count in following format. For ex.
> If the count is 45 then it should display 000045
> If the count is 145 then it should display 000145
> If the count is 5 then it should display 000005
> If the count is 0 then it should display 000000
> Let know how is this possible.
> Thanks in advance,
> Pete
>|||CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteCREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteCREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/