How to scheduling Report

Hi Friends

How to generate the Report every month automatically.with out endusers explicitly .

Regards

Raju

Hello Raju,

Take a look at Subscriptions in Books Online.

Hope this helps.

Jarret

How to run a report from webform ........URGENT

Hi,

I am using SSRS 2000 and VS2003.I am new to SSRS.I have to generate a report on button click event.I have to pass parameters(values from textbox,dropdown) to the report.I am a little confused like how to call report from webpage and how to pass web page control values as parameters.Please guide me.Thank you.

hi use reportviewer control. Its easy. For more details see abt this control in MSDN

|||

Hi,

I serached in MSDN.I didn't get how to get REPORTVIEWER control in webform.I don't see any built in reportviewer in VS2003.Can you please guide me how to add that control to my webpage (TOOLBOX).Thank you

|||

hi sure,

But I have office only on Monday. So will let u know then... my id issivaatzenith@.gmail.com. Pls ping to this id... I will let u know

|||

Hi,

Sure I will mail you.I have few more doubts.Thank you.

|||

hi hope this link will solve ur problem

http://www.codeproject.com/sqlrs/SQLRSViewer.asp

sql

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableName
If you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>
|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they
>
|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>
|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they[vbcol=seagreen]
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> > Using 2005
> >
> > I want to generate a list of all tables that have indexes, so I can
> > reindex
> > them. I don't want to bother doing a reindex on a table that has no
> > indexes. I thought it would be easy enough to pull this data from
> > sys.indexes (or something like that), but it is quirky. The following
> > query
> > will return some tables, showing them as having 1 index, when really
they
> > have NO indexes. But yet other tables result with having 1 index, when
> > that
> > is correct. And this query also returns dtproperties, which is not one
> > of
> > my user tables, so that also makes this query incorrect.
> >
> > This is what I'm using, and maybe someone can help fine tune it so it is
> > 100% accurate... Try this against any of your DBs and you'll see it is
> > not
> > quite accurate even though mostly accurate...
> >
> > Select object_name(object_id) as TableName, count(object_id) as
> > NumIndexes,
> > object_id
> > from sys.indexes
> > where object_id>1000 -- eliminate some system table junk below 1000
> > group by object_id
> > order by TableName
> >
> >
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>

How to retrieve the name of the columns?

Hi everyone,

On daily basis I need to generate excerpts by mean of Excel. Prior to sql25k I used to play with Enterprise Manager and pick up the name of all the columns for a table very easily doing this

select top 1 f1,f2,-.. from table

nameage

enric80

How do I such thing from Sql Management Studio?

It's a silly thing, I know, but it's very useful for me because when I've got those columns then I can do paste them perfectly into .XLS.

Otherwise I see forced to write one by one and sometimes tables have more than 60 columns

It's not useful generate a CREATE TABLE script or launch SP_HELP <MYTABLE> because I obtain the name of the columns in vertical no horizontal.

Thanks a lot!!!

Hi Enric

How about this:

EXEC sp_columns @.table_name = 'customers'

Give it a try

Rgds,

Worf

|||select * from information_schema.columns
where table_name = 'customers'|||

Hi guys,

It doesn't useful at all for me. I'm looking for a way which allow me get the columns in horizontal way, just for copy them:

f1 f2 f3 f4

TIA

|||

I gotcha..you can do the same thing in Managment Studio. Just create a new query, and type in:

select top 1 * from Table

that will give you the fields that you want.

|||

hi there,

Ok, setting "results as text" in results panel.

Thanks

|||

Declare @.columns nvarchar(max)

Select @.columns = IsNull(@.columns + ', ', '') + column_name

From information_schema.columns

Where table_name = 'customers'

Select @.columns

-- or

Print @.columns