Sunday, March 25, 2012
a couple reporting/ normalization questions
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
TIA,
ChrisR
to answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR
|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...[vbcol=seagreen]
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
sql
a couple reporting/ normalization questions
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
--
TIA,
ChrisRto answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
>> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>> is
>> used only for reporting, no OLTP. The queries are quite slow to say the
>> least. Every day this data is truncated and repopulated from a mainframe.
>> From what I can tell there are a few options here.
>> 1; Normalize it. The problem here is there are 150+ Stored Procs
>> dependant
>> on the existing structure, and of course nobody has time to re-write
>> them.
>> Theres probably software that can do this for us, (?) but if so, does it
>> really work well? Also, the DTS Package that populates this DB every day
>> would need total overhauling.
>> 2; Forget this pile of crap. Let it stay how it is for existing stuff.
>> But
>> have new stuff go into Analysis Services, and then use Reporting
>> Services.
>> Everyone here want to use AS and RS anyways, so thats the way we are
>> leaning. The problem is I have little experience with either of these
>> tools.
>> Im open to the challenge and we move fairly slow anyways, so I think it
>> can
>> work. Now my questions.
>> 1; Can Reporting Services connect/report from Analysis Services?
>> 2, Can AS be populated from a totally de-normalized DB? Can I design the
>> cubes how they should be, and take the data from several different tables
>> to
>> populate them?
>> 3; If #2 isnt hopefull, would I be better off to create a new, well
>> designed
>> db, populate that, and from there populate AS?
>> All insights are appreciated.
>> --
>> TIA,
>> ChrisR
a couple reporting/ normalization questions
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
TIA,
ChrisRto answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well design
ed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well design
ed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...[vbcol=seagreen]
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
>
A couple of, I hope Basic Questions RE SqlDataSources and scripting
Greetings!
I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code.
txOTHrs.Attributes[
"onBlur"] ="return calculateTotal(this)";The onblur event handler is coded as follows
function calculateTotal(tb){
var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt;document.getElementById(
"txTotal").value = total;}
Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?
My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
Any help anyone can provide is greatly appreciated!!
If you are comming from a "Windows Forms" world, then there are a few key concepts that you are going to need to understand... most especially is the "Page Lifecycle" which I will mention further down... but to answer your questions quickly:
- Yes... you need to "PostBack" to the server at any time that you want to reflect changes.
sabrecat:
Have I missed some step where I have to notify the server that a value in the control has changed ?
- Again, Yes... here is a small example: DataView myDataView = (DataView)this.MarketDataSource.Select(DataSourceSelectArguments.Empty);
sabrecat:
In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
foreach (DataRow rowin myDataView.Table.Rows)
{
// do something here :)
}
// Or you can do...
string firstPersonsLastName = (string)myDataView.Table.Rows[0]["LastName"];
Here is the "Page Lifecycle" that I mentioned earlier:
http://msdn2.microsoft.com/en-us/library/ms178472.aspx
http://www.eggheadcafe.com/articles/o_aspNet_Page_LifeCycle.jpg
Read the MSDN article, save that picture to your desktop and look at it often :)
I hope this helps.
Peace,
|||Thank you very much for the reply! You have been tremendously helpful!
I thought that the data that I was putting into the control wasnt getting back to the serve. I wasnt sure what mechanism to use however to get it into the server. To actually perform thepostback I would use the __doPostBack function that ASP.Net puts into the page right ? I mean in my script I'd do somthing like
function calculateTotal(e, tb)
{
var regHrs = document.getElementById ("txRegHrs").value;
var otHrs = document.getElementById("txOtHrs").value;
var rate = document.getElementById("txRate").value;
var regAmt = rate * regHrs;
var otAmt= rate * ( otHrs * 1.5 );
var total = otAmt + regAmt;
document.getElementById("txTotal").value = total;
__doPostBack(tb,e);
}
Since my calulateTotal function right now only has the one argument (tb) I assume then that I would have to change my codebehind page to look somthing like
txOTHrs.Attributes[
"onBlur"] ="return calculateTotal(event, this)";instead of
txOTHrs.Attributes["onBlur"] ="return calculateTotal(this)";
Regarding the SqlDataSource. Does the argument DataSourceSelectArguments.Empty make the invocation of the Select method NOT go out to the database again ? If this is not the case and the database is hit again then I would want to get the data into a DataView at the time that I do my initial select on the database and then put that object into the session to make it persistant between page reloads ?
Again thank you very much for all the insights and the links to some really good data.
A couple of things with my new report
I have made a report in SRS and am not sure of how to do some of the functions I need.
In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. When I add UPPER to the SQL in my SRS report, the report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?
In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?
Thanks.
Why not put all of your TSQL into a stored procedure? I have just completed a project where this was done.
>Use NETWORKDAYS to calculate employee benefits that accrue based on thenumber of days worked during a specific term.) Is there somethingsimilar I can use in SRS?
It is possible to write such a function - I wrote one recently - will try and find a reference to it.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Holiday]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[YYYYMMDD] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[Holiday] WITH NOCHECK ADD
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Holiday] ON [dbo].[Holiday]([YYYYMMDD]) ON [PRIMARY]
GO
execsp_addextendedproperty N'MS_Description', N'Date in yyyyMMdd notation',N'user', N'dbo', N'table', N'Holiday', N'column', N'YYYYMMDD'
GO
CREATE FUNCTION dbo.fnAddWorkdays(@.FROM DATETIME, @.DAYS INT)
-- Purpose:
-- Calculate Date @.DAYS in future from @.FROM
-- Copyright (C) 2007 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
RETURNS DATETIME AS
BEGIN
DECLARE @.YYYYMMDD CHAR(8)
WHILE @.DAYS > 0
BEGIN
SET @.FROM = DATEADD(day, 1, @.FROM)
SET @.YYYYMMDD = SUBSTRING(REPLACE(CONVERT(CHAR(20), @.FROM, 126), '-', ''), 1, 8)
IF DATEPART(weekday,@.FROM) NOT IN (1, 7) BEGIN
IF NOT EXISTS(SELECT * FROM Holiday WHERE YYYYMMDD = @.YYYYMMDD) SET @.DAYS = @.DAYS - 1
END
END
RETURN @.FROM
END
GO
SELECT dbo.fnAddWorkdays(GETDATE(), 1)
SELECT dbo.fnAddWorkdays(GETDATE(), 2)
SELECT dbo.fnAddWorkdays(GETDATE(), 3)
-- I will write another function later today.
A couple of things with my new report
I have made a report in SRS and am not sure of how to do some of the functions I need.
In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. The report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?
In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?
Thanks.
You should be able to use UPPER in your SQL, certainly works for me....
i.e.
Code Snippet
select
acct.accno,
UPPER(nonpers.name) as 'name',
term.amount,
term.eod_balance,
Hello Joe,
There are 2 ways to fix your upper case requirement:
1. Like Will said, you can do it straight from your SQL query.
2. The equivalent function from within SSRS is UCase. Use it like this: =UCase(Fields!Field1.Value)
As for your other question, I think you'll have to write a custom function to handle that.
Hope this helps.
Jarret
sqlA couple of SQL Server Express questions
Hello - I'm about to rebuild my website on a nice new windows 2k3 server. I was previously using SQL server 7 but I'd like to use the 2005 edition.
Here's my first question - is the Express edition 2005 good enough to use on my live web server? Are there limits on the amount of connections at any one time?
My second question is this - what's the best way to connect to SQL Server 2005 Express?
At the moment I would use something like:
Server=.\SQLEXPRESS;Integrated Security=True;Database=myDB
And then I would set up a COMPUTERNAME\ASPNET account under SQL Server 2005 Express.
Is that an ok way to connect??
Any ideas?
Thanks.
Hi,
1) you don't have connection limits with SQl Express. Limitations are related to maximum RAM (1GB), 1 CPU and maximum database size. You can see information also at http://msdn.microsoft.com/vstudio/express/support/faq/#sql
2) That means using Windows authentication to connect. That is fine as long as you use this single account (built-in ASp.NET one) to connect to SQL Express, so that you don't provide own login (account) for each user, since that would cause connection pool fragmentation. (http://msdn2.microsoft.com/en-US/library/8xx3tyca(VS.80).aspx) If you do as you describe here, that's ok.
A couple of quick questions about SQL Server 6.5
1. Is there a stored proc that I can use in conjunction with
sp_foreachtable to step though all the user tables and list the permissions
that the public role has.
2. When I use the generate SQL Script , to have it script up all the
logins,users and permissions. I then look at the resulting script and it seem
to set all the passwords to (Null). Is there a way to have the passwords
intact?
Hi
1. I think you would need to look at writing your own that queries the
sysprotects/sysusers tables.
2. I believe this is by design. You may want to look at:
http://tinyurl.com/5yw77
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> I have a couple of quick questions to do with SQL 6.5.
> 1. Is there a stored proc that I can use in conjunction with
> sp_foreachtable to step though all the user tables and list the
permissions
> that the public role has.
> 2. When I use the generate SQL Script , to have it script up all the
> logins,users and permissions. I then look at the resulting script and it
seem
> to set all the passwords to (Null). Is there a way to have the passwords
> intact?
|||> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
Or perhaps sp_helprotect?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi
> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
> 2. I believe this is by design. You may want to look at:
> http://tinyurl.com/5yw77
> John
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> permissions
> seem
>
|||Hi Tibor
I didn't think that was in 6.5?
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>
> Or perhaps sp_helprotect?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
it[vbcol=seagreen]
passwords
>
|||Hi John,
To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the proc did exist in 6.5.
Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I guess we'll wait until
someone with 6.5 or 6.5 BOL can tell us for certain. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor
> I didn't think that was in 6.5?
> John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
> it
> passwords
>
|||sp_helprotect has been around since since the beginning.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234k4pTmnEHA.3988@.tk2msftngp13.phx.gbl...
> Hi John,
> To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the
> proc did exist in 6.5.
> Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I
> guess we'll wait until someone with 6.5 or 6.5 BOL can tell us for
> certain. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
>
A couple of quick questions about SQL Server 6.5
1. Is there a stored proc that I can use in conjunction with
sp_foreachtable to step though all the user tables and list the permissions
that the public role has.
2. When I use the generate SQL Script , to have it script up all the
logins,users and permissions. I then look at the resulting script and it seem
to set all the passwords to (Null). Is there a way to have the passwords
intact?Hi
1. I think you would need to look at writing your own that queries the
sysprotects/sysusers tables.
2. I believe this is by design. You may want to look at:
http://tinyurl.com/5yw77
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> I have a couple of quick questions to do with SQL 6.5.
> 1. Is there a stored proc that I can use in conjunction with
> sp_foreachtable to step though all the user tables and list the
permissions
> that the public role has.
> 2. When I use the generate SQL Script , to have it script up all the
> logins,users and permissions. I then look at the resulting script and it
seem
> to set all the passwords to (Null). Is there a way to have the passwords
> intact?|||> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
Or perhaps sp_helprotect?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi
> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
> 2. I believe this is by design. You may want to look at:
> http://tinyurl.com/5yw77
> John
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> I have a couple of quick questions to do with SQL 6.5.
>> 1. Is there a stored proc that I can use in conjunction with
>> sp_foreachtable to step though all the user tables and list the
> permissions
>> that the public role has.
>> 2. When I use the generate SQL Script , to have it script up all the
>> logins,users and permissions. I then look at the resulting script and it
> seem
>> to set all the passwords to (Null). Is there a way to have the passwords
>> intact?
>|||Hi Tibor
I didn't think that was in 6.5?
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
> > 1. I think you would need to look at writing your own that queries the
> > sysprotects/sysusers tables.
>
> Or perhaps sp_helprotect?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > 1. I think you would need to look at writing your own that queries the
> > sysprotects/sysusers tables.
> >
> > 2. I believe this is by design. You may want to look at:
> > http://tinyurl.com/5yw77
> >
> > John
> >
> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> >> I have a couple of quick questions to do with SQL 6.5.
> >>
> >> 1. Is there a stored proc that I can use in conjunction with
> >> sp_foreachtable to step though all the user tables and list the
> > permissions
> >> that the public role has.
> >>
> >> 2. When I use the generate SQL Script , to have it script up all the
> >> logins,users and permissions. I then look at the resulting script and
it
> > seem
> >> to set all the passwords to (Null). Is there a way to have the
passwords
> >> intact?
> >
> >
>|||Hi John,
To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the proc did exist in 6.5.
Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I guess we'll wait until
someone with 6.5 or 6.5 BOL can tell us for certain. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor
> I didn't think that was in 6.5?
> John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>>
>> Or perhaps sp_helprotect?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
>> > Hi
>> >
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>> >
>> > 2. I believe this is by design. You may want to look at:
>> > http://tinyurl.com/5yw77
>> >
>> > John
>> >
>> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> >> I have a couple of quick questions to do with SQL 6.5.
>> >>
>> >> 1. Is there a stored proc that I can use in conjunction with
>> >> sp_foreachtable to step though all the user tables and list the
>> > permissions
>> >> that the public role has.
>> >>
>> >> 2. When I use the generate SQL Script , to have it script up all the
>> >> logins,users and permissions. I then look at the resulting script and
> it
>> > seem
>> >> to set all the passwords to (Null). Is there a way to have the
> passwords
>> >> intact?
>> >
>> >
>>
>|||sp_helprotect has been around since since the beginning.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234k4pTmnEHA.3988@.tk2msftngp13.phx.gbl...
> Hi John,
> To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the
> proc did exist in 6.5.
> Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I
> guess we'll wait until someone with 6.5 or 6.5 BOL can tell us for
> certain. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
>> Hi Tibor
>> I didn't think that was in 6.5?
>> John
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>>
>> Or perhaps sp_helprotect?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
>> > Hi
>> >
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>> >
>> > 2. I believe this is by design. You may want to look at:
>> > http://tinyurl.com/5yw77
>> >
>> > John
>> >
>> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> >> I have a couple of quick questions to do with SQL 6.5.
>> >>
>> >> 1. Is there a stored proc that I can use in conjunction with
>> >> sp_foreachtable to step though all the user tables and list the
>> > permissions
>> >> that the public role has.
>> >>
>> >> 2. When I use the generate SQL Script , to have it script up all the
>> >> logins,users and permissions. I then look at the resulting script and
>> it
>> > seem
>> >> to set all the passwords to (Null). Is there a way to have the
>> passwords
>> >> intact?
>> >
>> >
>>
>>
>
A couple of questions on Remote connection to SQL Server 2005 database
What i want is to have access to BDHSE from an application in VB6 (APP1) running in PC2. All the INSERT, DELETE, UPDATE records process is done through APP1.
APP1 ia currently running in PC1 and is to be installed on PC2.
I have these questions:
1. What do i need to install in PC2 since all the INSERT, DELETE, and UPDATE is done using APP1? I guess i only have to install the Microsoft SQL Native Client (with all the prerequisites of course) but i am not sure.
2. In the APP1 made in VB6, do i have to change the connectionstring since i am accesing the database which physically is at PC1 and the APP1 will be used in PC2?
3. Any advice you can give me on doing this will be well received.
Thanks in advance,
BSc Fernando Martinez
1. If you do not want to use any new features of the SQL Server 2k5, you can also install the MDAC stack. You don′t need to install the SNAC drivers. If you install the SNAC drivers and want to use them, make sure that you change your connectionstring (samles can be found on the site www.connectionstrings.com).2. I don′t get your throught here, but you will have to point the connectionstring of the application to the server where the database is actually hosted.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||thanks, i found out the problem.
A couple of questions for the folk at Microsoft.
regarding database comparison and synchronization tools are passed directly
to them only.
Is this true, does Microsoft have an exclusive deal with them?
If so, where does that leave all the other tool vendors?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
Hi
Ask the Red Gate guys for this in writing.
No vendor is endorsed in such a manner by Microsoft.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2366787B-B08D-4E52-AB1A-A4B6ED06959A@.microsoft.com...
> At Tech Ed (Amsterdam) Red Gate are claiming that any enquires to
> Microsoft
> regarding database comparison and synchronization tools are passed
> directly
> to them only.
> Is this true, does Microsoft have an exclusive deal with them?
> If so, where does that leave all the other tool vendors?
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
|||thanks
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Ask the Red Gate guys for this in writing.
> No vendor is endorsed in such a manner by Microsoft.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
> news:2366787B-B08D-4E52-AB1A-A4B6ED06959A@.microsoft.com...
>
>
|||This is interesting, we wonder whether they are Microsoft Certified Partner
or some sort?
John King
http://www.agileinfollc.com
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2366787B-B08D-4E52-AB1A-A4B6ED06959A@.microsoft.com...
> At Tech Ed (Amsterdam) Red Gate are claiming that any enquires to
> Microsoft
> regarding database comparison and synchronization tools are passed
> directly
> to them only.
> Is this true, does Microsoft have an exclusive deal with them?
> If so, where does that leave all the other tool vendors?
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
sql
A couple of questions
Well, I had a questions, but I figured I'd include all of the things I've been trying to figure out.
I remember reading in books online about transformnig the data returned by a SQL query. For example, the database has a boolean 1 or 0, but I want to return a Yes / No or True / False. You could even doa number mapping for example, 1 becomes "one", 2 becomes "two" etc. Can anyone point me in the right direction?
Is there a way to check a data field for a specific format? SELECT IDNumber from table WHERE IDNumber is not 6 numberic digits. (IDNumber is nvarchar with a length of 50 and stores IDNUmbers that also contain letters and special characters)
Query returns 123456789, 12345678, 2-0152, A5487, but not 123456 or 101254, etc.
How do I insert several lines if I have the data as text? I can insert multiple lines from another table, but how do I insert multiple lines if I want to type in the data?
How do I insert the same line into several SQL database on different computers using query analyzer? I basically have to enter a line for each user into the user table. I can do it with osql, but I don't know haw to change the server in query analyzer. With query analyzer I can create a script and just substitute the variables defined at the beginning.
Thanks,
Cory
1. To 'transform' data, use the CASE structure.
Code Snippet
SELECT Gender = CASE Sex
WHEN 1 THEN 'M'
WHEN 0 THEN 'F'
END
FROM MyTable.
2. To check for specific format, i.e., 6 character number, something like this 'should' work for you (it's not perfect, but I think it may give you what you desire):
Code Snippet
SET NOCOUNT ON
DECLARE @.MyTable table
( IDNumber varchar(50) )
INSERT INTO @.MyTable VALUES ( '123456789' )
INSERT INTO @.MyTable VALUES ( '12345678' )
INSERT INTO @.MyTable VALUES ( '2-0152' )
INSERT INTO @.MyTable VALUES ( 'A5487' )
INSERT INTO @.MyTable VALUES ( 'A54871' )
INSERT INTO @.MyTable VALUES ( '123456' )
INSERT INTO @.MyTable VALUES ( '101254' )
SELECT IDNumber
FROM @.MyTable
WHERE ( len( IDNumber ) <> 6
OR isnumeric( IDNumber ) = 0
)
--
123456789
12345678
2-0152
A5487
A54871
3.
How do I insert several lines if I have the data as text?
I don't understand what you want to do here, please give more information.
4. Click on [File], [Connect], to connect to a different server. Open your script file, or cut/paste from the previous connection window.
(OR)
Set the additional Servers up as 'linked servers'. Refer to Books Online, Topic: 'Linked Server', sp_addlinkedserver.
Then you can use four object part naming to designate the server.
|||Hi Arnie,
May be we can use following statement for point number 2.
select
*
from
@.MyTable
where
IDNumber notlike'[0-9][0-9][0-9][0-9][0-9][0-9]'
AMB
|||Thank you for your quick reply.
For #3. In your example you used 7 insert statements to insert the rows into the table. Is there a way to insert them using one statement?
For #4. Is there a way to do it without adding linked servers? In osql, I type
osql -S 192.168.1.123 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")
osql -S 192.168.1.124 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")
osql -S 192.168.1.125 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")
osql -S 192.168.1.126 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")
Is there a way to do it in query analyzer without linked servers.
Thanks,
Cory
A couple of easy questions
Hi everyone,
Running Windows 2003 x64 and Sql25k 64-bit edition
We just have a couple of questions related with our system:
-What is Sql Server VSS Writer service for?
-What sort of implications must we assume when Integration Services service is running as NT AUTHORITY\NetworkService
Any link or advice would be welcomed.
Thanks for your time,
SQL Writer Service
(http://msdn2.microsoft.com/en-us/library/ms175536.aspx)
Setting Up Windows Service Accounts
(http://msdn2.microsoft.com/en-us/library/309b9dac-0b3a-4617-85ef-c4519ce9d014.aspx)
For SSIS the implications are much the same as any service, just bear in mind what the SSIS service does, not a lot really.
|||Thank you|||Be sure to mark the answers to your questions as an answer to the thread.
A couple of easy questions
1)
I was wondering myself, how many characters can handle Sql2k when you define
a field? Such limitation is common for any object? I mean, tables, indexes,
jobs.
I've got an ASP application which allow the user create fields and I would
need to know that rule in order to define a maxlenght value.
2)
I had in my EM remote servers added but today they are dissapeared although
now are currently up. Does anyone ever experienced such behaviour? It coul
d
be a shutdown of that server or even if that server is off from the client
vanishes...
Thanks a lot for your suggestions,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Hi
1)
Lookup in the BOL for maximum and minimum ovjects limits article
2)
I have not, but did you refresh your EM?
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
> Dear all,
> 1)
> I was wondering myself, how many characters can handle Sql2k when you
> define
> a field? Such limitation is common for any object? I mean, tables,
> indexes,
> jobs.
> I've got an ASP application which allow the user create fields and I would
> need to know that rule in order to define a maxlenght value.
> 2)
> I had in my EM remote servers added but today they are dissapeared
> although
> now are currently up. Does anyone ever experienced such behaviour? It
> could
> be a shutdown of that server or even if that server is off from the client
> vanishes...
>
> Thanks a lot for your suggestions,
>
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||hi again Uri,
Yes, I did. How odd, isn't?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:
> Hi
> 1)
> Lookup in the BOL for maximum and minimum ovjects limits article
> 2)
> I have not, but did you refresh your EM?
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
>
>|||
> Yes, I did. How odd, isn't?
Hi Enric, do you have 'Open object explorer and new query' in Tools/Options
dialog under Environment/General leaf?
I noticed that if object explorer is not mentioned in selected combo,
servers don't show up.
Peter|||Hi Rogas,
I don't see that option. From Entreprise Manager->Tools->Options I have two
leafs: General and Advanced. In General there is nothing related with you
said.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Rogas69" wrote:
>
> Hi Enric, do you have 'Open object explorer and new query' in Tools/Option
s
> dialog under Environment/General leaf?
> I noticed that if object explorer is not mentioned in selected combo,
> servers don't show up.
> Peter
>
>
A couple of beginner's questions about reporting services
1. Is there any way to program a field to clear, or even hide, under certain
conditions?
2. Is there any way to simulate a check box, to display boolean values? (I
don't see anything in the toolbox.)On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
wrote:
> I'm writing my first RS report.
> 1. Is there any way to program a field to clear, or even hide, under certain
> conditions?
> 2. Is there any way to simulate a check box, to display boolean values? (I
> don't see anything in the toolbox.)
1. Yes. Many of the controls on SSRS are expression based. In your
scenario, expressions can be used to manipulate the Hidden property of
a textbox based on other data, constants, etc.
2. Assuming you're talking about the body of the report. I have used
images in the past to simulate an X or a checkmark.|||I don't think I really understand what you mean by expressions yet. Are you
talking about writing what appears to be a vb command in the format field?
If so, I haven't been able to get that to work yet. What I'd like to do is
something like this:
iif(instr(Fields!SomeValue.Value, "A String") > 0,"",Fields!SomeValue.Value)
In other words, if the value of the incoming string does contain a
particular pattern, blank the box, otherwise display it. Or have I
completely misunderstood the concept?
"Potter" wrote:
> On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
> wrote:
> > I'm writing my first RS report.
> >
> > 1. Is there any way to program a field to clear, or even hide, under certain
> > conditions?
> >
> > 2. Is there any way to simulate a check box, to display boolean values? (I
> > don't see anything in the toolbox.)
>
> 1. Yes. Many of the controls on SSRS are expression based. In your
> scenario, expressions can be used to manipulate the Hidden property of
> a textbox based on other data, constants, etc.
> 2. Assuming you're talking about the body of the report. I have used
> images in the past to simulate an X or a checkmark.
>|||On May 10, 11:38 am, Potter <drewpot...@.gmail.com> wrote:
> On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
> wrote:
> > I'm writing my first RS report.
> > 1. Is there any way to program a field to clear, or even hide, under certain
> > conditions?
> > 2. Is there any way to simulate a check box, to display boolean values? (I
> > don't see anything in the toolbox.)
> 1. Yes. Many of the controls on SSRS are expression based. In your
> scenario, expressions can be used to manipulate the Hidden property of
> a textbox based on other data, constants, etc.
> 2. Assuming you're talking about the body of the report. I have used
> images in the past to simulate an X or a checkmark.
hi there
In the properties box - there is a visibility property - you can use
this and toggle it depending on other fields.
hope that helps
jewel|||Actually no, I need a way to program the visibility based on content.
Clicking on a field won't do.
"jewelfire" wrote:
> On May 10, 11:38 am, Potter <drewpot...@.gmail.com> wrote:
> > On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
> > wrote:
> >
> > > I'm writing my first RS report.
> >
> > > 1. Is there any way to program a field to clear, or even hide, under certain
> > > conditions?
> >
> > > 2. Is there any way to simulate a check box, to display boolean values? (I
> > > don't see anything in the toolbox.)
> >
> > 1. Yes. Many of the controls on SSRS are expression based. In your
> > scenario, expressions can be used to manipulate the Hidden property of
> > a textbox based on other data, constants, etc.
> > 2. Assuming you're talking about the body of the report. I have used
> > images in the past to simulate an X or a checkmark.
> hi there
> In the properties box - there is a visibility property - you can use
> this and toggle it depending on other fields.
> hope that helps
> jewel
>|||In the visibility property, hidden can be set to true, false or expression.
Pick expression and put the appropriate expression there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:6211D77E-5C9B-4206-B823-CE6273331547@.microsoft.com...
> Actually no, I need a way to program the visibility based on content.
> Clicking on a field won't do.
> "jewelfire" wrote:
>> On May 10, 11:38 am, Potter <drewpot...@.gmail.com> wrote:
>> > On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
>> > wrote:
>> >
>> > > I'm writing my first RS report.
>> >
>> > > 1. Is there any way to program a field to clear, or even hide, under
>> > > certain
>> > > conditions?
>> >
>> > > 2. Is there any way to simulate a check box, to display boolean
>> > > values? (I
>> > > don't see anything in the toolbox.)
>> >
>> > 1. Yes. Many of the controls on SSRS are expression based. In your
>> > scenario, expressions can be used to manipulate the Hidden property of
>> > a textbox based on other data, constants, etc.
>> > 2. Assuming you're talking about the body of the report. I have used
>> > images in the past to simulate an X or a checkmark.
>> hi there
>> In the properties box - there is a visibility property - you can use
>> this and toggle it depending on other fields.
>> hope that helps
>> jewel
>>|||Missed that! That's what I was looking for.
Just to play safe, here is my code.
=IIf (InStr(me.Value,"1/1/1753") > 0, True ,False)
I'm assuming that 'Me' in this context, refers to the current text field.
As I mentioned elsewhere, this is legacy code with '1/1/1753' indicating a
blank date. (No nulls, period. Company rules.)
"Bruce L-C [MVP]" wrote:
> In the visibility property, hidden can be set to true, false or expression.
> Pick expression and put the appropriate expression there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> news:6211D77E-5C9B-4206-B823-CE6273331547@.microsoft.com...
> > Actually no, I need a way to program the visibility based on content.
> > Clicking on a field won't do.
> >
> > "jewelfire" wrote:
> >
> >> On May 10, 11:38 am, Potter <drewpot...@.gmail.com> wrote:
> >> > On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
> >> > wrote:
> >> >
> >> > > I'm writing my first RS report.
> >> >
> >> > > 1. Is there any way to program a field to clear, or even hide, under
> >> > > certain
> >> > > conditions?
> >> >
> >> > > 2. Is there any way to simulate a check box, to display boolean
> >> > > values? (I
> >> > > don't see anything in the toolbox.)
> >> >
> >> > 1. Yes. Many of the controls on SSRS are expression based. In your
> >> > scenario, expressions can be used to manipulate the Hidden property of
> >> > a textbox based on other data, constants, etc.
> >> > 2. Assuming you're talking about the body of the report. I have used
> >> > images in the past to simulate an X or a checkmark.
> >>
> >> hi there
> >>
> >> In the properties box - there is a visibility property - you can use
> >> this and toggle it depending on other fields.
> >>
> >> hope that helps
> >> jewel
> >>
> >>
>
>|||I haven't used Me in RS (I've used it in VB). It is easy to test. Most
errors are picked up when you try to preview it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:52FB048B-27B0-436A-A269-8B1CC83E12AA@.microsoft.com...
> Missed that! That's what I was looking for.
> Just to play safe, here is my code.
> =IIf (InStr(me.Value,"1/1/1753") > 0, True ,False)
> I'm assuming that 'Me' in this context, refers to the current text field.
> As I mentioned elsewhere, this is legacy code with '1/1/1753' indicating a
> blank date. (No nulls, period. Company rules.)
> "Bruce L-C [MVP]" wrote:
>> In the visibility property, hidden can be set to true, false or
>> expression.
>> Pick expression and put the appropriate expression there.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
>> news:6211D77E-5C9B-4206-B823-CE6273331547@.microsoft.com...
>> > Actually no, I need a way to program the visibility based on content.
>> > Clicking on a field won't do.
>> >
>> > "jewelfire" wrote:
>> >
>> >> On May 10, 11:38 am, Potter <drewpot...@.gmail.com> wrote:
>> >> > On May 9, 4:13 pm, B. Chernick <BChern...@.discussions.microsoft.com>
>> >> > wrote:
>> >> >
>> >> > > I'm writing my first RS report.
>> >> >
>> >> > > 1. Is there any way to program a field to clear, or even hide,
>> >> > > under
>> >> > > certain
>> >> > > conditions?
>> >> >
>> >> > > 2. Is there any way to simulate a check box, to display boolean
>> >> > > values? (I
>> >> > > don't see anything in the toolbox.)
>> >> >
>> >> > 1. Yes. Many of the controls on SSRS are expression based. In
>> >> > your
>> >> > scenario, expressions can be used to manipulate the Hidden property
>> >> > of
>> >> > a textbox based on other data, constants, etc.
>> >> > 2. Assuming you're talking about the body of the report. I have
>> >> > used
>> >> > images in the past to simulate an X or a checkmark.
>> >>
>> >> hi there
>> >>
>> >> In the properties box - there is a visibility property - you can use
>> >> this and toggle it depending on other fields.
>> >>
>> >> hope that helps
>> >> jewel
>> >>
>> >>
>>|||You can also hide the field using an expression like this (instead of an iif statement):
Click your field, go to your properties box and scroll down to Visibility. Under that you have a hidden option--select Expression.
In the expression paste in a TRUE statement for what you want hidden
=Fields!Dates.Value= "1753"
That will hide the field just under certain conditions.
Hope that also gives you some flexibility
From http://www.developmentnow.com/g/115_2007_5_0_0_968676/A-couple-of-beginners-questions-about-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
A couple LDF questions.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the databa
se. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emp
tied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do
log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions.
It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup ye
t.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
A couple LDF questions.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
A couple LDF questions.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
Tuesday, March 20, 2012
A big, big SSIS for everything?
Dear all,
I would like to hear point of views, thoughts and ideas about the following request.
As some of us would have seen from a couple a days ago I’ve been setting posts one after one in order to obtain some answers about our business basis.
We’ve got a service running all day long (to accomplish this it was wrote by Visual Basic 6.0) which is checking up a file structure system for a several requirements such a verify if exists a .nul or .txt in each folder and therefore fires a DTS package. Up to here that’s fine, that system works amazing and even is be able to run till three packages at the same time. 400 Dts are scheduled this way and 100 are scheduled by Agent.
Problem comes naturally with SQL2005 where such control is not possible (it seems so) from old tools belonging to Visual Studio 6.0 because the aforementioned version uses assemblies instead of dll, bla,bla.
Keeping in mind that .NET is not possible for reasons that going beyond of any technical matter or whatever I’ve thought do that service by SSIS. A big SSIS running forever. Its responsabilities might be:
1-Look in each folder for the criteria specified in the scheduling of that SSIS
2-Launch a specified SSIS in the case that criteria has matched.
3-Update SQL tables with LOGS, times, executions, etc,
4-Look again
The point 3 will be visible from our Intranet via ASP 3.0 pages as currently we do.
Pros and cons?
Sorry for this painful request but I don’t believe that such issue can be of that kind of odd issues that nodoby suffer in its organizations.
Who else is needed for answers?
Thanks a lot for your time,
I'm not quite sure what your question is. Are you saying you want to run a SSIS package forever that simply keeps looping whenever a file is found? I don't see a problem with that. it would be interesting to see how it goes.
-Jamie
|||When finds a Jamie.Nul file in d:\server25k\a\ launch SSIS package B and then would delete that .NUL.
Sensible information attached in that SSIS launched as flat file for example, accounts info would be stored in a backup folder. After one execution come back to verify and so on
Monday, March 19, 2012
8gb of log files - how to i stop ?
Reporting services has been running for a couple of months. I noticed today
that I had 8GB of log files. Is there anything I can do to stop so much
being logged ? i don't really need it.
Thanks
ScottThis is a known issue (Bug #: 304241 -
http://support.microsoft.com/kb/885286/) and is fixed in Reporting Services
SP2 http://www.microsoft.com/downloads/details.aspx?FamilyID=502C0D89-130...
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"scott" <nospamscott@.yahoo.com> wrote in message
news:uConQxBlFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Reporting services has been running for a couple of months. I noticed
> today that I had 8GB of log files. Is there anything I can do to stop so
> much being logged ? i don't really need it.
> Thanks
> Scott
>|||many thanks
scott