How to create indented, outline formatted report

Anyone thought of a clever way to create a proper outline format - for
example using the results from a recursive hierarchy via the Level function?
The recommended approach seems to be to use the left pad textbox attribute,
setting it to a formula based on the indentation level desired (e.g., =5 +
(Level * 10) & "Pt"). This sort of works, but all it does is make the
starting char move over to the right in a fixed position text box. If the
text is almost as long as the text box, it will wrap - unless you make the
text box (table control column) very wide (or merged with other cols), which
is not feasible if you have other table colums to its right.
The right way to do it would be to shift the position of the text box itself
to the right, but text box posn can not controlled by a formula. This seems
to be true even if the text box is in a list control.
Has anyone figured out a good way to do this?On Oct 12, 7:05 pm, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Anyone thought of a clever way to create a proper outline format - for
> example using the results from a recursive hierarchy via the Level function?
> The recommended approach seems to be to use the left pad textbox attribute,
> setting it to a formula based on the indentation level desired (e.g., =5 +
> (Level * 10) & "Pt"). This sort of works, but all it does is make the
> starting char move over to the right in a fixed position text box. If the
> text is almost as long as the text box, it will wrap - unless you make the
> text box (table control column) very wide (or merged with other cols), which
> is not feasible if you have other table colums to its right.
> The right way to do it would be to shift the position of the text box itself
> to the right, but text box posn can not controlled by a formula. This seems
> to be true even if the text box is in a list control.
> Has anyone figured out a good way to do this?
One way to do it (albeit a lot of work) could be to programmatically
create the RDL file. That way you can control the text box width on
the fly. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I have all but concluded that what I want to do (which
seems like something that is not at all uncommon) just isn't supported by RS.|||On Oct 13, 8:28 am, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Thanks for the reply. I have all but concluded that what I want to do (which
> seems like something that is not at all uncommon) just isn't supported by RS.
You're welcome. Another small thing that might control the table size
and expansion w/the option you mentioned is to put the table(s) in a
rectangle control. Here's an MSDN link to the option I mentioned
previously.
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

How to create disk space alert?

I would like SQL Server (7 and 2K) to alert me when disk
space drops to a preset level. Is there a way to do this
using sql agent alerts? I looked at the alerts that are
pre-defined, they are more DB oriented. If someone could
point me to some examples it would be appreciated.
Dave K.Which OS are you running on ?
W2KAS has "quota" management , so you might look into it.
there are also commercial prods available
W2K quotas can be found with the Windows Exploder -select disk--> right
click-->properties--> Quota tab
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...

quote:

---

> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

---

|||Can you do this without putting a limit on disk space?
"MaSa" <matti putTheDotHere saukkonen _ POISTA_ mandatum.fi> wrote in
message news:uWp2rKE3DHA.2544@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Which OS are you running on ?
> W2KAS has "quota" management , so you might look into it.
> there are also commercial prods available
> W2K quotas can be found with the Windows Exploder -select disk--> right
> click-->properties--> Quota tab
>
> "Dave K" <anonymous@.discussions.microsoft.com> wrote in message
> news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
>

---

|||Win2K. BTW, I don't want to restrict anything with a
quota, I just want to be notified when I run out of backup
space. I have some 15GB databases that eat up space
fast. If the regular maintenance backups don't delete the
prior jobs, and sometimes they don't, I want to be
notified before the backup fails, not after.
Dave K.

quote:

---

>--Original Message--
>Which OS are you running on ?
>W2KAS has "quota" management , so you might look into it.
>there are also commercial prods available
>W2K quotas can be found with the Windows Exploder -select

---

disk--> right

quote:

---

>click-->properties--> Quota tab
>
>"Dave K" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
this[QUOTE]
could[QUOTE]
>
>.
>

---

|||Here's how I do it.
I scan my SQL instances to run xp_fixeddrives to get free
disk space for each hard drive and check each to see
whether it's below a threshold. If yes, I raise a SQL
error of severity 17. My errorlog monitoring setup will
pick it up from there and page the DBAs.
Of course, this can be consistently done with a NetIQ
configuration as well.
Linchi

quote:

---

>--Original Message--
>I would like SQL Server (7 and 2K) to alert me when disk
>space drops to a preset level. Is there a way to do this
>using sql agent alerts? I looked at the alerts that are
>pre-defined, they are more DB oriented. If someone could
>point me to some examples it would be appreciated.
>Dave K.
>.
>

---

|||I put together this vbs file and run it from task scheduler, works for me.
'*****************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "support@.assoft.com.au"
sEmailTo = "support@.assoft.com.au"
sConEmailMonitor = "dgrover@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'***************************************
***************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'***************************************
***************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") =
"High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") =
"Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*****************************
Regards
Don Grover
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...

quote:

---

> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

---

sql

How to create disk space alert?

I would like SQL Server (7 and 2K) to alert me when disk
space drops to a preset level. Is there a way to do this
using sql agent alerts? I looked at the alerts that are
pre-defined, they are more DB oriented. If someone could
point me to some examples it would be appreciated.
Dave K.Which OS are you running on ?
W2KAS has "quota" management , so you might look into it.
there are also commercial prods available
W2K quotas can be found with the Windows Exploder -select disk--> right
click-->properties--> Quota tab
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.|||Can you do this without putting a limit on disk space?
"MaSa" <matti putTheDotHere saukkonen _ POISTA_ mandatum.fi> wrote in
message news:uWp2rKE3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> Which OS are you running on ?
> W2KAS has "quota" management , so you might look into it.
> there are also commercial prods available
> W2K quotas can be found with the Windows Exploder -select disk--> right
> click-->properties--> Quota tab
>
> "Dave K" <anonymous@.discussions.microsoft.com> wrote in message
> news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> > I would like SQL Server (7 and 2K) to alert me when disk
> > space drops to a preset level. Is there a way to do this
> > using sql agent alerts? I looked at the alerts that are
> > pre-defined, they are more DB oriented. If someone could
> > point me to some examples it would be appreciated.
> >
> > Dave K.
>|||Win2K. BTW, I don't want to restrict anything with a
quota, I just want to be notified when I run out of backup
space. I have some 15GB databases that eat up space
fast. If the regular maintenance backups don't delete the
prior jobs, and sometimes they don't, I want to be
notified before the backup fails, not after.
Dave K.
>--Original Message--
>Which OS are you running on ?
>W2KAS has "quota" management , so you might look into it.
>there are also commercial prods available
>W2K quotas can be found with the Windows Exploder -select
disk--> right
>click-->properties--> Quota tab
>
>"Dave K" <anonymous@.discussions.microsoft.com> wrote in
message
>news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
>> I would like SQL Server (7 and 2K) to alert me when disk
>> space drops to a preset level. Is there a way to do
this
>> using sql agent alerts? I looked at the alerts that are
>> pre-defined, they are more DB oriented. If someone
could
>> point me to some examples it would be appreciated.
>> Dave K.
>
>.
>|||Here's how I do it.
I scan my SQL instances to run xp_fixeddrives to get free
disk space for each hard drive and check each to see
whether it's below a threshold. If yes, I raise a SQL
error of severity 17. My errorlog monitoring setup will
pick it up from there and page the DBAs.
Of course, this can be consistently done with a NetIQ
configuration as well.
Linchi
>--Original Message--
>I would like SQL Server (7 and 2K) to alert me when disk
>space drops to a preset level. Is there a way to do this
>using sql agent alerts? I looked at the alerts that are
>pre-defined, they are more DB oriented. If someone could
>point me to some examples it would be appreciated.
>Dave K.
>.
>|||I put together this vbs file and run it from task scheduler, works for me.
'*****************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "support@.assoft.com.au"
sEmailTo = "support@.assoft.com.au"
sConEmailMonitor = "dgrover@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'******************************************************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'******************************************************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") ="High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") ="Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*****************************
Regards
Don Grover
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

How to create a table from an existing table.

I'm new to this table and my kniwledge of SQL is of intermediate level.
Could anyone help me/guide me how to create a tble from a nexisting table, without copying any of the records?
Any help is greatly appreciated.
Thanks in advance.
Kalpanatry Select * into <your new table name> from <your old table name> where 1 = 2|||ThankQ very much I have been thinking to include a condition which is always false. Good idea!|||Just remember that this will only copy the table structure and NOT handle any indexes, statistics or foreign keys.|||Thanks very much Paul.
Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

Wishes
Kalpana|||It's just the fieldnames & data types, so required properties, default settings, all (check, foreign key, primary key) constraints as well as external structures like indices won't be copied.|||Originally posted by kalpana_lloyd
Thanks very much Paul.
Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

Wishes
Kalpana

What have you worked out so far for this problem? Have you figured out how to connect ot your remote server? If you apply the same query on table1 two times what wil lyou call table2 the second time?|||I have two ADOconnection objects, each of which connects to a different database. My source table in in db1 which I have to filter and save in table2 of db2.
Each time the query is executed the name of table2 should be changed.
So, the second time table2 will not be table2, it might change to table3.|||Seems to me you have a few options here.

1. do your query on table1, get the results back to your app, analyse the resultset for datatypes returned, build a create table command in your app, create the table on db2 and then push the data to db2..table2.

2. similar to #1 above but extract the table structure directly from db1, build the table on db2 with the appropriate name change then move the data via your app.

3. Similar to #2 but use bcp to extract the resultset in bulk and then use insert bulk to push data into db2.

4. if your servers are linked together you could use a four part nameing convention to run a query on db1 stroeing the results on db2. To accomodate the need to chage the target table name you would need to execute dynamic sql.|||Thanks. As of now both of my tables are in the same p.c, but in different databases.
Im not able to find any objects which could allow me to do these transactions. As of now Im using ADOs. If you could suggest me which objects in ADO support any of these operations I shall be very grateful.|||okay how about this, The TSQL statment would be something like:

declare @.tblname varchar(50), @.tsql varchar(255)
set @.tblname = 'kalpana_lloyd'
set @.tsql = 'select * into db2..' + @.tblname + ' from db1..table1 where 1 = 2'
exec(@.tsql)

This could be sent as one statment in ADO or wrapped in an stored procedure.|||Thanks very much . I shall try this and see.|||Hint: You will need 2 connection objects, one for each database, unless both are running under the same instance of sql server.

Question: Do you have to create the destination table on the fly, or are you just copying data from table to table? If you have to create the new table on the fly, you may need to create some logic to test for an existing table - if you have Query Analyzer, go into the templates and take a look at the script for creating table, it will have a portion starting with an "IF EXISTS" that checks for the existance of the table name you are trying to create, and drops it from the database...

However, if you are just copying the data over, then the first time you would need to create the table, and each following time you would need an insert rather than "SELECT INTO"

Good luck!|||if the two dbs are on the same server only one connection is needed.|||Thanks Paul. Actually my tables are in Access database. Because one can use T-SQL in VB using ADO's I have posted this doubt in this forum,. Moreover, I could'nt find any apt forum to post my question. I'm trying all my best , what Paul has told is working fine in SQL Server, now I need to see how I can implement this in VB.Please accept my apologies for I haven't made my doubt clear.
Thanks.|||Thanks Paul. Your suggestion has just worked even with Access databases, though both of my databases are in different locations.
This is what I used in VB
"Select * into mailshot.mdb.." & txtTableName & " from Contacts " & strWhereClause
I have been struggling for this since one week. Thanks again.
Kalpana

Originally posted by kalpana_lloyd
Thanks Paul. Actually my tables are in Access database. Because one can use T-SQL in VB using ADO's I have posted this doubt in this forum,. Moreover, I could'nt find any apt forum to post my question. I'm trying all my best , what Paul has told is working fine in SQL Server, now I need to see how I can implement this in VB.Please accept my apologies for I haven't made my doubt clear.
Thanks.

HOw to create a CUBE

Hi,

I am quite familiar with Pivot Tables and understand the concept of dimensional analysis. I want to take my understanding to the next level and want to learn how to make cubes (and analyze them) using SQL server 2000.

I'll appreciate if you can suggest a URL and ideally a book or two which will help me in this regard.

Many thankxs u in advance.

-JAnalysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH|||Originally posted by rhigdon
Analysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH

Many thanks. I'll try to get these books. Can you in the meanwhile recommend and URL also?|||For DW theory and design this is good

http://intelligententerprise.com/ports/search_dw_fund.shtml

To download BOL, go here

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?