date and time diffs: current

Showing posts with label current. Show all posts

Sunday, March 25, 2012

Datasets

Problem: I need to add filters on a Dataset. The current dataset is based on
a stored procedure that I would prefere NOT to touch.
Question: Is it possible to build a new dataset based on the first dataset?
(This would inable me to filter on the data output). Or are there other
suggestions for a solution to this problem.
Thanks.
Regards
JonasAs I am going on holidays can you please respond to
terry.bilsborough@.Alcan.com.
Thanks.
Regards
Jonas Larsen
Alcan Engineering
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Problem: I need to add filters on a Dataset. The current dataset is based
on
> a stored procedure that I would prefere NOT to touch.
> Question: Is it possible to build a new dataset based on the first
dataset?
> (This would inable me to filter on the data output). Or are there other
> suggestions for a solution to this problem.
> Thanks.
> Regards
> Jonas
>|||Yes it is possible to add a filter to a data set. The data set filter
functionality is located on the dataset dialog : Filter tab. Additionally
all data regions (lists, tables, matrix, and chart) support this
functionality.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23VWlAv%23YEHA.384@.TK2MSFTNGP10.phx.gbl...
> As I am going on holidays can you please respond to
> terry.bilsborough@.Alcan.com.
> Thanks.
> Regards
> Jonas Larsen
> Alcan Engineering
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Problem: I need to add filters on a Dataset. The current dataset is
based
> on
> > a stored procedure that I would prefere NOT to touch.
> >
> > Question: Is it possible to build a new dataset based on the first
> dataset?
> > (This would inable me to filter on the data output). Or are there other
> > suggestions for a solution to this problem.
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>sql

Thursday, March 8, 2012

Data-driven subscription: put parameter in the filename text

Hi all,

I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.

Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @.Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?

Thanks in advance.

Marco.

I tried the other option, making it by a query and this is what I want.

Anyway, instead of making this in my query, I couldn't have done this in the text? Like I said TestFile_ & @.Data? This would be interpreted as a string am I right?

Sunday, February 26, 2012

Databases Location

Hi all,

Need Some Advice…

Current Setup of the Databases:

All “system databases” + “reportserver & reportservertempdb”are on “ D drive” Raid 1 ( Mirrored)and their location is : D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Free Space left on "D" Drive is # 18 Gb.

“User Databases” are on - “E Drive” Raid 5 and their location is E:\Data\db\

Now , we got new storage device with much larger capacity. So, we are planning to transfer our databases on this new drive which is “RAID 5”.

My question is:

(1) Should I transfer all the databases ( (i.e.) “system databases”+ “reportserver & ReportServerTempdb” + “User databases”) on that new drive ( RAID 5).

(OR)

(2) Keep the “system databases” + “ReportServer& ReportServerTempdb” on the old

drive (i.e.) RAID 1andTransfer only the “USER Databases” on that new drive (i.e.) RAID 5.

(OR)

(3) Transter only Tempdb to the new drive (i.e.) on Raid 5, so as to avoid any I/O congestion. and keep all other system & ReportServer & ReportServerTempdb on the old "D"drive.

(OR)

(4) Is there anything I should be careful when doing this& Is there any better way of doing this….

Thanks for all your help….

Hi, this is a big topic area, so I can only give generic advice based on what you have said in your posting.

The things you want to split up (get onto seperate drives) are:

1) TempDb

2) Your user mdb files

3) your user ldf files

Usually the best performance drives are raid 1+0 for these, but in practice for your setup:

Put your ldf (log files) on your RAID 1 (mirrored) drives and your mdf (database) files on the RAID 5 drive. Depending on how you are using your tempdb and the size of it, it could be worth placing this on the system (i'm assuming C Smile drive.

Hope this helps.

|||Here's what I'd do although there are a lot of variables.

I'd leave the system databases and the ReportServer db's on the RAID 1. I'd move the user database log files only and TempDB to the new RAID array. (I assume you have one TempDB file per cpu.)

This way, you're separating your log I/O from your data I/O. Ideally, you'd have your TempDB on it's own RAID-5 (although RAID 10 is better since you get striping and mirroring) array if you had another device. The only qualifier might be if your databases are using the FULL recovery model and they are update intensive. If that were the case I would probably recommend putting the log file of the database that is most heavily updated on it's own device. Log files are written sequentially so the READ/WRITE heads would not move between updates and you'd get a little performance gain that way.

Just fyi, the more spindles the better. Because you really only have three logical devices available to you your optimization options are fairly limited. The fact that you're using RAID-5 indicates that there are multiple devices striped together but the parity overhead is a killer for write performance. I always recommend RAID-10 to my clients. You'll get far better overall performance but the tradeoff is that it's more expensive. Sometimes I lose that battle but they do it with their eyes open.
|||

Yes, i agree that temdb and log files should be there on RAID 1, but here its already Raid 5, we cant help , and both log files and data files are stored together on the same Raid 5.

Its not OLTP, its batch processing .

So, with the given requirement, what will be the best place for "system+ user + Reportserver & Reportservertempdb", Databases, in the new environment (i.e.) on the new drive.

So, what the suggestion now

Thanks again for all your help...

|||Define "batch" processing. Are the batches update intensive? Do they do a lot of sorting? Are there a lot of temp tables or table variables involved? Are the transactions short and sweet or do they involve thousands of rows before a commit occurs?

I would NOT put the tempdb or the log files on RAID 1 because you would lose the benefit of striping which distributes the I/O across multiple spindles.

You can re-distribute data or log files at any time using the ALTER DATABASE statement. At least you can in SQL 2005. I'm not sure what the procedure is in SQL 2000 but I'm sure it's possible.

Friday, February 24, 2012

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

DatabaseMetadata methods with catalog parameters now error if current database does not match.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

DatabaseMetadata methods with catalog parameters now error if current database does not match.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

Sunday, March 25, 2012

Datasets

Thursday, March 8, 2012

Data-driven subscription: put parameter in the filename text

Sunday, February 26, 2012

Databases Location

Friday, February 24, 2012

DatabaseMetadata methods with catalog parameters now error if current database does not match.

DatabaseMetadata methods with catalog parameters now error if current database does not match.

DatabaseMetadata methods with catalog parameters now error if current database does not match.

DatabaseMetadata methods with catalog parameters now error if current database does not match.

date and time diffs

Blog Archive

About Me