Count

I am trying to count entries into each column heading by zip code, but when I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Put the DISTINCT keyword inside the COUNT():
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jay via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efd1fc265539@.uwe...
I am trying to count entries into each column heading by zip code, but when
I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||I'm guessing you might need to COUNT the distinct values for each
column. If that is the case:
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
etc.
If that is not what you need please elaborate on what you actually
hope to see.
Note that since you were already doing a GROUP BY there was no purpose
to having a DISTINCT for the entire query.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 15:49:59 GMT, "Jay via SQLMonster.com" <u7124@.uwe>
wrote:
>I am trying to count entries into each column heading by zip code, but when I
>run the script below I get the same count across all columns. Can someone
>tell/show me how to make these unquie counts?
>Thanks in advance.
>SELECT DISTINCT P.ZIP,
> COUNT (evC.day_care_center) AS 'Day Care',
> COUNT (evC.drop_care) AS 'Drop-In Care',
> COUNT (evC.family_day_care) AS 'Family day care',
> COUNT (evC.financial) AS 'Financial',
> COUNT (evC.montessori_program2)AS 'Montessori program',
> COUNT (evC.nanny_service) AS 'Nanny service',
> COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
> COUNT (evC.parenting_info) AS 'Parenting information',
> COUNT (evC.sick_child_care) AS 'Sick child care',
> COUNT (evC.special_needs)AS 'Special needs',
> COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> COUNT (evC.temporary) AS 'Temporary',
> COUNT (evC.transportation2)AS 'Transportation',
> COUNT (evC.support_groups) AS 'Support groups',
> COUNT (evC.other) AS 'Other'
>FROM Patient_Elg pe
> INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
> INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
>WHERE (pe.Payor_Key = 59)
> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>GROUP BY P.Zip|||I made the changes you suggested and it did make the counts unique but they
are not correct by column heading/category. To check I displayed all the
information for zip code 55901 under the column 'Day Care' for '07. I came up
with a count of 200, but for this script I only get 11. How can I get all
records to count?
Thanks again for your help in advance
Roy Harvey (SQL Server MVP) wrote:
>I'm guessing you might need to COUNT the distinct values for each
>column. If that is the case:
>SELECT P.ZIP,
> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>etc.
>If that is not what you need please elaborate on what you actually
>hope to see.
>Note that since you were already doing a GROUP BY there was no purpose
>to having a DISTINCT for the entire query.
>Roy Harvey
>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via http://www.sqlmonster.com|||Usually the fastest way to get answers to questions like this is to give us
the SQL statements to create sample tables and load those tables with sample
data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
you would want to see from that sample data. That keeps us from having to
guess what you want, and you will get an answer that has been tested against
your sample data.
Tom
"jpettigrew via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efdb43406fee@.uwe...
>I made the changes you suggested and it did make the counts unique but they
> are not correct by column heading/category. To check I displayed all the
> information for zip code 55901 under the column 'Day Care' for '07. I came
> up
> with a count of 200, but for this script I only get 11. How can I get all
> records to count?
> Thanks again for your help in advance
> Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but
>>when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
> --
> Message posted via http://www.sqlmonster.com
>|||What Tom Cooper said. SHOW us what you have, and what you want.
Your original query counted all the rows for the zip code. All the
columns were the same because COUNT(columnname) returns a count of all
the rows with non-null values for that column, and apparently every
row was non-null. If there are 200 rows for a zip code, why would you
expect any number except 200 for day_care_center or drop_care?
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 16:56:33 GMT, "jpettigrew via SQLMonster.com"
<u7124@.uwe> wrote:
>I made the changes you suggested and it did make the counts unique but they
>are not correct by column heading/category. To check I displayed all the
>information for zip code 55901 under the column 'Day Care' for '07. I came up
>with a count of 200, but for this script I only get 11. How can I get all
>records to count?
>Thanks again for your help in advance
>Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip|||Because of HIPAA compliance it is difficult to provide information contained
in one of the tables. How do you suggest I provide the information? If i was
not specific in my previous posts I apologize I am trying to total the
columns in the script by zip code, but do not seem to be getting all the
information.
Tom Cooper wrote:
>Usually the fastest way to get answers to questions like this is to give us
>the SQL statements to create sample tables and load those tables with sample
>data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
>you would want to see from that sample data. That keeps us from having to
>guess what you want, and you will get an answer that has been tested against
>your sample data.
>Tom
>>I made the changes you suggested and it did make the counts unique but they
>> are not correct by column heading/category. To check I displayed all the
>[quoted text clipped - 28 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||On Wed, 30 Jan 2008 20:11:22 GMT, Jay via SQLMonster.com wrote:
>Because of HIPAA compliance it is difficult to provide information contained
>in one of the tables. How do you suggest I provide the information?
Hi Jay,
* Post the table structure as CREATE TABLE statements, including all
constraints, properties, and indexes.
* Post the data as INSERT statements. It doesn't need to be the real
data, a made-up sample that illustrates the problem is just as well
(probably even better, as there is no need to posts thousands or even
millions of rows when you can illustrate the problem with ten). I don't
think HIPAA disallows the posting of made-up data.
* Post the expected results.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jan 30, 9:49=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> I am trying to count entries into each column heading by zip code, but whe=n I
> run the script below I get the same count across all columns. Can someone
> tell/show me how to make these unquie counts?
> Thanks in advance.
> SELECT =A0DISTINCT P.ZIP,
> =A0 =A0 =A0 =A0 COUNT (evC.day_care_center) AS 'Day Care',
> =A0 =A0 =A0 =A0 COUNT (evC.drop_care) AS 'Drop-In Care',
> =A0 =A0 =A0 =A0 COUNT (evC.family_day_care) AS 'Family day care',
> =A0 =A0 =A0 =A0 COUNT (evC.financial) AS 'Financial',
> =A0 =A0 =A0 =A0 COUNT (evC.montessori_program2)AS 'Montessori program',
> =A0 =A0 =A0 =A0 COUNT (evC.nanny_service) AS 'Nanny service',
> =A0 =A0 =A0 =A0 COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn= centers',
> =A0 =A0 =A0 =A0 COUNT (evC.parenting_info) AS 'Parenting information',
> =A0 =A0 =A0 =A0 COUNT (evC.sick_child_care) AS 'Sick child care',
> =A0 =A0 =A0 =A0 COUNT (evC.special_needs)AS 'Special needs',
> =A0 =A0 =A0 =A0 COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> =A0 =A0 =A0 =A0 COUNT (evC.temporary) AS 'Temporary',
> =A0 =A0 =A0 =A0 COUNT (evC.transportation2)AS 'Transportation',
> =A0 =A0 =A0 =A0 COUNT (evC.support_groups) AS 'Support groups',
> =A0 =A0 =A0 =A0 COUNT (evC.other) AS 'Other'
> FROM =A0 =A0Patient_Elg pe
> =A0 =A0 =A0 =A0 INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
> =A0 =A0 =A0 =A0 INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D ev=C.Patient_Key
> WHERE =A0 =A0 (pe.Payor_Key =3D 59)
> =A0 =A0 =A0 =A0 AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
> GROUP BY P.Zip
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server/200801/1
Hi Jay,
I think you need to do each count(distinct field) separately and store
them (maybe into a variable). For example:
Declare @.zip int, @.center int, etc
Select @.zip =3D select count(distinct p.zip)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
Select @.center int =3D select count(distinct evC.day_care_center)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
etc.
HTH|||On Wed, 30 Jan 2008 20:40:12 -0800 (PST), SB <othellomy@.yahoo.com>
wrote:
>I think you need to do each count(distinct field) separately and store
>them (maybe into a variable).
All at once or individually, if they are always grouped by the same
thing there will be no difference.
Roy Harvey
Beacon Falls, CT

Could this be run using OSQL script

If there a way to detect if backup fails? I like to add better error
handling... I need to be able to run using a script not thru the Sql
Scheduler.
DECLARE @.name VARCHAR(50) -- database name
DECLARE @.path VARCHAR(256) -- path for backup files
DECLARE @.fileName VARCHAR(256) -- filename for backup
DECLARE @.fileDate VARCHAR(20) -- used for file name
SET @.path = 'C:\Backup\'
SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
BACKUP DATABASE @.name TO DISK = @.fileName
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursorHi
You could test @.@.ERROR after performing the backup to see if the backup
command worked and store a list of failures, but some errors will abort the
batch and therefore subsequent databases will not be backed up. SQL2005 has
better error handling. The script can be run using OSQL with the -i flag if
you save the script to a file. The -b flag reports as a command line error
any error value so it could be trapped using ERRORLEVEL it will be 1 if one
of the backups failed. You may also want to use the -n flag to suppress line
numbers. If using SQL Agent you do not need to use a command prompt.
Your backups will append to any files that are already present with the
given name you may want to specify the INIT command if you want to overwrite
these.
John
"AHartman" wrote:
> If there a way to detect if backup fails? I like to add better error
> handling... I need to be able to run using a script not thru the Sql
> Scheduler.
> DECLARE @.name VARCHAR(50) -- database name
> DECLARE @.path VARCHAR(256) -- path for backup files
> DECLARE @.fileName VARCHAR(256) -- filename for backup
> DECLARE @.fileDate VARCHAR(20) -- used for file name
> SET @.path = 'C:\Backup\'
> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE name NOT IN ('master','model','msdb','tempdb')
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
> BACKUP DATABASE @.name TO DISK = @.fileName
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||Thanks... for the info
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:45510EB6-452B-4597-8775-99F7FC501054@.microsoft.com...
> Hi
> You could test @.@.ERROR after performing the backup to see if the backup
> command worked and store a list of failures, but some errors will abort
> the
> batch and therefore subsequent databases will not be backed up. SQL2005
> has
> better error handling. The script can be run using OSQL with the -i flag
> if
> you save the script to a file. The -b flag reports as a command line error
> any error value so it could be trapped using ERRORLEVEL it will be 1 if
> one
> of the backups failed. You may also want to use the -n flag to suppress
> line
> numbers. If using SQL Agent you do not need to use a command prompt.
> Your backups will append to any files that are already present with the
> given name you may want to specify the INIT command if you want to
> overwrite
> these.
> John
> "AHartman" wrote:
>> If there a way to detect if backup fails? I like to add better error
>> handling... I need to be able to run using a script not thru the Sql
>> Scheduler.
>> DECLARE @.name VARCHAR(50) -- database name
>> DECLARE @.path VARCHAR(256) -- path for backup files
>> DECLARE @.fileName VARCHAR(256) -- filename for backup
>> DECLARE @.fileDate VARCHAR(20) -- used for file name
>> SET @.path = 'C:\Backup\'
>> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
>> DECLARE db_cursor CURSOR FOR
>> SELECT name
>> FROM master.dbo.sysdatabases
>> WHERE name NOT IN ('master','model','msdb','tempdb')
>> OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @.name
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
>> BACKUP DATABASE @.name TO DISK = @.fileName
>> FETCH NEXT FROM db_cursor INTO @.name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>>sql

Could Script Task Component uses a Framework 1.1 assembly?

Could Script Task Component uses a Framework 1.1 assembly?

Yes, it can - we did some testing. But you have to copy it to SYSTEM\assembly and register it in GAC (gacutil.exe).

HTH

could not find table error message

I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
Dan D.
Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.
|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
Dan D.
"Tibor Karaszi" wrote:

> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>

could not find table error message

I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct th
e
problem and get things back in sync?
Thanks,
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I g
et
> an error message that says "Could not find a table or object named 'tblDNS
'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct
the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:

> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>

could not find table error message

I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
--
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
> >I run a script that tells me what tables need to be defragged. This query
> > gets a list of the tables:
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > and then this query checks to see how fragmented the table is:
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> >
> > The first query is finding some tables that when the second query runs I get
> > an error message that says "Could not find a table or object named 'tblDNS'.
> > Check sysobjects.
> >
> > Why would the first query find a table but the second query wouldn't find
> > the table. They must be looking in two different places. How do I correct the
> > problem and get things back in sync?
> >
> > Thanks,
> >
> >
> >
> > --
> > Dan D.
>

Could I Resolve a KPI's Data Value in a SSIS Script Task?

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

ivanchain wrote:

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

Yep. If its .Net then you can use it in a Script Task!

-Jamie

|||

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

|||

ivanchain wrote:

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

Aha. Yes, you're absolutely right. I should have realised this before - sorry.

Read this:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||Thanks, Jamie. You saved my life.

Corruption of backup file on remote server

Dear all,

What I want to do is to backup my database through the SQL script, when the backup is successful, the .bak file is saved in my local harddisk. After that, I used the XYRunProc to execute a "copy" command to copy the .bak file to a remote server.

However, when I want to restore the database using the .bak on the remote server, I found that the .bak is corrupted. This problem does not exist if the file is copied to the remote server manually (click copy on local server and click paste on remote server).

Do anyone know what's the problem of this? This really made me very frustrated. Hope someone can help me, thank you very much!

Regards,
Strike

Hi Strike,

XYRunProc is an extended stored procedure written by someone outside of Microsoft. I couldn't even begin to tell you how it works or why it might be causing problems when copying files. Since a manual copy doesn't seem to affect the .bak file, it's clearly something about how XYRunProc is handling it.

I would suggest you contact the author of the xProc to determine what might be happening during the file copy.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!