I am implementing an application that is looking into another System’s database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.
Hello all,I have the following requirement (SQL 2000, SP2)1. Need to restore production database(A) to another database (B) onanother server (No direct connection)2. In the restored back-up(B) SP codes should not be visible (even tosa) or all SP's can be deleted .3. This needs to be carried out daily4. New tables may be added frequently to A5. This job should be scheduled6. I can overwrite the entire database (B)7. I can not encrypt SP in the original database (A)I thought of Snap-shot replication first. But when new tables areadded , I need to add new tables manually to publication .Can some one suggest most reliable and implementable method ?ThanksSrinivas
Some of our databases have many transactions (a million or more) a day. I have read that every so often I need to rebuild indexes, update statictics for all tables (however that is done), and shrink the transaction logs.
I'm confused by all this. What are the daily recommended database maintennace steps steps for database "health" and how can they be done?
I would like to use SQL Server's built in database snapshot feature. We have a database (e.g., ThatDatabase), and I would like to capture a snapshot of it at exactly midnight each morning. I would need the snapshot to have the same name each day (e.g., ThatDatabase12AM). My non-technical end users (statisticians with SAS) are going to use the snapshot a lot, but are not going to be able to handle it it having a different name every day.
Obviously, I could set up a job with a start time of 23:59:50 that drops the existing snapshot and then creates a new snapshot. This would have the problem that the snapshot name disappears for a few seconds every day. That would either a) kill user sessions, or b) block the snapshot drop and delay the new snapshot until after exactly 12:00 AM.
I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.
How can I do that. Does someone have any script which can help me to do that.
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping Snapshot Replication Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
Hello.. I want to upgrade my website to asp.net 2.0 and I want to add a Daily news to website.and 10 to 15 news will be added daily , so after a year we have almost 4000 entry in the database. For designing this DB what is your advice on how to store news in database?Is it better to create same tables for each year ? for exampletblNews_2005tblNews_2006 Or just to make one table and have all news stored in that ? its gonna be huge after years , isnt that make any problems? ThanksRegards.
On our particular database server, we run the Rebuild Index Task (Using classic Maintenance Plan Designer) every night. Running the script below, I saw that about 77 tables had an avg_fragmentation_in_percentage between 80% and 99% !!
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want ORDER BY indexstats.avg_fragmentation_in_percent DESC
I dont understand why these tables are highly fragmented after a daily index rebuild! Unless the users are doing heavy inserts/updates/deletes during the day.
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
I need to query a database with about 3 million records using a 'like' statement on various varchar columns. I've have a non-clustered index on the columns included in my query, but the performance is still pretty slow. Can anyone suggest ways to increase the speed of my queries? Does full-text indexing improve performance or does it just facilitate more advanced searches (which I don't need)? Would char instead of varchar datatypes be faster?
Hi, I have been using Microsoft Access for a couple of years and we have just switched to SQL Server. However, I'm having problems querying the Mysql databases our company has in place. I have managed to establish connections in the Visual Studio but can't seem to link tables from two different databases like I could in Access. Is this possible? Also, is it possible to connect to the mysql database via management studio? Apologies for my complete lack of knowledge!
Hi everybody, Our SRS reporting security model had to be designed so that the access is by individual report basis and by user. I cannot give access to individual reports based on windows AD group. Because everybody in that group does not need to have access to a particular report. Two people from one AD group needs access to say Report1. Only one person need to have access to Report2 etc.... Creating lot of AD groups for the security is also not advisable.
Right now I can tell right away users having access to a particular report as the number of reports and the users is small. But I want to design a report that can give me a list of users who have access to a specific report. How do I query the reportserver database to get this information. I mean is this information saved in any of the tables? Can you please give any info that you know of such information? Thank you in advance for all your help,
I'm in a trouble. So, I need to query some data in DB2 with Reporting Service. I don't know if there's a way to do it, without using DTS Package, because if I did it, I'd be replicating it, and it's not the case. 'Cause I just want to query the data in DB2 and not bring the data to SQL.
There's a provider that I can do it? Suppose I use a Linked Server, and add a Net-Library based on their Operational System, it would resolve? If not, what would be the best way of resolving this trouble??
Hello,I need to come up with a stored procedure that will allow me to readdata from another database. The database I need to read the data fromis a UniData residing on a Unix server. The stored procedure needs toreside on my sql 2005 server. The task is very simple in Access as wehave ODBC connections set up to the UniData via Informix (or IBM)UniData ODBC drivers. I can easily combine my UniData and Sql Servertables from within access. However, I can't seem to find a way toreplicate the same behavior in MS SQL Stored Procedure without the useof Access. Is that even possible?Thanks,Marek
to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:
SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)
I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.
I created an Image for the Database in sqlserver 2000. When I am querying directly the database as "Select * from employee", it is returning the result set.
But, when I am querying Database image with same query. It is giving error
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'Employee'
It is expecting the Database Owner. If I do query like "Select * from [Owner].employee", it is working fine.
My requirement is not use owner in queries, it should be simple query ("Select * from employee").
Can any one, help me out. Thanks in advance. Bhupesh
Is there any way to deploy reports to a Report Manager in SQL Server Reporting Services 2005 if the reports query SQL Server 2000 databases? Or are only 2005 databases able to deploy to the web?
Also, if I have a report that queries both, can I deploy it to the web on the 2005 Report Manager with a linked server to the 2000 server?
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.
helloI have two related tabletable1cIdcDesctable2IdNamephonecIdThese two tables are relatedwhere cId in table 1 is primary key and cId in table2 is foreign keyIf I want to delete a record in table 1 which is related to table 2Which is faster and more accurateshould I write my stored procedure like thisif exists(select * from table2 where cID = @CID)delete from table1 where cID = @CIDelsereturn 0------------------------------------------or------------------------------------------delete from table1 where cID = @CIDif @@error<>0return 0
I just want to ask one question and i am looking for advice or recommendation.
I want to make an application using windows application in .NET. this application is only to have one or two users to enter details of clients on the database using the application and it will be a search for client name or number and other search methods which is similar.
My question now, what database shall i use for this kind of application SQL server or MS Access. i am looking for a new product to use as the cleint can not afford expensive software. Please advise!! thanks!!
Version: SQL Server 7.0App: MS Access 2000I created DTS to move records from MySQL to SQL Server (approx over 500,000+per months dataset) for purpose of canned and adhoc reporting. The apphave a simple interface using MS Access where user will input the timeframeof the dataset they will process.Both servers are physically located in the same bldg from another state.Previous to MySQL, they send us cd in text format and my app runs the DTSusing a BAT file containing the DTSRUN command. But I would like stay awayfrom the DTSRUN since they take awhile to load to the server, plus it runson background on client pc. I wanted a functionality where it will run onthe server without user supervision, and they can go about their businessand do something else. In other words, let the server do majority of thework.Now that MySQL came into the picture, the Network Support created a filedsnwhere sqlserver sits and I use that as a reference on my DTS (using UNC,\myserverfile$sample.dsn)The problem:1) I work from home and when creating and running this DTS's, it takes awhile to process;2) When specifying the UNC path on DTS and saved, for some reason itswitches to the User/System DSN section;3) I also tested from office with the same result (i connect using cablemodem)Now the only way I can think of to solve this problem:a) save the DTS as a file and send it over to Network Support and have themload it up and manually change the source location OR have them modify theDTS and change the source locationb) then create a DTS jobThe person who will be running this DTS might be running it from home oroffice. Also, is there a way to automate the DTS job using an SP? I wantedto make it as easier as possible for the end user, where all they have to dois enable the job and run only for this time and for this package.TIA!Bob
on the 1st opening of my web page it uses the membership control of asp.net for logging on. sometimes it takes time to logon and worse is, it will encounter sql server error. that can be resolved by just restarting that specific page and it will be alright, it will logon normally. this only issue happens only on the first opening of the logon page. i have read a certain article and it says there that on the first time you access the sql server, sometimes the sql server needs a little more time communicating back and forth. how can i make my log-on page wait a little longer till it communicates or finishes communicating to the sql server? im afraid that this may happen once i deploy my web project. i am expecting over 100 users and they might save to the database all together. will i encounter problem on this particular? i am using ASP.NET 2.0 and SQLExpress for this project. pls advise me. BIG THANKS.
I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization. Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes? I appreciate any suggestions. tblOrg (OrgID) tblDocuments (DocID, OrgID) tblNotes (NoteID, OrgID) tblContact ???
I would be appreciate to hear some advice regarding my mission to upgrade from SQL Server 6.5 to 7.0. The database would still be located in the same server. Do I have to do checking on the datatypes? - many thanks-
I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.
My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.
Have found this two books in Amazon:
1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition
2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition
However their rating didn't encourage me to place directly my orders.
Does someone have any experience with them ?
Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?
Do you know some others books you woul rather encourage me to buy ?
HiWe usae Sql2000.I'm setting up backups jobs and need advise.When I run a backup of a database, does it automatically clean out thetransaction log ? For a complete backup ? for a differential backup ?When would it be best to run a shrinkdatabase command ? Why would I wantto run the shrinkdatabase ?ThnaksDavid Greenberg
I am very new in SSIS. I have the Report Server set up with two dozen of the OLAP reports in it. The reports are connected to the OLAP database on a seperate computer running on Sql 2005 server and the data source for the OLAP DB is pointed to the warehouse database on another computer running on Sql 2000 server. The warehouse server updates daily from a number of "Production" Sql servers running on Sql 6.5 and Sql 7.0 with the legacy front-end data entry application. I know the update from the "Production" Sql 6.5/7.0 servers to the warehouse server complete around 9am in the morning everyday. How do I set up a package in SSIS to schedule the update of the OLAP database say 9:30am so that end-user can have the latest data to their report. The OLAP database has one fact table, half a dozen of cubes and ten dimensions. I know it is not an easy task to set up the package in SSIS. Please provide sample and step on how to do this. Thanks. One last question how to prevent/warn users from opening the report when SSIS updating the OLAP database?
I have a dimension table that, aside from its surrogate key (which is auto-generated using an identity column), has only one varchar column. I'd like to use an SCD Transform to insert only new output in the dimension, but this won't (?) work because I have no business key (the single varchar column comes from the SSIS pipeline), so my question is: how should I approach this problem? Is the design incorrect (e.g. should I generate the SK in the pipeline and use it as a business key?!), or am I doing something wrong in SSIS?
I am an Application Developer. I know just this about T-SQL : Insert, UPDATE, delete aRecord. JOIN tables. Create a Hierarchy column. execute MERGE Statement. [in order to create IDENTICAL tables .]
Create a Master-detail TABLE . [PrimaryKey-Foreig-Key]
WHAT ELSE SHOULD I KNOW widly speaking about T-SQL ??
What are ESSENTIAL things you should know to Be a BIT of an EXPERT ???
