AUTOGROW info recorded: March 2012

Friday, March 30, 2012

Is it possible to recover from failed sp4 installation?

Hi All,
I have a client who last night attempted to install sql 2000 Enterprise
Edition SP4. When running the first time through it got past the Password
Validation and moved to the Analysing and Preparing stage of the
installation. At this point it completely hung - after an hour there was
still no activity.
Now when attempting to re-install SP3a it states that a later version is
already installed and when attempting to reinstall SP4 it hangs on password
validation everytime.
This is a production server, does anyone know if it is possible to recover
from the above scenario?
Thanks
LarryJosh
I have been istalling SP4 mamy times and have never got the situation. Hmmm,
try tirn off (from the network) the server and re-install SP4
"Josh Lawrence" <someone@.microsoft.com> wrote in message
news:%23DgeHurtGHA.4544@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I have a client who last night attempted to install sql 2000 Enterprise
> Edition SP4. When running the first time through it got past the Password
> Validation and moved to the Analysing and Preparing stage of the
> installation. At this point it completely hung - after an hour there was
> still no activity.
> Now when attempting to re-install SP3a it states that a later version is
> already installed and when attempting to reinstall SP4 it hangs on
> password validation everytime.
> This is a production server, does anyone know if it is possible to recover
> from the above scenario?
> Thanks
> Larry
>|||I have experienced this issue previously but can't recall the solution. I
have the awful feeling it may require a complete reinstallation of SQL2000
from the ground up.
Thanks
Josh
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OryZZ2rtGHA.4752@.TK2MSFTNGP02.phx.gbl...
> Josh
> I have been istalling SP4 mamy times and have never got the situation.
> Hmmm, try tirn off (from the network) the server and re-install SP4
>
>
> "Josh Lawrence" <someone@.microsoft.com> wrote in message
> news:%23DgeHurtGHA.4544@.TK2MSFTNGP04.phx.gbl...
>> Hi All,
>> I have a client who last night attempted to install sql 2000 Enterprise
>> Edition SP4. When running the first time through it got past the
>> Password Validation and moved to the Analysing and Preparing stage of the
>> installation. At this point it completely hung - after an hour there was
>> still no activity.
>> Now when attempting to re-install SP3a it states that a later version is
>> already installed and when attempting to reinstall SP4 it hangs on
>> password validation everytime.
>> This is a production server, does anyone know if it is possible to
>> recover from the above scenario?
>> Thanks
>> Larry
>|||Hi --
I have run into the exact same issue, where SP4 installation hangs on
password validation. It worked for me on number of other servers but it
doesn't work on this server.
I have tried using Windows and SQL Server authentication, sa without
password but it didn't make any difference.
Did anyone find a solution?
The funny thing is that @.@.version shows SP4, however it is Not SP4.
Thanks
Gurpreet
"Josh Lawrence" wrote:
> I have experienced this issue previously but can't recall the solution. I
> have the awful feeling it may require a complete reinstallation of SQL2000
> from the ground up.
> Thanks
> Josh
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OryZZ2rtGHA.4752@.TK2MSFTNGP02.phx.gbl...
> > Josh
> > I have been istalling SP4 mamy times and have never got the situation.
> > Hmmm, try tirn off (from the network) the server and re-install SP4
> >
> >
> >
> >
> >
> > "Josh Lawrence" <someone@.microsoft.com> wrote in message
> > news:%23DgeHurtGHA.4544@.TK2MSFTNGP04.phx.gbl...
> >> Hi All,
> >> I have a client who last night attempted to install sql 2000 Enterprise
> >> Edition SP4. When running the first time through it got past the
> >> Password Validation and moved to the Analysing and Preparing stage of the
> >> installation. At this point it completely hung - after an hour there was
> >> still no activity.
> >>
> >> Now when attempting to re-install SP3a it states that a later version is
> >> already installed and when attempting to reinstall SP4 it hangs on
> >> password validation everytime.
> >>
> >> This is a production server, does anyone know if it is possible to
> >> recover from the above scenario?
> >>
> >> Thanks
> >> Larry
> >>
> >
> >
>
>sql

Is it possible to recover a dropped table?

Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.With no backups you have to hope the data is recoverable from the transaction logs with a tool likewww.lumigent.com offers, but even this does not offer 100% certainty of recovering the table.

is it possible to recieve result of SQL stored procedure to web page?

I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.


for (int i=0; i<dataset.Tables.Count; i++) {
 Response.Write(dataset.Tables[i].TableName);
 Response.Write("\n");
 // loop rows
 for (int j=0; j<dataset.Tables[i].Rows.Count; j++) {
 // loop columns
 for (int k=0; k<dataset.Tables[i].Columns.Count; k++) {
 Response.Write(dataset.Tables[i].Rows[j][k].ToString());
 if (k < dataset.Tables[i].Columns.Count - 1) {
 Response.Write(", ");
 }
 }
 Response.Write("\n");
 }
 Response.Write("\n");
}

this isn't exactly what you want, but idea.|||If the procedure returns a fixed number of result sets, you can return the results a datareader and then bind each result set to something like a datagrid or a repeater using .nextresult between bindings. A datareader can hold more than one result set. It can be pretty handy.

Is it possible to recieve a notification when a subscription fails?

Is it possible for me to receive an email notification from SSRS if one of my report data-driven subscriptions fail?

Status of this memo
This document is an Internet-Draft. Internet-Drafts are working documents of the Internet Engineering Task Force (IETF), its areas, and its working groups. Note that other groups may also distribute working documents as Internet-Drafts.

Internet-Drafts are draft documents valid for a maximum of six months and may be updated, replaced, or made obsolete by other documents at any time.It is inappropriate to use Internet-Drafts as reference material or to cite them other than as "work in progress".

To learn the current status of any Internet-Draft, please check the "1id-abstracts.txt" listing contained in the Internet-Drafts Shadow Directories on ftp.is.co.za (Africa), nic.nordu.net (Europe), munnari.oz.au (Pacific Rim), ftp.ietf.org (US East Coast), or ftp.isi.edu (US West Coast).

Distribution of this document is unlimited. Please send comments to theHTTP working group at . Discussions of theworking group are archived at .

Abstract
This specification defines an HTTP notification architecture that transmits notifications between HTTP resources. An HTTP resource could be any object which might need to send or receive a notification, for example a distribution list, buddy list, print job, etc.

is it possible to re-build index without logging?

nt5, sql2k
i have a vendor application on one of my sql servers.
this application has many tables, and each tables have many indexes. it's
very write intensive (because when a record is inserted, it's insereted to
many many other tables, and that behavior can't be changed). the problem is
page split happened often, but when i tried to reindex tables once a week,
the log file run out of space.
can anyone offers some good solutions to my problem?
thank you!!
Steve
You can try changing the recovery mode to Bulk Logged or Simple and see if
that helps. Do only a few tables at a time and backup the log in between.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
> nt5, sql2k
>
> i have a vendor application on one of my sql servers.
> this application has many tables, and each tables have many indexes. it's
> very write intensive (because when a record is inserted, it's insereted to
> many many other tables, and that behavior can't be changed). the problem
is
> page split happened often, but when i tried to reindex tables once a week,
> the log file run out of space.
>
> can anyone offers some good solutions to my problem?
> thank you!!
>
> Steve
>
|||as i said, the database is write intensive. so simple recovery mode might be
too risky. is there a way to run a scheduled re-index job as a non-logged
operation?
thank you so much for the help.
steve
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> You can try changing the recovery mode to Bulk Logged or Simple and see if
> that helps. Do only a few tables at a time and backup the log in between.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
it's
to
> is
week,
>
|||Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@.TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
> thank you so much for the help.
> steve
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
if
between.
> it's
insereted
> to
problem
> week,
>

is it possible to re-build index without logging?

nt5, sql2k
i have a vendor application on one of my sql servers.
this application has many tables, and each tables have many indexes. it's
very write intensive (because when a record is inserted, it's insereted to
many many other tables, and that behavior can't be changed). the problem is
page split happened often, but when i tried to reindex tables once a week,
the log file run out of space.
can anyone offers some good solutions to my problem?
thank you!!
SteveYou can try changing the recovery mode to Bulk Logged or Simple and see if
that helps. Do only a few tables at a time and backup the log in between.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
> nt5, sql2k
>
> i have a vendor application on one of my sql servers.
> this application has many tables, and each tables have many indexes. it's
> very write intensive (because when a record is inserted, it's insereted to
> many many other tables, and that behavior can't be changed). the problem
is
> page split happened often, but when i tried to reindex tables once a week,
> the log file run out of space.
>
> can anyone offers some good solutions to my problem?
> thank you!!
>
> Steve
>|||as i said, the database is write intensive. so simple recovery mode might be
too risky. is there a way to run a scheduled re-index job as a non-logged
operation?
thank you so much for the help.
steve
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> You can try changing the recovery mode to Bulk Logged or Simple and see if
> that helps. Do only a few tables at a time and backup the log in between.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:#otAPXEGEHA.3288@.TK2MSFTNGP12.phx.gbl...
it's
to
> is
week,
>|||Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@.TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
> thank you so much for the help.
> steve
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@.TK2MSFTNGP09.phx.gbl...
if
between.
> it's
insereted
> to
problem
> week,
>sql

Is it possible to read/write a file at privilege?

hello.
I saw some systems which were hacked by sql injection tool
And some files of the systems were changed. I guess the tool tried to
read/write files.
howerver, the user privilege is not 'sa'. Is it possible for user who
is not 'sa' to read/write files?
If it is possible, how can I prevent the tools from reading/writing
files even if my web page is injectable?dodol (Dolka1@.gmail.com) writes:
> I saw some systems which were hacked by sql injection tool
> And some files of the systems were changed. I guess the tool tried to
> read/write files.
> howerver, the user privilege is not 'sa'. Is it possible for user who
> is not 'sa' to read/write files?
It could be another user with sysadmin rights. Or execution rights might
have been granted on xp_cmdshell or sp_OAxxx.

> If it is possible, how can I prevent the tools from reading/writing
> files even if my web page is injectable?
Make sure that xp_cmdshell and the sp_OAxxx procedures are disabled.
Make sure that SQL Server runs on a domain account that has no extra
privileges. The less welcome it is in the rest of the network the better.
But the main line of defence is of course to use stored procedure or
parameterised statements and never interpolate incoming stuff into
query strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

|||

Definately worth a look.

-Jamie

|||

Jamie/Darren,

-dhideal

Is it possible to read data from a table with exclusive lock ?

Hello,

We currently use SQL Serv 2000 with an ERP application and VB applications.

Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.

What can I do ?

thank you

Nico

Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.

Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.

Your next option is to add a transaction to the process and set the isolation level of the transaction to "readuncommited". This will allow you to 'by pass' the exclusive lock, but the RISK is that you may get obsolete (incorrect) data.

|||

Hello,

Just to confirm you it's seems to work and this a great new for our users :)

Thank you very much for you help

regards

Nico

|||

hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo .

Seriously, glad to help, would you please remember to mark a question as "answered" once you are satisfied. It will help others find answers, and allow answerers to move on to other unanswered questions.

Is it possible to read data from a table with exclusive lock ?

Hello,

We currently use SQL Serv 2000 with an ERP application and VB applications.

What can I do ?

thank you

Nico

Is the ERP a third party app? If so, since it is the one doing the locking, you will need to talk to that vendor and see if they can/will do a more permissive lock.

Since that is 'unlikely to happen' (vendors are funny that way) You might increase the timeout on your connection object to see if waiting a few more seconds will allow the lock to clear.

|||

Hello,

Just to confirm you it's seems to work and this a great new for our users :)

Thank you very much for you help

regards

Nico

|||

hmmm.. "Nico" & "Polleveys".. sounds like I should open up answers for ouzo .

sql

Is it possible to read a transation log?

Is it possible to read a transaction log or can they only be used for
restoration purposes?
thanks in advance
Russ White
rwhite@.cascodev.com
Yes. There is a third-party product from Lumigent called Log Explorer that
is very good for reading transaction logs. It has a lot of features for
finding transactions and undoing or redoing them.
www.lumigent.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Russell White" <rwhite04@.maine.rr.com> wrote in message
news:OdHbKLUgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Is it possible to read a transaction log or can they only be used for
> restoration purposes?
> thanks in advance
> Russ White
> rwhite@.cascodev.com
>
>

Is it possible to read a transation log?

Is it possible to read a transaction log or can they only be used for
restoration purposes?
thanks in advance
Russ White
rwhite@.cascodev.comYes. There is a third-party product from Lumigent called Log Explorer that
is very good for reading transaction logs. It has a lot of features for
finding transactions and undoing or redoing them.
www.lumigent.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Russell White" <rwhite04@.maine.rr.com> wrote in message
news:OdHbKLUgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Is it possible to read a transaction log or can they only be used for
> restoration purposes?
> thanks in advance
> Russ White
> rwhite@.cascodev.com
>
>

Is it possible to read a transation log?

Is it possible to read a transaction log or can they only be used for
restoration purposes?
thanks in advance
Russ White
rwhite@.cascodev.comYes. There is a third-party product from Lumigent called Log Explorer that
is very good for reading transaction logs. It has a lot of features for
finding transactions and undoing or redoing them.
www.lumigent.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Russell White" <rwhite04@.maine.rr.com> wrote in message
news:OdHbKLUgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Is it possible to read a transaction log or can they only be used for
> restoration purposes?
> thanks in advance
> Russ White
> rwhite@.cascodev.com
>
>

Is it possible to query a server's DBCC SHOWFILESTATS from one server to another?

I am trying to write a single query that will go out a collect all the
DBCC SHOWFILESTATS. The trick is that the query has to attach to the
remote server, then run the DBCC SHOWFILESTATS, and pull that
information back.
e.g. use servername2.master DBCC SHOWFILESTATS
This obviouly does not work even with the linked server option. Is
there another way to do this? I know you can use a select statement and
a linked server connection to query remote databases.you could create a procedure like this then call the proc on the server
create proc prShowFileStats
as
set nocount on
create table #SHOWFILESTATS (FileID int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name varchar(100),
FileName varchar(100))
insert into #SHOWFILESTATS
exec ('DBCC SHOWFILESTATS')
select * from #SHOWFILESTATS
set nocount off
http://sqlservercode.blogspot.com/

Is it possible to query a prior query?

Is there some way to save a selection set in SQL
so that another SQL query can further refine the
prior query?Peter Olcott (olcott@.worldnet.att.net) writes:
> Is there some way to save a selection set in SQL
> so that another SQL query can further refine the
> prior query?

http://www.algonet.se/~sommar/share_data.html may give you some
suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||My suggestion is to dynamicly build view.You can query view as any other
table.You can also dynamicly build SQL SELECT statement and add conditions
in WHERE clause and execute it.sql

Is it Possible to query a Cube with Query Analyzer?

i would like to query either a cube or dimension direclty using tsql and query analyzer. i thought i would be able to use openquery but cant figure out the proper syntax. any help would be appreciated.

This is an example in a previous discussion here. I have not tried this example myself.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=658049&SiteID=1

HTH

Thomas Ivarsson

is it possible to put the raiseerror in a text file or show it to the user after its been

I am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...

Any help will be appreciated.

Regards

Karen

Take a look forSqlException.Errors. Any SqlError object has the number of error and another informations.

PS.: To another databases, take a look forOleDbException.Errors.

|||

Hi Karenros,

Based on my understanding, you want to use Raiserror to generate an error message and pass it to the client user. Client user may get an alerting message or write it to a text file when getting the error message. If I've misunderstood you ,please feel free to tell me, thanks.

You can put your sqlcommand in a try block and in your catch block, write sqlconnection.errors.message to a text file. Please remember do not assign the severity value of your error message more than 19, or else it maybe cause terminate your connection. Sample code is like the following:

 try { con.Open(); cmd.ExecuteNonQuery(); }catch (SqlException ex) {using(StreamWriter sw=new StreamWriter("your text log file path here")) {foreach (SqlError errin ex.Errors) sw.WriteLine(err.Message+"\n"); } }

Hope my suggestion helps

|||

Chen,

Thanks for your answer. yeah and thats exactly that i wanted to do... so that user would know if the import process was successful or not...

I have tried using sqlexception before with no luck.. may be i didnt import the right header files in order for that work and i have also seen on msdn that we need a sqlinfomessage class or something like to do it.. Pls correct me if i am wrong...

anyways i am gonna give it a try and will let you know...

Regards

Karen

|||

Below is an example of how you can use the InfoMessage event handler:

First you'll have to create an event handler for this event like below:

con.Open();
con.InfoMessage +=new SqlInfoMessageEventHandler(con_InfoMessage);// here i've registered for the event
... set up the command object
cmd.NotificationAutoEnlist =true;
cmd.ExecuteNonQuery();

Then you can go on and write any code you want in that event handler method.

private void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// your file writing code goes here. The eventArgs e holds errors, messages, source etc.
 // you can just use e.ToString() and everything is there for you.
}

Hope this will help.

|||

Hi Karenros,

I've tested the sqlexception code on my local machine and it does work fine. So, maybe you have made some mistakes somewhere else.

However, I think you can also trydhimant 's solution. That's really a good method to solve your problem. thanks

Is it possible to put several queries into one sp

I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.

After that query could be a good time to count total of all calculated sub sums.

Something like this.

previous query

END

go
SELECT SUM(SubTotal)
FROM dbo.t_Shoppings

I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)

Is this possible or do I have make and call another stored procedure.

I can send my sp if someone wants.

No, this is for sure possible. Try taking the "END" between your first and second query and put it after your second query. Let me know what errors you are getting when you try to execute it if that doesn't work

|||

you can just put queries on eby one withiout any separator , just start every select statement in new line. Do not use GO it unless you run your query in Management studio environment (Go is not SQL comamnd)

|||

Thank you both for your advises. I'll test the sp later today. And I have to check how to return parameters and so on first.

I'll mark your answers as answers then too.

Regards

Leif

|||

I tried today my sp and got it working. Server management studio didn't show output parameter right but the query itself seems to work. I'll test it with code later.

Thanks for help.

Here is my stored procedure now. I didn't translate it, so variables may look strange. Kokonaissumma means total value btw.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END

return (SELECT count(*) FROM dbo.t_osto)

(select @.kokonaissumma=sum(Yhteensa)FROM dbo.t_osto)

Is it possible to put in "IF ...ELSE" or "Case" in WHERE CLAUSE?

Dear all...

need your help... i am now trying to create a report using SQL reporting services... when declare all the @.parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.

select..............(blah blah).....
.....(SELECT CASE WHEN
((SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType .....................(blah blah)......

where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate) OR (@.FromDate = ' ') OR (@.ToDate = ' ')
AND (@.PatientType = CASE WHEN
(SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)

my situition is something like above, i know i have done something wrong in teh WHERE clause for the @.PatientType... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...

thanks in advanced ...............Your post is kind of confusing regarding requirements, but part of your problem may be due to using brackets where they are not necessary, and not using them where they might be necessary to specify logical operations.

--Your Version:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate)
OR (@.FromDate = ' ')
OR (@.ToDate = ' ')
AND (@.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END)

--Unnecessary brackets removed:
where CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' '
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END

--Useful brackets added:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' ')
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END|||ya...thanks for reminding me...as there are too many parameters to pass, i also confused... ;) anyway, really appreciate ur help

Is it possible to publish subscriptions to Reporting Services?

Is there anything in the API that would support this, or would it have to be
done surreptitiously in the database?
Thanks,
Bill MellYes it is possible . . . using the CreateSubscription method.
(just in case anyone cares)
Bill Mell
"Bill Mell" <BillM@.netforcement.com> wrote in message
news:%23sRXzT0DIHA.1316@.TK2MSFTNGP02.phx.gbl...
> Is there anything in the API that would support this, or would it have to
be
> done surreptitiously in the database?
> Thanks,
> Bill Mell
>sql

is it possible to process the cube using vb.net/c# which exists in the remote server?

can we process the cube exists in the remote system, using vb.net/c#

i am able to process in the same system, where analysis service available.

i couln't process this from the client system.

is there any soluctions?

You can use either the AMO or ADOMD libraries to send processing commands to a remote server.

The AMO library is easier to work with for admin tasks, but you would have to redeploy the dll's. Where as any client that has to query the cubes will already need have ADOMD installed.

The following send an XMLA processing command using ADOMD, all you need to do is to create a console application, add a reference to Microsoft.AnalysisServices.AdomdClient and paste in the following:

Code Snippet

Sub Main()

Dim serverName As String = "Server1"

Dim databaseName As String = "Adventure Works DW"

Dim databaseID As String = databaseName

Dim cubeID As String = "Adventure Works"

Dim cn As New AdomdConnection("Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName)

Console.WriteLine("Opening Connection...")

cn.Open()

Dim cmd As AdomdCommand

cmd = cn.CreateCommand()

cmd.CommandType = CommandType.Text

cmd.CommandText = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Parallel><Process> <Object>" & _

"<DatabaseID>" & DatabaseID & "</DatabaseID>" & _

"<CubeID>" & cubeID & "</CubeID> " & _

"</Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>"

Try

Console.WriteLine("Executing Command...")

cmd.ExecuteNonQuery()

Console.WriteLine("Command Complete")

Catch ex As Exception

Console.WriteLine(" --== ERROR ==--")

Console.WriteLine(ex.Message)

Console.WriteLine()

Finally

cn.Close()

Console.WriteLine("Finished")

End Try

End Sub

|||

Hi Darren

I tried your solution using AdoMd 8.0 on AS 2005,

but it seems that no data are refreshed,

neverthless the command text is executed without any error

Do I miss any other instruction (i.e: 'comitt transaction', .)?

Thanks

|||

I don't think ADOMD 8.0 has the execute method you need 9.0 to work with AS2005.

I just double checked and if your account has permission to process the server, database and cube properties are set correctly it should work. Try running a profiler session on the server and see if there are any errors being thrown at that end.

Is it possible to prevent user to edit parameters in adress bar?

I have reports in report server which are accessed by url access with parameters.

When the report is generated the whole url string is displayed in the adress bar with parameters. Is there any way to prevent the user to change the parameter value?

Any ider whould be very appreciated!

You could use a html form and use POST instead of using GET via the url.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=301748&SiteID=1

Is it possible to prevent databases from being copied?

Hi,

We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.

Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.

Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.

Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.

Is this possible?

Regards,

Sigol.

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

There are several issues.

1. As you noted, a backup 'could' be restored on another server. Various third party backup programs allow passwords and encryption for backups. Any SQL Admin can create a backup that can be restored elsewhere.
2. A SQL administrator could take the database 'offline' for a few minutes and copy the data file (*.mdf)
3. A local administrator could shut down the SQL Service for a few minutes and copy the data file (*.mdf).
4. Even with an Encrypted database, or tables, or even specific columns, a local SQL Administrator can usually get around the protections.

Don't allow any local administrators to be in the SQL Admins role.

So if you are concerned about protecting a database, the 'best' solutions, in a situation where you can't control the local administrators, is to look into database encryption using encryption keys, or better yet, certificates.

|||

Thank you for your comments, Arnie. This was very helpful to me.

Regards,

Sigol.

|||

Arnie Rowland wrote:

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

Having a grammatically bad day, Arnie!

sql

Is it possible to perform terms lookup on unstructured files ?

Hi,
I need to categorize a lot of html or text files according to a list of terms and I wonder if terms lookup is adequate for this. The problem is that terms lookup can only take an Oledb source as input. My files can be up to 80 Kb big and aren't columns structured.

Should I import my files in a table ? But if so, how can I import a column with more than 8000 characters ?

Thank you in advance.

I think you may have this the wrong way around. The list of terms must be stored in an OLE-DB sourced table, but the input is the data you want to examine. This can come from any upstream component. You will still need to get your data into the pipeline, but that is perhaps not quite as hard as OLE-DB. Maybe the Import Column Transform could help?

You mention a 8000 character limit, which is the limit for non-unicode strings in the varchar (T-SQL) or DT_STR (SSIS) data types. Whilst the Term transformations only support unicode data types, with their 4000 character limit, they do support the DT_NTEXT type, equivalent to the T-SQL ntext type, which allows up to 2GB of data.

|||Thank you very much for your quick reply. My mistake, you're right, I'm a new user of SSIS and I misunderstood the explanations on the lookup. I'm digging into this. Thanks again for your help.

Is it possible to perform Sql 7 Enterprise upgrade to Sql 200o Standard Editions

Hi all,

I was curious if there any issues that I need to be aware off in attempting to upgrade an existing client database (Sql Server 7 Enterprise Edition) to Sql 2000 Standard edition. During upgrades are we forced to stay with the same editions or can the upgrade to lower level editions.

Thanks
ZeidNot possible, you need to uninstall and re-install SE.
EE is far superior than SE by any means.

is it possible to pass array to stored procedure

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me

thanks

Moving SQL Server forum.

Thanks.

|||

No... it is not posible to pass arryas to sp. BTW what is u r requirement , there are workaround for everything.. so pse specify u r requirement..

Madhu

is it possible to pass a report table column in sql reporting serivces

Hi,
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh

Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.

Hope that helps,

-Lukasz

is it possible to pass a report table column in sql reporting serivces

Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.

Hope that helps,

-Lukasz

sql

Is it possible to output a Field Value based on another field value?

I would like the data value of a text field in a table to based on the
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>

Is it possible to optimize DB by this strange way ?...

hi,

Does anybody know severals studies, reports, researchs, tests, benchmark results on this kind of DB optimization ?

Let's considering, i'm working with a big database, with millions reccords, amongst 30 tables with 10 fields each one.

1.
If i have many fields named like this '12345fdf4564ggdfg65456446465' (for a privacy security reason)
Is it important in the SQL execution speed ?

2.
if i have many tables (with fields too) named like "table123132111111122222222222fdgdfgdfgdfg22"
Is it important in the SQL execution speed ?

3.
if my order creation table is this : table A, then table B, then table C and in last the table D.
Supose the table D (last created,donc in the end of hard drive) is the table which contains the more data.
Is the SQL execution will be slower than if the table was created in first (table a) ?

4.
now, supose the table D (last created) is the table which concerns the most part of SQL query of my application in a day and its contains the more data.
Is the SQL execution will be slower than if the table was created in first (table a) ?

5. last question :
is it more speed to format a date in the SELECT query (so using DBS server) OR in the front back with using a PHP function (or other language) ?

any ideas for all ?

thanks for help ! (don't laugh please, and remeber i working on a big database...)1. non
2. non
3. non
4. non
5. c'est tout egal|||i can't be equal on a big volume database...|||please give example for the SQL used in question 5|||Does anybody know severals studies, reports, researchs, tests, benchmark results on this kind of DB optimization ?

Yes. I bought a stopwatch for $30 at WalMart, and it is a great way to study optimization.

Let's considering, i'm working with a big database, with millions reccords, amongst 30 tables with 10 fields each one.

No, let the DBMS consider it. Create millions of records in a test database, write a script to time various queries and see what is faster.

There is only one way to be certain and that is to test it yourself.|||Quick and dirty MSSQL Server test

DECLARE @.Start DateTime

SET @.Start = GetDate()

--YourSQLStatementToTest

SELECT DateDiff(ms, @.Start, GetDate()) As 'Time Taken to Execute (milliseconds)

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to name the Excel sheets of an exported report ?

Hello,
Is there a way to name Excel sheets when I export a report ?
My report generate several sheets and I would like to name each one.
Thanks for your help.Unfortunately, this is not possible in this release of Reporting
Services.
Mike|||It gets asked for a lot. I'm still amazed that they haven't been willing to
add this feature. Many people have been asking for it since day one of RS
going live.
"Bassist695" wrote:
> Unfortunately, this is not possible in this release of Reporting
> Services.
> Mike
>sql

is it possible to move table(s) from one db to another?

...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts[vbcol=seagreen]
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same
> in
> the
>
|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves

...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the

is it possible to move table(s) from one db to another?

thanks in advance.Use the import/export functionality in Enterprise Manager.
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||By move you mean you want the data to be copied right ? If yes, then you
have:
1. Create a DTS package has a wizard approach that will transfer the same in
a few clicks.
2. Script out all the data using available third party tools and script the
data and import the same. One such script present at :
http://vyaskn.tripod.com/code/generate_inserts.txt
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Tin" <oe@.fawcette.com> wrote in message
news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> thanks in advance.
>|||Hi,
It seems tin needs to move the table from one database to another database
in the same server. In this case he can select * INTO statement.
For eg:
You have 2 databases- 1 is HR and the 2nd is Finance..
Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
execute the below command from Query Analyzer.
select * into FINANCE..Employee from HR..Employee
Once the tabe is successfully copied then you can drop the table from source
database. But ensure that full data is copied to destination.
Note:
If you need the indexes then you have to create the indexes by scripting
them using the Enterprise manager -- Databases-- Alltasks-- Generate Scripts
by selecting the current database and table.
Thanks
Hari
MCDBA
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> By move you mean you want the data to be copied right ? If yes, then you
> have:
> 1. Create a DTS package has a wizard approach that will transfer the same
in
> a few clicks.
> 2. Script out all the data using available third party tools and script
the
> data and import the same. One such script present at :
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Tin" <oe@.fawcette.com> wrote in message
> news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > thanks in advance.
> >
> >
>|||Well Hari.
Cool. The select into clause is the easiest implementation. But having said
that this also copies the data not moves :) ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > By move you mean you want the data to be copied right ? If yes, then you
> > have:
> >
> > 1. Create a DTS package has a wizard approach that will transfer the
same
> in
> > a few clicks.
> > 2. Script out all the data using available third party tools and script
> the
> > data and import the same. One such script present at :
> > http://vyaskn.tripod.com/code/generate_inserts.txt
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD, MCSD
> > http://www.extremeexperts.com
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> >
> >
> > "Tin" <oe@.fawcette.com> wrote in message
> > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > thanks in advance.
> > >
> > >
> >
> >
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP12.phx.gbl>...
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having said
> that this also copies the data not moves :) ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > It seems tin needs to move the table from one database to another
> database
> > in the same server. In this case he can select * INTO statement.
> >
> > For eg:
> >
> > You have 2 databases- 1 is HR and the 2nd is Finance..
> >
> > Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> > execute the below command from Query Analyzer.
> >
> > select * into FINANCE..Employee from HR..Employee
> >
> > Once the tabe is successfully copied then you can drop the table from
> source
> > database. But ensure that full data is copied to destination.
> >
> > Note:
> > If you need the indexes then you have to create the indexes by scripting
> > them using the Enterprise manager -- Databases-- Alltasks-- Generate
> Scripts
> > by selecting the current database and table.
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> > news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
> > > By move you mean you want the data to be copied right ? If yes, then you
> > > have:
> > >
> > > 1. Create a DTS package has a wizard approach that will transfer the
> same
> in
> > > a few clicks.
> > > 2. Script out all the data using available third party tools and script
> the
> > > data and import the same. One such script present at :
> > > http://vyaskn.tripod.com/code/generate_inserts.txt
> > >
> > > --
> > > HTH,
> > > Vinod Kumar
> > > MCSE, DBA, MCAD, MCSD
> > > http://www.extremeexperts.com
> > >
> > > Books Online for SQL Server SP3 at
> > > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> > >
> > >
> > > "Tin" <oe@.fawcette.com> wrote in message
> > > news:%23AM8vlrPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > > > thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >

is it possible to move table(s) from one db to another?

...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems tin needs to move the table from one database to another
database
> in the same server. In this case he can select * INTO statement.
> For eg:
> You have 2 databases- 1 is HR and the 2nd is Finance..
> Incase if Tin need to move the EMPLOYEE table in HR to Finance then he can
> execute the below command from Query Analyzer.
> select * into FINANCE..Employee from HR..Employee
> Once the tabe is successfully copied then you can drop the table from
source
> database. But ensure that full data is copied to destination.
> Note:
> If you need the indexes then you have to create the indexes by scripting
> them using the Enterprise manager -- Databases-- Alltasks-- Generate
Scripts
> by selecting the current database and table.
>
> Thanks
> Hari
> MCDBA
>
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OhXlH0tPEHA.628@.TK2MSFTNGP11.phx.gbl...
same[vbcol=seagreen]
> in
> the
>|||If you need to move the table, with structure intact (indexes,
triggers, constraints, permissions, etc) I would probably recommend
using the DTS Import feature. This will better ensure that all
dependencies are intact. Select Into is quite fast, but may leave you
with some cleanup work on setting defaults, permissions, etc.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message news:<usDWiJxPEHA.1348@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Well Hari.
> Cool. The select into clause is the easiest implementation. But having sai
d
> that this also copies the data not moves

...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%230rs5FuPEHA.556@.TK2MSFTNGP10.phx.gbl...
> database
> source
> Scripts
> same
> in
> the

is it possible to move my sql 2000 database (in C disk) to another disk (Disk) ?

hello,all

I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .

is it possible to do it ?

if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?

thanks in advanced!

You can "detach" the detabase, physically copy/move the files (*.mdf and *.ldf files) to the new location, then "attach" the DB and select the files from the new location. You dont need to change anything on the application level.

Is it possible to move a database from 2005 to 2000?

Hello,

I have a database in 2005 that I'd like to copy and attach in 2000. Is this
possible? if so, how do I do it? I've tried doing a number of things from
just trying to attach to the db file to trying to export and import via
access and I can't seem to get anything to work.

Any help would be greatly appreciated.

Thanks!
RickRico (me@.you.com) writes:

Quote:

Originally Posted by

I have a database in 2005 that I'd like to copy and attach in 2000. Is
this possible? if so, how do I do it? I've tried doing a number of
things from just trying to attach to the db file to trying to export and
import via access and I can't seem to get anything to work.

You cannot attach a database from SQL 2005 on SQL 2000. When the developers
completed SQL 2000, they did not what know would be in the product five
years later.

To copy the schema, right-click the database in Mgmt Studio and select
Tasks->Generate Script. There is a setting to force SQL 2000 syntax,
you need to select that one. (I seem to recall that there was a bug
with this in SQL 2000 RTM, so make sure that you have SP1.)

If memory serves, create of foreign keys is at the end. In any case,
split the script so that FKs are in a script of their own. Run only
the script without the FKs. Beware that copying the schema can only be
completed successfully, if there is on use of new features in SQL 2005 in
the database.

To copy the data run this:

SELECT 'BCP db..' + name + ' out ' + name + '.bcp -T -S server2005 -n'
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy and paste result, run from a command-line window. Then change
the above to:

SELECT 'BCP db..' + name + ' in ' + name + '.bcp -T -S server2000 -n ' +
case when ident_current(name) is not null then ' -E' else '' END
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy, paste and run again.

Finally rnn the script with the FK:s.

Note the BCP stuff assumes that you have everything in the dbo schema,
as is the only thing that makes sense on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Is it possible to modify Report Builder template ?

I would like to be able to modify the basic report template offered by Report Builder , so as to be able to include custom fields that I would like to be present in every report (Company name , Logo , Date , etc) to achieve consistency in the presentation of the reports created with Report Builder

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George
|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George
|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Hi
At moment support for templates, styles, and images are not available with report builder. For more information please see: http://www.microsoft.com/sql/technologies/reporting/faq.mspx, http://msdn2.microsoft.com/en-gb/library/aa964121.aspx (Understanding the Product Limitations)

The workaround for this is to have a report (just format) that will be served as template. User will required to open this report (template) then after making the required changes they make copy of the same by doing "Save As" instead of "Save"

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify Report Builder template ?

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify Report Builder template ?

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify column data type of view

The SQL Server Management Studio shows the data type of each column of views. I am wondering how SQL server determines the types since my SQL code of views does not specifiy data types for any columns.

I am much more interested in knowing whether the data types can be modified. Could anyone offer some hint?

Thanks,

SQL Server uss the underlying schema information to do so unless you don′t specify a different data type than the source data type (liek within CONVERT). You can change the resulting data type in the view e.g. via CONNVERT(VARCHAR(10),GETDATE(),112), which was a datetime before and a varchar afterwards.

HTH, Jens Suessmeyer.|||

Jens, thanks a lot! That is exactly what I was looking for.

Is it possible to Migrate 2005 database to 2000 engine?

... Try your own manual migration and come up with a white
paper

... Read below link
http://searchsqlserver.techtarget.co...301536,00.html
Keppe on posting the issues you face so that we can help...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Simon" wrote:

> Hi,
> We have been developing a new database using 2005 with the understanding
> that the deployment would be on a 2005 production instance. Now we learn
> that the target server is running 2000. We could create a separate 2005
> instance on the same server but it seems that the additional administration
> of security and object ownership will be more hassle that downgrading the
> empty data objects to 2000. So far, the database consists of about 20
> tables with accompanying meta data (table/column descriptions). I was
> planning to use sql scripts generated in 2005 to recreate the tables on a
> 2000 instance but realize that some modifications will be required to
> recreate the metadata.
> Does anyone have any suggestions on the migration of a 2005 database to
> 2000?
> Thanks, Simon
>
>
sql

Wednesday, March 28, 2012

Is it possible to Migrate 2005 database to 2000 engine?

... Try your own manual migration and come up with a white
paper

... Read below link
http://searchsqlserver.techtarget.c...x301536,00.html
Keppe on posting the issues you face so that we can help...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Simon" wrote:

> Hi,
> We have been developing a new database using 2005 with the understanding
> that the deployment would be on a 2005 production instance. Now we learn
> that the target server is running 2000. We could create a separate 2005
> instance on the same server but it seems that the additional administratio
n
> of security and object ownership will be more hassle that downgrading the
> empty data objects to 2000. So far, the database consists of about 20
> tables with accompanying meta data (table/column descriptions). I was
> planning to use sql scripts generated in 2005 to recreate the tables on a
> 2000 instance but realize that some modifications will be required to
> recreate the metadata.
> Does anyone have any suggestions on the migration of a 2005 database to
> 2000?
> Thanks, Simon
>
>

Is it possible to Migrate 2005 database to 2000 engine?

Hi,
We have been developing a new database using 2005 with the understanding
that the deployment would be on a 2005 production instance. Now we learn
that the target server is running 2000. We could create a separate 2005
instance on the same server but it seems that the additional administration
of security and object ownership will be more hassle that downgrading the
empty data objects to 2000. So far, the database consists of about 20
tables with accompanying meta data (table/column descriptions). I was
planning to use sql scripts generated in 2005 to recreate the tables on a
2000 instance but realize that some modifications will be required to
recreate the metadata.
Does anyone have any suggestions on the migration of a 2005 database to
2000?
Thanks, SimonNo easy way :(... Try your own manual migration and come up with a white
paper :)... Read below link
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html
Keppe on posting the issues you face so that we can help...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Simon" wrote:
> Hi,
> We have been developing a new database using 2005 with the understanding
> that the deployment would be on a 2005 production instance. Now we learn
> that the target server is running 2000. We could create a separate 2005
> instance on the same server but it seems that the additional administration
> of security and object ownership will be more hassle that downgrading the
> empty data objects to 2000. So far, the database consists of about 20
> tables with accompanying meta data (table/column descriptions). I was
> planning to use sql scripts generated in 2005 to recreate the tables on a
> 2000 instance but realize that some modifications will be required to
> recreate the metadata.
> Does anyone have any suggestions on the migration of a 2005 database to
> 2000?
> Thanks, Simon
>
>

Friday, March 30, 2012

Wednesday, March 28, 2012

Blog Archive

About Me