Hi Guys,
I am new in analysis service. I just installed analysis service in my
personal computer that has a sql server enterprise installed. When I opened
analysis manager and try to open the server in the left pane, I got error
that no register sql server or it is not in the olap group(roughly, I cannot
remember clearly), then server icon because red(closed). it seems like I
couldn't connect to database server. But it is same server and of cause I
have already register this server. What is wrong with this? Thanks.
it would help if 'clearly' and not 'roughly' took the time to post the full
error and in return we will 'clearly' answer your question!
It sounds to me the user account you are logged on as isn't in the OlapAdmin
group. Add your logged on account to this group and it should do the trick.
Immy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75B81FA5-1CEC-4815-A597-95BBCA829BDB@.microsoft.com...
> Hi Guys,
> I am new in analysis service. I just installed analysis service in my
> personal computer that has a sql server enterprise installed. When I
> opened
> analysis manager and try to open the server in the left pane, I got error
> that no register sql server or it is not in the olap group(roughly, I
> cannot
> remember clearly), then server icon because red(closed). it seems like I
> couldn't connect to database server. But it is same server and of cause I
> have already register this server. What is wrong with this? Thanks.
>
|||You will get this behavior if you are trying to run an earlier version of
Analysis Services 2000 on Win XP Pro. If this is the case, you need to
upgrade both SQL Server 2000 and As 2000 to SP4. Search the MSKB for
details.
John G.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75B81FA5-1CEC-4815-A597-95BBCA829BDB@.microsoft.com...
> Hi Guys,
> I am new in analysis service. I just installed analysis service in my
> personal computer that has a sql server enterprise installed. When I
> opened
> analysis manager and try to open the server in the left pane, I got error
> that no register sql server or it is not in the olap group(roughly, I
> cannot
> remember clearly), then server icon because red(closed). it seems like I
> couldn't connect to database server. But it is same server and of cause I
> have already register this server. What is wrong with this? Thanks.
>
Showing posts with label guys. Show all posts
Showing posts with label guys. Show all posts
Friday, March 30, 2012
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.
Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>
|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
sql
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.
Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>
|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
sql
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks
.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, th
e
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Than
ks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks
.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, th
e
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Than
ks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
Regarding drop and restore replication database
Hi Guys,
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:
> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:
> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Regarding domain name for mirroring
Hi guys, may I know whether the domain name needed to be same for the principal server and mirror server in order to get a connection between both the servers and setting up mirroring section successful? Is it possible to use different domain name for both servers for setting up the mirroring section. Can someone share his/her idea or experience like how to setting up the mirroring section and the configuration for the windows before setting up. Thx and hope can get any assistance here.
Best regards,
Hans
Yes, Database Miroring can be setup across two different domains.Please look at the Books Online at http://msdn2.microsoft.com/en-us/library/ms177412.aspx for details.Regarding Database Tuning Advisor
Hi guys, I am new to use Database Tuning Advisor. Right now I just save a workload as a sql and runs it in Database Tuning Advisor. The script that I wrote is kinda simple ( Select EID,EName,EDescription From Events Where EID = 300) . However, after I finish analyzed it, there's nothing to display on the recommendations session. Is it suppose got any details in it ? Or does it mean I no need to tune my query performance ? Hope someone can help me out here. Thx guys.
Best Regards,
Hans
It sounds like there were no recommendations -which I would suspect for such a simplistic script if you had basic indexes.
Normally, to generate a 'workload', you would run Profiler for a period of time, capturing all work on the server to a file (or table), and then use the DTA with that workload. I normally recommend, depending upon the amount of 'traffic', to capture a 24 hours block of work, or several key one hour segments during a day.
The primary idea with DTA is to try and reproduce what is happening on the server so that it can try to determine if the indexing is appropriate considering all that is being requested.
Wednesday, March 28, 2012
Regarding Accessing on every objects within the sql 2005
Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thxcould you explain this a bit more, I did′n′t get your point from the explanation.
Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Sorry for the short explanation. Er... let me give a scenario. Once I open SQL management studio, there's a user login pop up window in order I able to access into my server. After login successfully,there are 3 database displays and stored in my servers ( Db A,B and C). When I want to click on the A database, then will pop up the user login window again for authorization. After that, if I want to choose a specific object (eg: Table 'Test') , then there's the use login window pop up again for authorization. At here, which means that authorization will show first before proceed any actions on any objects from the database in my server,.
So is there any possible to make such scenario? I'm just curious about it and would like to enhance my SQL security for my server and away from data thefting (For example, while away from the pc for a while and there's a staff come towards the pc for viewing and modifying the data). Thx for any assistance. Have a nice day.
From,
Hans
|||Hi,
no SSMS does not have this functionality. once autenticated, permissions are checked upon access to the objects not prompting for any credentials again.
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
You should lock your console when away from your machine.
Thanks
Laurentiu
There was no such feature in SQL Server 2000. Maybe you are referring to a client feature, in which case you should post your inquiry on the Tools forum.
Thanks
Laurentiu
Regarding 3rd party SQL tools (Free Secure?)
Hi guys, I just curious about 3rd party tools whether there are free secure to use and any recommend tools for SQL? Currently I am using some tools like 'SQL Server Online Backup' and 'ApexSQL'. When I am using those 3rd party tools,is there any trace file or information (username & password) will be keep and send to other locations or 3rd party? Thanx and hope can get any assistance or opinion here. Have a nice day.
Best Regards,
Hans
Refer to http://www.sql-server-performance.com website and refer to the software spotlight for such information.
Moving to Tools section as per the subject relevancy.
Regarding 3rd party SQL tools (Free Secure?)
Hi guys, I just curious about 3rd party tools whether there are free secure to use and any recommend tools for SQL? Currently I am using some tools like 'SQL Server Online Backup' and 'ApexSQL'. When I am using those 3rd party tools,is there any trace file or information (username & password) will be keep and send to other locations or 3rd party? Thanx and hope can get any assistance or opinion here. Have a nice day.
Best Regards,
Hans
Refer to http://www.sql-server-performance.com website and refer to the software spotlight for such information.
Moving to Tools section as per the subject relevancy.
Monday, March 26, 2012
Refuse connections
Hi guys,
I would like to know how could I configure my SQL Server (2000) to refuse every connection except localhost or server-name ?
I am sure this can be acomplished, but I've looked everywhere and can't find it.
I use asp.net only and I want to configure the server to accept connections from local computer and that's it. Any thoughts?
ThanksDisable all network protocols except shared memory.|||Thanks.
Also, I have two instances of MSSQL installed, one is SQL 2000 & other is Desktop Edition, will the client configurations apply to both or just to SQL 2000 and if just to SQL 2000 how can I configure it also for the Desktop Edition?|||It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)
Any other ideas?|||It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)
Any other ideas?
Did you restart SQL after changing the network configuration? Look at the error log for SQL Server when it restarts, there should be entries in there to indicate what protocols/ports SQL is listening on.
Regards,
hmscott|||Restarted, still same thing. I can connect from one box to the other. Log file:
2005-12-06 04:11:31.10 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2005-12-06 04:11:31.10 server Copyright (C) 1988-2002 Microsoft Corporation.
2005-12-06 04:11:31.10 server All rights reserved.
2005-12-06 04:11:31.10 server Server Process ID is 3336.
2005-12-06 04:11:31.10 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2005-12-06 04:11:31.20 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2005-12-06 04:11:32.78 server SQL Server configured for thread mode processing.
2005-12-06 04:11:32.79 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-12-06 04:11:32.85 server Attempting to initialize Distributed Transaction Coordinator.
2005-12-06 04:11:35.71 spid3 Starting up database 'master'.
2005-12-06 04:11:36.10 spid3 Server name is 'SERVER9'.
2005-12-06 04:11:36.10 spid3 Skipping startup of clean database id 44
2005-12-06 04:11:36.26 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-12-06 04:11:36.26 spid5 Starting up database 'model'.
2005-12-06 04:11:36.35 spid8 Starting up database 'msdb'.
2005-12-06 04:11:36.35 spid9 Starting up database 'pubs'.
2005-12-06 04:11:36.37 spid10 Starting up database 'Northwind'.
2005-12-06 04:11:36.37 spid11 Starting up database 'ahsweb'.
2005-12-06 04:11:38.68 spid5 Clearing tempdb database.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 127.0.0.1: 1433.
2005-12-06 04:11:40.45 server SQL server listening on TCP, Named Pipes.
2005-12-06 04:11:40.48 server SQL Server is ready for client connections
2005-12-06 04:11:39.95 spid9 Starting up database 'ahl'.
2005-12-06 04:11:40.06 spid10 Starting up database 'afp'.
2005-12-06 04:11:40.31 spid8 Starting up database 'act'.
2005-12-06 04:12:19.15 spid8 Recovery is checkpointing database 'eah' (73)
2005-12-06 04:12:29.78 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2005-12-06 04:12:55.46 spid3 Recovery complete.
2005-12-06 04:12:55.46 spid3 SQL global counter collection task is created.|||The Enterprise Manager says TCP is disabled but it still shows up on the logs
Any work arround?|||2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 127.0.0.1: 1433.
2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
2005-12-06 05:37:02.32 server SQL server listening on TCP, Shared Memory.
2005-12-06 05:37:02.32 server SQL Server is ready for client connections
Even thought TCP is disabled|||Any more thoughts? Anyone?|||You're sure you are diabling TCP in the Server Network Parameters (or Server Network Utility)? The Client network utility looks the same, but it has a different function. Also, why the multiple IP addresses? I don't usually multi-home a database server (except for a cluster), so I don't know how SQL will behave with multiple IP addresses.
Regards,
hmscott
I would like to know how could I configure my SQL Server (2000) to refuse every connection except localhost or server-name ?
I am sure this can be acomplished, but I've looked everywhere and can't find it.
I use asp.net only and I want to configure the server to accept connections from local computer and that's it. Any thoughts?
ThanksDisable all network protocols except shared memory.|||Thanks.
Also, I have two instances of MSSQL installed, one is SQL 2000 & other is Desktop Edition, will the client configurations apply to both or just to SQL 2000 and if just to SQL 2000 how can I configure it also for the Desktop Edition?|||It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)
Any other ideas?|||It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)
Any other ideas?
Did you restart SQL after changing the network configuration? Look at the error log for SQL Server when it restarts, there should be entries in there to indicate what protocols/ports SQL is listening on.
Regards,
hmscott|||Restarted, still same thing. I can connect from one box to the other. Log file:
2005-12-06 04:11:31.10 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2005-12-06 04:11:31.10 server Copyright (C) 1988-2002 Microsoft Corporation.
2005-12-06 04:11:31.10 server All rights reserved.
2005-12-06 04:11:31.10 server Server Process ID is 3336.
2005-12-06 04:11:31.10 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2005-12-06 04:11:31.20 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2005-12-06 04:11:32.78 server SQL Server configured for thread mode processing.
2005-12-06 04:11:32.79 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-12-06 04:11:32.85 server Attempting to initialize Distributed Transaction Coordinator.
2005-12-06 04:11:35.71 spid3 Starting up database 'master'.
2005-12-06 04:11:36.10 spid3 Server name is 'SERVER9'.
2005-12-06 04:11:36.10 spid3 Skipping startup of clean database id 44
2005-12-06 04:11:36.26 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-12-06 04:11:36.26 spid5 Starting up database 'model'.
2005-12-06 04:11:36.35 spid8 Starting up database 'msdb'.
2005-12-06 04:11:36.35 spid9 Starting up database 'pubs'.
2005-12-06 04:11:36.37 spid10 Starting up database 'Northwind'.
2005-12-06 04:11:36.37 spid11 Starting up database 'ahsweb'.
2005-12-06 04:11:38.68 spid5 Clearing tempdb database.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 1433.
2005-12-06 04:11:39.42 server SQL server listening on 127.0.0.1: 1433.
2005-12-06 04:11:40.45 server SQL server listening on TCP, Named Pipes.
2005-12-06 04:11:40.48 server SQL Server is ready for client connections
2005-12-06 04:11:39.95 spid9 Starting up database 'ahl'.
2005-12-06 04:11:40.06 spid10 Starting up database 'afp'.
2005-12-06 04:11:40.31 spid8 Starting up database 'act'.
2005-12-06 04:12:19.15 spid8 Recovery is checkpointing database 'eah' (73)
2005-12-06 04:12:29.78 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2005-12-06 04:12:55.46 spid3 Recovery complete.
2005-12-06 04:12:55.46 spid3 SQL global counter collection task is created.|||The Enterprise Manager says TCP is disabled but it still shows up on the logs
Any work arround?|||2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
2005-12-06 05:37:02.30 server SQL server listening on 127.0.0.1: 1433.
2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
2005-12-06 05:37:02.32 server SQL server listening on TCP, Shared Memory.
2005-12-06 05:37:02.32 server SQL Server is ready for client connections
Even thought TCP is disabled|||Any more thoughts? Anyone?|||You're sure you are diabling TCP in the Server Network Parameters (or Server Network Utility)? The Client network utility looks the same, but it has a different function. Also, why the multiple IP addresses? I don't usually multi-home a database server (except for a cluster), so I don't know how SQL will behave with multiple IP addresses.
Regards,
hmscott
Wednesday, March 21, 2012
Referential Integrity
Hi guys,
Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?
For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.
It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.
This is on behalf of a guy at work so "requirements" may change. ;)
Anyone got some ideas??
Cheers,
ShaunI see three options:
1) Try / Error approach: just delete, catch eventual error and handle it
2) In a SQL Server using ADo: Use OpenSchema to retrieve foreign key constraints
3) query SQL Server's schema tables
If these hints are not sufficient, I'll eloborate on your preferred option.|||There's always the "deleted item" option. Instead of removing it just mark it as deleted and then filter out deleted items in your search results. I used to think that was a lazy way of doing it, but now I kind of think it's helpful when dealing with product databases. Unless of course you expect to be removing many items over a short period of time.|||select object_name(fkeyid) from sysforeignkeys where rkeyid = object_id(tablename)
This will give you the table names which have a foreign key constraint on the table.|||what about the referential integrity option
the only reason i can think of why you'd want to select first before deleting the author, is if you had failed to properly implement referential integrity and were facing the prospect of perhaps leaving orphaned books around
with referential integrity declared, here's what you do
1. delete author
if this is successful, there were no books that would've been "orphaned"
if the delete is unsuccessful, there were, so then issue your error message|||thanks for all the suggestions guys. much appreciated. look like the same sort of stuff I was suggesting to the guy.
the reason he wants to do this is because he is expecting stupid users who won't understand what they are deleting by deleting a author (following the example).
I think he is intending to go with a deleted flag (soft delete) at this point.
Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?
For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.
It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.
This is on behalf of a guy at work so "requirements" may change. ;)
Anyone got some ideas??
Cheers,
ShaunI see three options:
1) Try / Error approach: just delete, catch eventual error and handle it
2) In a SQL Server using ADo: Use OpenSchema to retrieve foreign key constraints
3) query SQL Server's schema tables
If these hints are not sufficient, I'll eloborate on your preferred option.|||There's always the "deleted item" option. Instead of removing it just mark it as deleted and then filter out deleted items in your search results. I used to think that was a lazy way of doing it, but now I kind of think it's helpful when dealing with product databases. Unless of course you expect to be removing many items over a short period of time.|||select object_name(fkeyid) from sysforeignkeys where rkeyid = object_id(tablename)
This will give you the table names which have a foreign key constraint on the table.|||what about the referential integrity option
the only reason i can think of why you'd want to select first before deleting the author, is if you had failed to properly implement referential integrity and were facing the prospect of perhaps leaving orphaned books around
with referential integrity declared, here's what you do
1. delete author
if this is successful, there were no books that would've been "orphaned"
if the delete is unsuccessful, there were, so then issue your error message|||thanks for all the suggestions guys. much appreciated. look like the same sort of stuff I was suggesting to the guy.
the reason he wants to do this is because he is expecting stupid users who won't understand what they are deleting by deleting a author (following the example).
I think he is intending to go with a deleted flag (soft delete) at this point.
Monday, March 12, 2012
Referencing a field in another table as part of a formula in a computed column
Hi guys, I am new to all of this stuff so please bare with me. I will try to explain this as simply as I can. I have 2 tables.. Table1 and Table2 they are linked using both the STOCKCODE and SUPPLIER fields. In Table 1 is the STOCKCODE, LENGTH AND SUPPLIER fields (there are others but these are the 3 that I need to use) In Table2 is STOCKCODE, SUPPLIER and SUPPLER_MASS. What I want to do is put a calculated column into Table2. The formula I need is fairly simple it is: 1/(LENGTH*SUPPLIER_MASS)
Ok so here is what I would like:
1/(SUPPLIER_MASS*(SELECT Table1.LENGTH FROM Table1 WHERE Table1.STOCKCODE=Table2.STOCKCODE AND Table1.SUPPLIER=Table2.SUPPLIER)
From what I understand I can't have a query in a calculated column's "formula" field?
So how would I do this? I don't want to run a query because LENGTH or SUPPLIER_MASS could change on a day to day basis meaning I will have to run the query everything one of these changes. The benefit of having the calculated column is that it will update as soon as LENGTH or SUPPLIER_MASS changes.
Is there any way I can do this?
Thanks guys (and girls)You can have insert, update trigger which will update calculated column every time you update a record or insert a new one.
Thank you.
Friday, March 9, 2012
reference for error handling
Hi guys,
Do you have complete reference for sql error numbers?
I want to design customize error message, I want to know how to RAISERROR
with correct error number. thanksHi
select * from master.dbo.sysmessages
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||Between this and BOL you should be able to figure it all out:
SELECT *
FROM master..sysmessages
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||I tried a test, In this case, table4 doesn't exist in database,
but how can I print out this error ? I want to set this error to a
variable,
maybe to store in in error log table later on.
CREATE PROCEDURE testme
AS
select * from table4
return @.@.error
declare @.errornumber int
exec @.errornumber = testme
print @.errornumber
result:
Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
Invalid object name 'table4'.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> Hi
> select * from master.dbo.sysmessages
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
RAISERROR
>|||Britney,
Please read Erland Sommarskog's article on error handling so you can get a
handle on batch abortion and other niceties of SQL Server error handling...
http://www.sommarskog.se/error-handling-I.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> I tried a test, In this case, table4 doesn't exist in database,
> but how can I print out this error ? I want to set this error to a
> variable,
> maybe to store in in error log table later on.
>
> CREATE PROCEDURE testme
> AS
> select * from table4
> return @.@.error
> declare @.errornumber int
> exec @.errornumber = testme
> print @.errornumber
> --
> result:
> Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
> Invalid object name 'table4'.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> RAISERROR
>|||I don't think I can set severity 16 error number to a variable.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OVFeyUQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Britney,
> Please read Erland Sommarskog's article on error handling so you can get a
> handle on batch abortion and other niceties of SQL Server error
handling...
> http://www.sommarskog.se/error-handling-I.html
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
>
Do you have complete reference for sql error numbers?
I want to design customize error message, I want to know how to RAISERROR
with correct error number. thanksHi
select * from master.dbo.sysmessages
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||Between this and BOL you should be able to figure it all out:
SELECT *
FROM master..sysmessages
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||I tried a test, In this case, table4 doesn't exist in database,
but how can I print out this error ? I want to set this error to a
variable,
maybe to store in in error log table later on.
CREATE PROCEDURE testme
AS
select * from table4
return @.@.error
declare @.errornumber int
exec @.errornumber = testme
print @.errornumber
result:
Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
Invalid object name 'table4'.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> Hi
> select * from master.dbo.sysmessages
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
RAISERROR
>|||Britney,
Please read Erland Sommarskog's article on error handling so you can get a
handle on batch abortion and other niceties of SQL Server error handling...
http://www.sommarskog.se/error-handling-I.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> I tried a test, In this case, table4 doesn't exist in database,
> but how can I print out this error ? I want to set this error to a
> variable,
> maybe to store in in error log table later on.
>
> CREATE PROCEDURE testme
> AS
> select * from table4
> return @.@.error
> declare @.errornumber int
> exec @.errornumber = testme
> print @.errornumber
> --
> result:
> Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
> Invalid object name 'table4'.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> RAISERROR
>|||I don't think I can set severity 16 error number to a variable.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OVFeyUQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Britney,
> Please read Erland Sommarskog's article on error handling so you can get a
> handle on batch abortion and other niceties of SQL Server error
handling...
> http://www.sommarskog.se/error-handling-I.html
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
>
Wednesday, March 7, 2012
reeeeeally easy for you guys!
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.
so that I can edit it to suit my own work like:
eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.
I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)
CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted
insert into tablename2 values (@.value1)
Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.
so that I can edit it to suit my own work like:
eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.
I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,
Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?
The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.
This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table
use northwind
go
CREATE TABLE people
(people_id int)
go
CREATE TABLE Pay
(pay_id int,
people_id int)
go
CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int
select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted
insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go
INSERT INTO people
VALUES (1)
INSERT INTO people
VALUES (2)
INSER INTO people
VALUES (5)
SELECT * from people
SELECT * from pay
DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:
INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
...or any other set based operation?
so that I can edit it to suit my own work like:
eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.
I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)
CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted
insert into tablename2 values (@.value1)
Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.
so that I can edit it to suit my own work like:
eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.
I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,
Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?
The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.
This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table
use northwind
go
CREATE TABLE people
(people_id int)
go
CREATE TABLE Pay
(pay_id int,
people_id int)
go
CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int
select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted
insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go
INSERT INTO people
VALUES (1)
INSERT INTO people
VALUES (2)
INSER INTO people
VALUES (5)
SELECT * from people
SELECT * from pay
DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:
INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
...or any other set based operation?
Saturday, February 25, 2012
Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K
Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
Sam
You can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
Sam
You can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K
Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K
Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
Subscribe to:
Posts (Atom)