How to FTP

How to automate FTP files to unix server from Windows server ? I dont want t
o
use SQL Server but run a bat file that migth ftp files to unix serverHi
You can use the FTP.exe program which comes with windows. Type in ftp -h at
a command prompt to get the options or look in windows help file.
If you are calling from a stored procedure this can be called using
xp_cmdshell.
John
"Disney" wrote:

> How to automate FTP files to unix server from Windows server ? I dont want
to
> use SQL Server but run a bat file that migth ftp files to unix server

How to format cell according to different data type

Hi,
I have a table cell which could accomodate date,currency,numeric at run
time,how do I set up individual format for each type? I don't know vb
so I'd appreciated for any help.
ThanksGetting the difference between curency and numeric is a challenge, for the
rest you can do a nested iif expression for the format property eg:
=iif(isdate( Fields!Yourfield.Value),"dd MMM
yyyy",isnumeric(Fields!Yourfield.Value),"N","C")
"ottawa111" wrote:
> Hi,
> I have a table cell which could accomodate date,currency,numeric at run
> time,how do I set up individual format for each type? I don't know vb
> so I'd appreciated for any help.
> Thanks
>|||Thanks a lot

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence
> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/pro...e14.mspx#EDAA.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
]
Dejan Sarka, SQL Server MVP
Associate Mentor
[url]www.SolidQualityLearning.com" target="_blank">http://www.microsoft.com/technet/pr...ityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside14.mspx#EDAA.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to fix corrupt database?

Hello,

I am trying to run the example program: Coding4Fun: Building a Family History Web Service at this URL

http://msdn.microsoft.com/coding4fun/xmlforfun/familyhistory/default.aspx

The project has an SQL Express database Family.mdf. When I try and open Family.mdf in VS 2005, I get this error:

'FAMILY.MDF' cannot be upgraded because its non-release version (587) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

I can create a new database, but then the stored procedures will be lost.

Any suggestions how to repair the database?

Thanks

That database was based on a beta build. Beta build were not supposed to be supported after the RTM version is launched. í dont′think that there is a upgrade path for this internal version to the RTM version. If you still have a beta SQL Server 2005 (Yukon) you can try to attach the db to that one and script the structure out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hello Jens,

Thanks for taking time to reply to this question. I dont have a beta version of SQL 2005, so I guess I will have to just create a new database.

Thank you,

Tom

How to find unused Objects?

Is there a way in SQL I can tell when the last time a stored procedure was run or a table was accessed? I know there are a lot of objects in my system that are probably no longer being used but what is the best way to go about identifing these objects?

Gk

That is not going to be a 'simple' process. There is nothing inherrent in SQL Server that will give you that information with certainty.

You could set up a Profiler trace to capture the procedure/function/view/table name over time.

There are some Third party products that will follow the dependency trees, and also track usage. Check Tibor's list.

The process I follow is this:

When I identify an object that is a removal candidate:

All Stored Procedures/Functions/views/Tables that have been created in a database for solely administrative purposes are, in SQL 2000 named [dbs_, dbf_, dbv_, db_] and in SQL 2005, added to the [Admin] schema. I have a 'home-grown' .NET tool that will cycle through the source control store, examining application code, compiling a list of command.text statements, Since my practice is to require the use of stored procedures for all data access, the command.text is a list of Stored Procedures. I then check object dependencies against the list. I now have my 'first pass' candidate list. I first rename the candidate object (add 'x' to the beginning and they all sort to the bottom of the list). If something then 'breaks', it is very quick to change the name back and restore functionality. I leave the renamed objects for 3-6 months before final deletion. (Some things may be rarely used, but they may be for an 'important' process -such as reporting.) If it is obvious that the object is for a reporting process, I may leave it for a year -there are annual reports... When an object is finally removed, I script it out and store the script in an object archive. (Who knows, perhaps that object had some 'nice' code in it that I can refer to later.)sql

How to find the in Progress job in MSDB database

Hello,
I am try to find which job is in progress.
Then I start a job A. run a statement like:
select * from sysjobhistory where job_id = 'A' and
run_status = 4
it return nothing, even I know the job it is running.
Can anybody tell me how to get information about those job
in progress from MSDB database?
Thanks in advance...Don't feel bad about not finding it. It isn't in the database. It is
actually in the shared memory between SQL Agent and SQL Server. I don't
know of any way to access it either.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Harry G" <anonymous@.discussions.microsoft.com> wrote in message
news:04ad01c3d6f7$aa1624f0$a101280a@.phx.gbl...
> Hello,
> I am try to find which job is in progress.
> Then I start a job A. run a statement like:
> select * from sysjobhistory where job_id = 'A' and
> run_status = 4
> it return nothing, even I know the job it is running.
> Can anybody tell me how to get information about those job
> in progress from MSDB database?
> Thanks in advance...|||Hi,
Please execute the procedure
exec msdb..sp_help_job @.job_id = 0x3F2224EAAFD7A9418A4643AF5C020379,
@.job_aspect = N'job'
(replcae the jobid with your job id)
current_execution_status =1 then Job executing
current_execution_status =4 then not Running
Thanks
Hari
MCDBA
"Harry G" <anonymous@.discussions.microsoft.com> wrote in message
news:04ad01c3d6f7$aa1624f0$a101280a@.phx.gbl...
> Hello,
> I am try to find which job is in progress.
> Then I start a job A. run a statement like:
> select * from sysjobhistory where job_id = 'A' and
> run_status = 4
> it return nothing, even I know the job it is running.
> Can anybody tell me how to get information about those job
> in progress from MSDB database?
> Thanks in advance...

How to find the in Progress job in MSDB database

Hello,
I am try to find which job is in progress.
Then I start a job A. run a statement like:
select * from sysjobhistory where job_id = 'A' and
run_status = 4
it return nothing, even I know the job it is running.
Can anybody tell me how to get information about those job
in progress from MSDB database?
Thanks in advance...Don't feel bad about not finding it. It isn't in the database. It is
actually in the shared memory between SQL Agent and SQL Server. I don't
know of any way to access it either.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Harry G" <anonymous@.discussions.microsoft.com> wrote in message
news:04ad01c3d6f7$aa1624f0$a101280a@.phx.gbl...

quote:

---

> Hello,
> I am try to find which job is in progress.
> Then I start a job A. run a statement like:
> select * from sysjobhistory where job_id = 'A' and
> run_status = 4
> it return nothing, even I know the job it is running.
> Can anybody tell me how to get information about those job
> in progress from MSDB database?
> Thanks in advance...

---

|||Hi,
Please execute the procedure
exec msdb..sp_help_job @.job_id = 0x3F2224EAAFD7A9418A4643AF5C020379,
@.job_aspect = N'job'
(replcae the jobid with your job id)
current_execution_status =1 then Job executing
current_execution_status =4 then not Running
Thanks
Hari
MCDBA
"Harry G" <anonymous@.discussions.microsoft.com> wrote in message
news:04ad01c3d6f7$aa1624f0$a101280a@.phx.gbl...

quote:

---

> Hello,
> I am try to find which job is in progress.
> Then I start a job A. run a statement like:
> select * from sysjobhistory where job_id = 'A' and
> run_status = 4
> it return nothing, even I know the job it is running.
> Can anybody tell me how to get information about those job
> in progress from MSDB database?
> Thanks in advance...

---

How to find out the job/report that is currently running?

How do I find out the report that is currently being run? I have a scenario where some users run a report without knowing the amount of data that will be pulled by it... In such a case, it bogs down the server and doesn't let me access the Report Manager application.

How can I find out what is the report that is currently being run? I know I can get that information from the Show Jobs link fromm Site Settings in Report Manager. But in this case, I am not even able to login to the Report Manager. The memory (8 gigs) in the app server is fully maxed out and it doesn't log me in to the Report Manger.

I ran a query against the ExecutionLog table in the ReportServer database that logs all the executions. But it looks like the data to this table gets logged only after the report execution is completed. There is no way to tell what is being executed right now. I also checked the table RunningJobs in the ReportServer database, but there are no records in that table.

Can someone help?

Thanks.

can someone help?|||

I am unable to find information on the web related to this. Can someone help?

Thanks for your response.

|||In the ReportServer database there is a RunningJobs table which hold this data.|||I have mentioned in my post that this table did not contain any records when I checked. Any possible scenarios/cases as to when such a thing could occur?

How to find out the job/report that is currently running?

How do I find out the report that is currently being run? I have a scenario where some users run a report without knowing the amount of data that will be pulled by it... In such a case, it bogs down the server and doesn't let me access the Report Manager application.

How can I find out what is the report that is currently being run? I know I can get that information from the Show Jobs link fromm Site Settings in Report Manager. But in this case, I am not even able to login to the Report Manager. The memory (8 gigs) in the app server is fully maxed out and it doesn't log me in to the Report Manger.

I ran a query against the ExecutionLog table in the ReportServer database that logs all the executions. But it looks like the data to this table gets logged only after the report execution is completed. There is no way to tell what is being executed right now. I also checked the table RunningJobs in the ReportServer database, but there are no records in that table.

Can someone help?

Thanks.

can someone help?|||

I am unable to find information on the web related to this. Can someone help?

Thanks for your response.

|||In the ReportServer database there is a RunningJobs table which hold this data.|||I have mentioned in my post that this table did not contain any records when I checked. Any possible scenarios/cases as to when such a thing could occur?

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
Igor
Hi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>
|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
IgorHi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
IgorHi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far :) Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far :) Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I was wondering if there is a way to find out restore progress of the
>> job. If I run restore in QA, I can use STATS option to control restore
>> progress statistics but this information is not accessible when restore
>> executed as a part of a job.
>>
>> Thanks,
>> Igor
>

how to find invalid objects --After DDL changes

Hi

I have a SQL Server 2005 database running. When I run some ddl changes, I want to find all the procs/objects that get invalidated because of object not found error.....

Is there any way that I can look up in sysdepends or other tables to find information about this.

Regards

Imtiaz

You can query the system view sys.syscomments for any procedures or functions that reference the objects. The text of each is stored in sys.syscomments.text.

How to find database table and their field name run time.

I am using sql server as back end. Through connection stringI am getting database name. But in A dropdown I want to show list of table inthat database. And in another B drop down I want to show fields of tableselected in A dropdown. can any one help in getting the field .

The sp_Tables query will return the tables in a database andsp_Columns returns the column names

http://www.vb-tips.com/dbpages.aspx?ID=9e2d9af4-3909-421d-b422-87c1d376674d

|||

And if you want to dig deeper into the structure of the database or server, you can use SQL-DMO in SQL Server 2000 or SMO (SQL Management Objects) in SQL Server 2005.

Don