Hello Everyone,
I have a SQL 6.5 database that is about to grow beyond the size of its current volume. I have 3 volumes of 20GB each, 2 of which aren't being used. What do I need to do to ensure that I can expand the device across multiple volumes?
Thanks in advance for your help,
Terry
I am creating a report that contains a table. The table only has one column containing 10 characters so there is a lot of wasted space on the page. I would like the columns to wrap to at least two columns on each page. I have tried to set the number of columns on the report properties to two, however it seems as if the second column is disabled and the data does not wrap into the second column when the report is run.
Any suggestions would be GREATFULLY appreciated!
Thank You
Kaysie
I have a report that returns multiple tables in a group. I'm grouping by project manager which has multiple projects, each project contains data in a table. My problem is that when I print, my tables are being cut off so that the headers and first few rows are on one page and the rest of the table and totals are on the next page. There was a command in access that would allow me to keep my tables together so that if a table wouldn't fit on a page it would move the whole table to the next page. No table is larger that a page, but I'll get 2 and 1/2 tables on page 1 and 3 and 1/3 on the second page, I'd like to have 2 on the first page, move table 3 to page 2 and move on from there. Is this possible?? Thanks for any help you can give me.
View 3 Replies View RelatedIs it possible to create a view that spans over 2 servers? It uses 1
database from the first server and 1 database from the second. I don't
know how to specify the location of the second database. If I put
'Server2.MISProd.dbo.work_orders', I get an error that "this object
contains more than the maximum number of prefixes. The maximum number
is 2"
I do have remote servers set up so that from each server, I am able to
see the other. Is there any way to work through this?
I would appreciate any thoughts. Thanks!
Toni
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
How do you add a table to a report that will span over two columns before going to the next page?
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
We have our production SQL 2005 server (64 bit Standard Ed.) attached to an iSCSI Equalogic SAN. We have set up 2 new servers an installed the cluster service. My question is: can I install SQL 2005 in this cluster environment an latter on disconnect the data and log volumes from the production server, attach those volumes to the cluster an reattach the DBs? the reason we need to do it like that is that we don't have enough spare space in the SAN to initially create these 2 volumes in the cluster.
Any ideas/suggestions would be greatly appreciated.
Soon, I will be migrating SQL cluster volumes from our old SAN to the new one. I have an idea of how to do it, but I just wanted some feedback. Yes, I know the best way would be to set up a new cluster using the new SAN and migrate the DB, but unfortunately I don't have that luxury. Here's my plan...
Add new storage to cluster, ensuring the drives are active cluster resources and dependencies match old resources
Back up DB
Shut down SQL Server
Copy all files & folders from old storage to new storage
Reassign drive letters to make new storage match old configuration
Start SQL Server
In theory, I think this will be fine because as long as SQL sees the correct drive letters, it should function properly. Just concerned about the quorum portion of the cluster.
Thanks!
Kolby
We have our production SQL 2005 server (64 bit Standard Ed.) attached to an iSCSI Equalogic SAN. We have set up 2 new servers and installed the cluster service. My question is: can I install SQL 2005 in this new cluster environment an latter on disconnect the data and log volumes from the production server, attach those volumes to the cluster an reattach the DBs? the reason we need to do it like that is that we don't have enough spare space in the SAN to initially create these 2 volumes in the cluster.
Any ideas/suggestions would be greatly appreciated.
I'm looking at using Cluster Shared Volumes on a new Windows Server 2012/SQL Server 2014 cluster. Each instance is going to be configured to use cluster shared volumes. Is there any reason why Availability Groups couldn't be used in conjunction with Cluster Shared Volumes.
View 4 Replies View RelatedI have an existing database with approx 500,000 rows and accessed by afew hundred users per day creating approx 1,000 new records per dayplus typical reporting - relatively low volume stuff for SQL Server.I'm about to add a process that will be importing data daily fromlegacy databases and summarizing it for reporting purposes, integratingit with the existing database. This volume of data will be considerablyhigher, perhaps 100,000+ rows per day, which will be deleted once ithas been summarized and the results written to some intermediatetables.Is there any concern about mixing different levels of volume within onedatabase? As I'll be creating lots of rows daily and then deleting themI was wondering about fragmentation, transaction logging etc. andwhether having this processing in a separate database from the mainapplication would be 'better'.
View 3 Replies View RelatedHello all,
I have recently been task with rewriting a database that holds large volumes of data, whilst ensuring that query can be run in optimal time. Having never really delved into this sort of thing before, I hoped you guys might be able to offer some advice and guidance.
The design I have inherited is based around 2 main tables:
[captured_traps]
[id] [int] IDENTITY (1, 1) NOT NULL
[snmp_version] [int] NULL
[community_name] [varchar] (255)
[packet_type] [varchar] (50)
[oid] [varchar] (500)
[source_ip] [varchar] (15)
[generic] [int] NULL
[specific] [int] NULL
[time_stamp] [varchar] (15)
[trap_entered] [datetime] NULL
[status] [int] NULL
[captured_varbinds]
[id] [int] IDENTITY (1, 1) NOT NULL
[captured_trap_id] [int] NOT NULL
[varbind_oid] [varchar] (500)
[varbind_text] [varchar (500)
The relationship between the two tables is on the "captured_traps (id)" to "captured_varbinds (captured_trap_id)". Currently the "captured_traps" table contains around 350 million rows, the "captured_varbinds" table contains around 900 million rows.
Now as you can probably gather this model runs like a....well it sort of hobbles more than runs hence the need to redesign.
My current thoughts on this are:
- Normalising all varchars - there is alot of duplicate values in most of the varchar fields.
- Full Text Indexing
However beyond that I am not sure which route to go down. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is fatally flawed somewhere.
Newbie here. I need help setting up volumes so I can do practice exercises for exam 70-431.
What I have on an XP Home edition personal computer are my C volume which is 300+ GB, a NTFS basic, and my CD/DVD ROM on D.
What is the code I use at command prompt to do this or can I do it from Disk Management?
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
I have a database that contains a few tables such as person, staff, member, and supporter. The person table contains information about every staff, member, and supporter. The information it contains is name,address,email, and telephone. I also created an id that is the primary key. My issue is that I also have an primary key ID for staff, member, and supporter.
For instance, in the person table is John with id 1.He is a supporter so in the supporter table is pID(for person id)to reference back to John with all his information and ID(for supporter ID).pID references to the person table and every person has an ID incremented by 1 starting at 1. supporter ID is for every supporter and also starts at 1 and is incremented by 1.
Is it possible to have in the supporter table pID = 1 and supporter ID = 1? Another person may have a pID = 26 and supporter ID = 5. Or will supporter ID have to be different than the pID and be something like "sup"? So you would have pID = 1 and supporter ID = sup1 or pID = 26 and supporter ID = sup5
I am trying to display data from two databases.
in case of one database we can join multiple tables.
But how to join tables in multiple database.
I have a SQL Server 2005 Express database that was designed to be used by one client. What is the best way to change the design so it can contain multiple clients that can only see data entered by users of each client organization?
Also I'm using the asp.net membership database to handle login and profiles. Can this be used with my multi client database?
Hi Forum,
Can a DataBase have more than one DBOwner?
If so, is there a query to return the list of DBOwners(dbo)?
Regards,
Naveen
hi all~i very confuse~i using mssqlserver 2005 in visual basic 2005express..but i feel like want to update two table at onces..i faced some problem~i give an example..my tableA got user id, user name and table B got user id and status.In both table, i put user id as primary key and it will auto link each other and i retrieve the user id and user name from table A and user status from table B~..in order to come out the data in gridview with three columns which are user id, user name and user status..in this case, it works perfectly..but when come to update part, i only able to update one table at once, when i trying to update both table, it occurs errors...i did read some forum, some said it is impossible to do it?isn't?then is dere got any other ways to make me update both table at once?my purpose of this is wish to update user id and user name from table A and user status on table B....~i guess maybe my database design concept got problem..hope someone giv help on these... by the way izit foreign key can solve this?and how to put the foregin key?
View 4 Replies View RelatedHello EveryoneI am in the process of creating a web application that will allow me to have one place to go to see all of my SQL Databases. I would like to have one of the pages that showed the current status, or maybe next to the name of the server, have the status listed.I know how to create a database connection string using the web.config But I will need to have a query that loops thru a list of database server names, and each of them returning the status. Being able to connect, query the status of the server, show the status of each database. My database connection string will only log into one database. How can I code this to be able to loop thru the list of stored database server names, and then be able to log into each database server?Can I use a separate class with a method, and pass in the name of the server? That means that I would have to have the same user name on all the database servers, wouldn't I ?I am looking for a way to accomplish this. I am open to suggestions. I am coding my web app using C#. ThanksAndrew SQLDBA
View 2 Replies View RelatedI am planning to develop reports using reporting services. My reports will use data from 3 different SQL Server databases that are application specific on three different cpu's. I'm planning on having a reporting database that is a common repository for data extraction / summarization from the other 3 databases.
I'm trying to understand the best way to approach my design (it's been a while since I've done database work - pretty obvious...):
Use Analysis Services to move the data from the three databases to the reporting database server.
Create some stored procedures that can do this.
I'm not seeing the reporting services as being a "data movement" tool but more of a shell data access tool (correct?).
Other tools someone would suggest.
Thanks and any help would be appreciated.
Is it possible to connect to 3 database and execute a cross db query?I usually use SqlConnection(connectionString) for a single connection
View 1 Replies View RelatedUsually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?
View 11 Replies View RelatedGood afternoon one and all,
I want to write a query that will affect data across two databases (on the same server). How would I do this?
TIA for all help
Gurmi
Good Morning to Everyone :-)
We're having DB Design discussions in our office for a new project. Basically, we're trying to decide on a DB Solution that allows us to manage multiple online communities. We expect to have several hundred thousand users (about 100k per community), and each user will have associations to user-specific content on the community level.
At some point, we expect to have to export one or several communites into their own Datasource. This will be a downstream event that will not affect us in the immediate future.
In terms of administering the DB and each community, we're having trouble deciding between generating this DB in one inclusive datasource vs creating multiple datasources (one for each community). With that in mind, any advice or suggestions that anyone may have on this subject would be appreciated.
FYI -- We're running CF v4 and MSSQL v7.
Our concerns are as follows:
Data Structure Issues (what abilities/SQL functions will we lose when implementing multiple DB's vs one DB? We definitely have a need to be relational at every level of this application.)
DB Performance (will a one DB solution run more efficiently on MS SQL v7 with high traffic than several DB's?)
Administration (It's our understanding that multiple DB's will cause us to lose our ability to perform table joins, etc. Are there any other shortcomings that we should be aware of?)
Stability Issues (We've heard that one DB vs Multiple DB's will have fewer threads. We've also heard that MS SQL v7 becomes increasingly unstable as more threads get created. Is this true, or is our understanding of this issue wrong?)
CF Integration Issues (How will CF v4 react to handling a large amount of high-traffic DSN's vs. having one DSN and the same amount of traffic?)
Thanks in advance for all your help. We look forward to hearing from you.
Regards,
Abraham Lloyd
Senior Developer
Compbiz.net
Hello,
Can anyone tell me if SQL Server 6.5 has anything similiar to linked tables in MS Access? I need to query two tables simultaneously that reside in separate SQL databases. Is this possible, or must the tables reside in the same database?
For example, I have two databases db1 and db2. Table1 is in db1 and Table2 is in db2. I want to query something like this:
"SELECT * FROM table1 WHERE fieldname1 in (SELECT fieldname2 FROM db2.Table2 WHERE...)
Thanks,
Andrew
Hello All,
I want to know if it's possible for a database on multiple devices to put specific tables on specific devices?? and if this is possible how to do it??
Thanks for help
Mohamed hani
Dear All,
Is there any feature in SQL Server 2000 through which I can translate my Database into different languages at runtime.
Thanks & Regards
Anoop Singhal
Oracle Certified Professional
Team Leader (Database)
KeyWest Business Systems (P) Ltd.
Ph. 0135-2710533,2710511
Cell: +91-9412051028