How to show all tables info in Task Pad?

In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.

1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?

2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?

Thanks.Task pad should show Next and Last options in the bottom of the page.
You can also get all user table info by executing this sql..
select * from information_schema.tables where table_type like 'BASE TABLE'|||First, in TaskPad, there is no next or last button, second that line of code you gave:

select * from information_schema.tables where table_type like 'BASE TABLE'

Did not return the # of rows and KB size of all of my tables.

THis is what I am looking for.

Anyone else know?

I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.|||I'd suggest:SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS data_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid = 255 THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
, Object_Name(si.id)
FROM dbo.sysindexes AS si
GROUP BY si.id-PatP|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.|||sp_spaceused?

EDIT: Found this...

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.Change the 5s to 15s and try again.

It shows some interesting space observations, by table.

-PatP|||pat,

That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.

Is this what is expected? Is the data_mb column the table size?

Thanks for the help, it is greatly appreciated.|||Brett,

Wonderful!!!!!!!!!!

That was it!!!!!!

Thanks a million!!!!!!|||Does my total match the taskpad total?

-PatP

how to setup Integration Services on SQL 2005 cluster

We have SQL Server 2005 cluster setup, active/passive, 2 nodes, on Windows
2003. If I try to add a maintenance task using sql management studio on my
pc I get error: "apply to target server failed for job". If I try it using
management studio while on the active node of the cluster I get error:
"failed to create the task, exception from hresult: 0xC0010014
Microsoft.SqlServer.DTSRuntimeWrap".
I installed Integration Services on both nodes and followed instructions in
document ms345193 to configure IS on the cluster, but I must have something
wrong.
Any ideas what I should look at to get this to work? Thanks!
Denise,
this you follow this http://msdn2.microsoft.com/en-us/library/ms345193.aspx
?
HTH,
_Edwin.
"denise" <denise@.discussions.microsoft.com> wrote in message
news:92A9C846-69CA-4B46-87FE-75E567904FD9@.microsoft.com...
> We have SQL Server 2005 cluster setup, active/passive, 2 nodes, on Windows
> 2003. If I try to add a maintenance task using sql management studio on
my
> pc I get error: "apply to target server failed for job". If I try it
using
> management studio while on the active node of the cluster I get error:
> "failed to create the task, exception from hresult: 0xC0010014
> Microsoft.SqlServer.DTSRuntimeWrap".
> I installed Integration Services on both nodes and followed instructions
in
> document ms345193 to configure IS on the cluster, but I must have
something
> wrong.
> Any ideas what I should look at to get this to work? Thanks!
|||Yes, I was a little unsure of which drives to use as the dependencies (should
all shared drives be there, we have several, I just picked one, E drive), and
the registry set up, in step 9, I just put what is in the document, then in
step 14 I put the shared drive where I copied the MsDtsSrvr.ini.xml file (I
used E drive).
Could someone talk me through what should be set in each step? Thanks!
"Edwin vMierlo" wrote:

> Denise,
> this you follow this http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> ?
> HTH,
> _Edwin.
>
>
> "denise" <denise@.discussions.microsoft.com> wrote in message
> news:92A9C846-69CA-4B46-87FE-75E567904FD9@.microsoft.com...
> my
> using
> in
> something
>
>
|||
> Yes, I was a little unsure of which drives to use as the dependencies
(should
> all shared drives be there, we have several, I just picked one, E drive),
and
If the E drive is the only drive you use for the packages and file, than
that is OK, however if not sure you can add all drives.

> the registry set up, in step 9, I just put what is in the document, then
in
In step 9 you fill in the registry location (not the disk);
"SOFTWARE\Microsoft\MSDTS\ServiceConfigFile"

> step 14 I put the shared drive where I copied the MsDtsSrvr.ini.xml file
(I
> used E drive).
In step 14 you put in the fulname and directory path in the registry,
Example
E:\dir\subdir\MsDtsSrvr.ini.xml

> Could someone talk me through what should be set in each step? Thanks!
HTH,
_Edwin.
|||I have all this set up correctly. Any other ideas of what could be wrong?
Would it be affected by the MSDTS configuration set up on the cluster?
Thanks so much for your help.
"Edwin vMierlo" wrote:

> (should
> and
> If the E drive is the only drive you use for the packages and file, than
> that is OK, however if not sure you can add all drives.
> in
> In step 9 you fill in the registry location (not the disk);
> "SOFTWARE\Microsoft\MSDTS\ServiceConfigFile"
> (I
> In step 14 you put in the fulname and directory path in the registry,
> Example
> E:\dir\subdir\MsDtsSrvr.ini.xml
>
> HTH,
> _Edwin.
>
>

How to set null value for a smalldatetime inside a Script Component task?

how the hell you allocate a null value for a smalldatetime sql field?

Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:

.Parameters("@.FecEnajenacion").Value = "1999-01-01"

error:

.Parameters("@.FecEnajenacion").Value = vbNull

.Parameters("@.FecEnajenacion").Value = Null

.Parameters("@.FecEnajenacion").Value = SqlDbType.?

Try

Parameters("@.FecEnajenacion").Value = Nothing

-Jamie

|||

hi jamie,

Well, it doesn't works because of my code wait any value:

Public Overrides Sub PreExecute()

sqlCmd = New SqlCommand(sSql, sqlConn)

sqlParam = New SqlParameter("@.FecEnajenacion", SqlDbType.SmallDateTime)

sqlCmd.Parameters.Add(sqlParam)

....

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

With Sqlcmd

If dFecha2 = "0000/00/00" Then

.Parameters("@.FecEnajenacion").Value = Nothing

Else

.Parameters("@.FecEnajenacion").Value = dFecha2

End If

.ExecuteNonQuery()

End With

End Sub

Prepared statement '(@.Ejercicio smallint,@.NIFPerc char(9),@.NIFRep char(9),@.Nombre va' expects parameter @.FecEnajenacion, which was not supplied.

|||enric,

the DBNull data type must be used when setting the parameter value property to null.

i recommend that you post this question to the ADO.NET forum for further assistance.|||thank you

How to set douplicate id in sql server 2005?

Hi There,

Some one please help me to achieve this task.

I have task to join 2 tables and insert values.The table which i am inserting values has typeid column which is primary key column.I supposed to insert values except this column(TypeId).When i m trying insert values its throw an error saying

Error: Cannot insert the value NULL into column column does not allow nulls. INSERT fails.

Please let me know ther is a way to set duplicate id for this rows?

Thanks in advance.

Don't select the typeID column in your OLE DB destination.|||I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?|||

Rafael Salas wrote:

I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?

It could either be an identity, or he needs to assign a value to it.|||

Hi ,

Yes i want to assign a value automatically for the TypeId for inserting rows.

|||See if this helps you. Take a look at my post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211015&SiteID=1|||

Hi Phil,

Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?

|||

Tamizhan wrote:

Hi Phil,

Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?

I wrote one just for you... http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/
Also, a more simplified version can be found at SQLIS.com. It does not start where the table left off. (It assumes starting at 1 always). http://www.sqlis.com/37.aspx
|||

Hi Phil,

I have gone thru your articles i got the ideas to do. But i am facing problem while using theis scripts.

i have no.of sql statements i have droped no.of execute sql tasks for each statements.Please find the below script,

select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType

__

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.

' This will set up the counter accordingly

If (NextKey = 0) Then

' Use MaximumKey +1 here because we already have data

' and we need to start with the next available key

NextKey = MaximumKey + 1

Else

' Use NextKey +1 here because we are now relying on

' our counter within this script task.

NextKey = NextKey + 1

End If

Row.TypeId = NextKey ' Assign NextKey to our ClientKey field on our data row

End Sub

End Class

I followe all your steps as mentioned.Please Advice me.

|||Does your query return more than one row? Notice that you aren't selecting the max(typeid) from the table, tblType.

select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType|||

Hi Phil,

Yes you are right.It returns more than one row.Please advice how to achieve this?

Please correct the script if there is a problem.

Many thanks in advance.

|||

Tamizhan wrote:

Hi Phil,

Yes you are right.It returns more than one row.Please advice how to achieve this?

Please correct the script if there is a problem.

Many thanks in advance.

Change your query to select the max(typeid) from the table. Your query isn't the same as mine.

How to Send Mail to a Personal Distribution List (PDL) ?

Hi friends,

Though we can send mails to Individual addresses, I wonder what would be the Syntax to specify in the script task or Format to specify in a "To" Property of Send Mail task that I use to Send a mail to my Personal Distribution List (PDL).

Thanks

Subhash Subramanyam

You cannot do this using the SMTP Mail Task, because SMTP does not have personal lists. That is a feature of your "mail box", and comes with technologies like MAPI and mail servers. You do not want MAPI I assure you, it is horrific to use in a server environment.

Really you need to get a list created at the mail server level, or just send to all people using individual addresses.

|||

Hi Friends,

Surely I accept to the ideas of experts' reply here.

Thanks to dipendra baghel, who found a custom component "nsoftware send email task".

But solution for the Send Mail task to work is right here: When we create a PDL, The exchange server creates a valid address which can route to list of address in PDL.(say, sdfds@. sdd.com) . This address can be fed into "To" property in Send Mail Task

Thanks to my colleagues Sunil Gidwani, Dipendra Baghel and Prashanth Tiwari for their support

Thanks

Subhash Subramanyam

|||

Thanks for your reply, Darren.

I understood here that PDL is a feature of mailbox having MAPI Technology or other Mail Servers.

So I'd rather wish that in the future verison of SSIS, Send mail task should have an option to import addresses from a csv file or address book and should help us allow grouping that help customize broadcasting (Says incase of Newsletters, Alerting the Teams etc)

Thanks

Subhash Subramanyam

|||

As you mentioned in the answer to this post, groups and mailing lists are a function of the email server, not of the task sending the mail. If you want to send to a group of people via the SMTP task, you can already either set up a group email alias on your mail server, or read an external list, and use a script to put a series of email addresses in the To: property on the SMTP task.

|||

Distribution lists can be defined in a personal MAPI "mailbox", in fact it worked for that in the old SQL Mail or DTS Send Mail task, but MAPI is absolute pants in an unattended envrionment., and I cannot stress that enough. You had to install Outlook on your server to get it, and the only version that worked reliably was Outlook 2000. MAPI is owned my the office team, and they have a different focus. They effectively broke MAPI to SMTP delivery in one release, you had to leave Outlook running on the server!

So do not wish for MAPI is my message, you would regret it!

Importing a CSV for sending mail, ma I point out that we have SSIS, see the Flat File source, the recordset desination, and the for each loop. Using those components and tasks you can drive a email list off a CSV file and mail each address. It is not the simplest method, but then SSIS is not supposed to be a bulk mailer. What about featues like unsubscribe and other list management features? Use the right tool for the job I is what I mean. If bulk mail features are too much, then just use the standard mail server itself, they all support distribution lists much better than SSIS or MAPI ever will.