Recently my partner in crime (so to speak) and I have been asked to develop procedures and/or methods for our software development group. Currently we're running SQL Server 8 with several Access apps and Crystal Reports. In a nutshell, I'm looking for resources.
We're supposed to develop a process (we're both DBA's) where our development team can formally submit new stored procedures, views, or whatever they've come up with to solve for buisiness requirements. It'll be our job to take thier scripts and execute them on the production database after ensuring someone has tested the logic and signed-off on it.
Unfortunately, I have been charged with developing this process (ie: development, sign-off, testing, etc..). Currently we have no procedures in place; until now my partner and I have been the primary deveopers (this is about to change). Are there any resources out there that could help me develop a formal process for our soon-comming developers?
I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Hi, i installed Microsoft SQL Server 2005 Express Edition Toolkit which contains Connectivity Components, Business Intelligence Development Studio, Management Studio Express, and a Software Development Kit, and Microsoft SQL Server 2005 Express Edition with Advanced Services which contains Reporting Services. What i get now is Visual Studio 2005 where i effectively can create reports. But where are the rest? I mean, how to use the Software Development Kit and the Connectivity Components? I have also installed Management Studio express and Visuam Web Developer 2008; but i don't see any difference after having installing the toolkit. Thanks T.
The web hotel I am using have MS SQL server installed. I do not have it on my local development PC.
How do I develop and test a website, locally, using MS SQL DB, without having MS SQL sever. Is there any light MS SQL sever I can use with a good user interface, and then just upload the DB to MS SQL server?
OK, it isnt the end of the world but i have been moving all SQL databases from one server to another in our development environment and think i have screwed up a bit.
In essence, the users in each database are different to the logins in the security tab. IE, there are users in DB1 that dont appear in the logins list. Is there anyway of getting the security information uptodate? I move the databases by detach|file copy|attach into the nice shiny new server. I am running into all sorts of errors when i try to create users in security as it says the user alrady exists ...
all thoughts welcomed, am steeling myself to start all over again!
wondering what software tools people in the industry use for development of SQL projects.
I use SQL Enterprise Manager to manage the server then a mixture of SQL Query Analyser and MS Access to develop queries for our ASP/WEB projects (using access as an instant graphical front-end to the data).
Used to use a third-party tool to develop the sql code but not anymore as it was just a pain to get done, was called RapidSQL.
look forward to people's replies on this one, any input much appreciated,
Hi I am new to the SQL sever development environment.....I was given a project to work on and the problem looks like this....
I have a student data table and a HR table that loads student data and Employee data into a Master table.First the Student data and HR data are loaded into SISDATA and HRDATA tables respectively.Then any duplicates in the SIS DATA and HRDATA are filtered into a DUPESID table and DUPEEID table respectively.If there are duplicates between hrdata and sis data tables are loaded into the DUPES table.If there was a student who works as a part time employee and is a student then there is a conflict in the master table with the row missing its student id in the master table........pls help me to resolve this.Do i need to look over the DB design........????pls help me.
Trying to figure out what development enviroment we need in order todo the following:- develop a non-native SQL server stored procedure;- call a web service or java program from the stored procedure;- return static values;- call the stored procedure from a view.How do I get a hold of the right tools and what do I need to put thepieces together?Obviously, I've not used SQL server and I'm looking for the basicstarting point.Thanks!
I am about to begin writing T-SQL procedures, I was given SQL SERVER MANAGEMENT STUDIO is this the optimal tool for T-SQL development? I now that oracle developers use specific tools for procedures developemt (PL/SQL DEVELOPER) is there a specific tool for T-SQL developent like in ORACLE?
When I start SQL Server Management Studio from my development PC and connect to a remote SQL-server, I do NOT see any Diagrams. When I use Remote Desktop and connect to the server, and the open SQL, I see the diagrams. Is there a way to get the diagrams to work on my local machine?
Hi,I am trying to create a development database server (make use of an old machine), with which to learn about sql 2005 and oracle etc. I'm using VS 2005 Beta 2 on my development/workstation machine.My workstation and the prospective server are connected via a router and can 'see' each other.I have installed win 2003 server on two seperate partitions (multiple boot) and installed sql server 2005 on one partition and will install oracle 10g on the other. (I understand these two databases can run on the same machine/OS, but I just wanted to keep things tidy and I won't be using them at the same time, so ...).My question is how do I/should I configure win 2003 server / sql server 2005 on my server machine, in order to be able to connect from my workstation via vs 2005 beta 2 ?Any suggestions or resources on configuration appreciated.
we have a situation where modifications to the stored procs and views keep happening in the development databse environ while there is a live production database also up and running.
periodically we need to ensure that the development and the production are in synch where all changes made to dev are transferred to prod. however the entire prod database obviously cannot be overwritten as it contains production data. therefore what we need to do is to transfer all SPs and Views from the development to prodn. ( we do not prefer incremental transfers as there are many SPs and views and we can never really be sure that all changes have been transferred )
the obvious way is to script out all SPs and views in dev and run the scripts in prodn. but what we encountered was the enterprise manager does not script out the objects in heirarchical sequence - ie parent objects first and then the objects that refer the parent objects. this leads to errors when running the scripts as the objects required for the creation of a SP, say another SP run from within that SP , may not have been generated by then as the "contained" SP is lower in the alphabetical order in which SQL server scripted out the objects.
finally we had to look at each and every error and manually set the process right.
moreover in the way we did things the "sysdepends" table got all screwed up and therefore we can no longer depend on enterprise manager to show dependencies within the database.
is there any elegant way of going about this ? we also tried transfer manager in DTS but it also gave the same error. moreover can the sysdepends table be rebuilt from scratch ?
We're trying to work out why the same SQL Script takes much longer to run within the Enterprise environment compared to the Development?
What makes matters worse is the development version is on a standard desktop (P4 1.8 256MB RAM) compared to the Enterprise version which sits on a dual Xeon with 1Gig of RAM.
Is is related to the SQL product or am I missing something here? :confused:
Can anyone suggest good books on exclusively sql server development..Not for the beginners but at intermediate to advanced level.. Books which gives good understanding on Stored Procedures, Triggers, Cursors, Functions...
We have been facing some delevelopment issues with SSIS. These are as follows:
We want to execute some tasks in a particular package depending on a condition. Example: If record count is more than 0 then go to next task else stop the package. We tried to write a script, in which we evaluate the value of the variable. This variable is populated with the record count value. We cannot find any particular function which can exit the package successfully. We can fail the Task, but that means the package is failed. wo dont want to fail the package. In short we want to conditionally execute some tasks in a package. Something like an "If container" similar to "For container".
We have one main package from which we execute 5 other packages. We want to assign values for connection information and variables in this main package only. The other 5 packages should pick values from this main package.
We are scheduling our package from Windows Scheduled Tasks. When the package is run in this manner, it doesnt read values of variables and connections from configuration file. It uses the values that were provided during development which are embedded in the code.
I am running SQL Server 2005 SP2 in Vista. I have Developer editions of everything. I am trying to get BI Development Studio and Analysis Services loaded.
I don't seem to have BI Development Studio loaded. When I go to Control Panel/SQL Server install it says it is already loaded, it needs to be modified. When I try to modify it, it isn't there.
Do I need to run this from the command line with SKUUPGRADE=1 ? Modify the registry?
Second question. Can I get Analysis Services projects loaded into my regular 2005 Visual Studio? How do I install Analysis Services into Visual Studio? Or is it always completely separate, just looks like Visual Studio but can't be accessed from there?
I am looking for advice with setting up a 2005 environment for development, debugging and testing using a laptop as the development platform and a desktop pc as the database engine both connected via a LAN. The current configuration is that all the full blown Visual Studio / SQL development software is installed on the development laptop and can go wherever the developer goes, and the Express versions are installed on the desktop pc so that the development database has an unbroken connection with the network. This setup worked well in the past for developing applications that use a typical database interface. The applications could be completely developed and tested before it went live on the corporation€™s full blown production SQL server. Now we want to utilize the Reporting Services/SSIS in our development efforts and I would like to know what parts of the development software (Express vs. Full) should be installed on what unit (laptop vs. desktop) to fully develop and test using Reporting Services before we move to production? And anything else, like what services need to be running on what units etc.
I'm using visual studio 2005pro. I have SQLE2005 installed but would like to use SQLCE as well. Will they co-exist/play nice on my development machine? Any special install considerations?
I could use some help from those of you who have SSIS deployed in a production environment. The developer creates a SSIS package on a workstation with BIDS. Package accesses a network drive and a SQL database on a development server. The developer can test the SSIS package successfully in BIDS.
The only way to use the ServerStorage protect level is that the developer needs to save the SSIS package directly to the development server (we are storing the package in SQL Server). This is not a problem, but it possesses a problem when we need to migrate the SSIS package to the production server. This is because in development our databases are suffixed with the word test. Also, the SQL Server user id that has access to the tables has a different password in production. We run our SSIS packages with the SQL agent. The SQL agent id has permissions to the network drives; the connection information within the package has permissions to the SQL tables.
How can the DBA, who would ideally does not want to and may not be able to due to audit restrictions, update the server name, user password, and database name without using BIDS? I tried using the configuration file option, but that does not seem to work. It keeps deploying the XML configuration file to my local machine even after successfully migrating the package to SQL Server.
To me, the DBA should simply need to run the deployment wizard and target the production server. At the time of the deployment, the wizard should prompt for items that need to be updated, and the settings should be stored in SQL Server. I know this scenario sounds exactly like those presented in the docs, but I must be doing something wrong.
In the past I wrote DTS transforms entirely by myself. With SSIS, our team of several developers now wants each member to develop a piece of the same package.
Do SSIS packages support this type of simultaneous multi-developer creation or is it a "one developer at a time" type product?
Hello, I have my local development evrioment and my live webserver in colo. The two different machinces both have SQL2005 and copies of roughly the same database. I use the one for developement and the other obviously for production. Currently I read in different connection strings from my web.config file for my live version v my development version so I can have different database names and connection strings. I'm looking at moving to LINQ, but I can't figure out how I'd achive an equivelent flexibility as database names and connection strings seem to be hardcoded in to the class using it's designer. Obviously if modifing this wasn't possible, nobody would be using LINQ, so how do I work around this? Thanks, Joel Barsotti
I need to move an entire live environment from MS SQL 2000 (because of developing further before uppgrading) to SQL Express but cannot get it to work. Is this possible to do at all?
I am running both SQL Server 2000 and SQL Server 2005.
Lets say I have a live database that is version 1 for an application. The database is copied onto a development machine to develop version 2 of the application. I want to be able to take a snapshot of the database and then get a program to write all the changes made after the snapshot into sql code. This is so I can run this sql code on the live database and transform version 1 to version 2.
Is there any program/tool that will allow me to do this?
I have a question about how to efficiently develop on a development version of a database and get those changes over to the production database without loosing live data, etc.?
Here is how we are trying it:
1 Export production database to temp database(exact copy) 2 Delete production database 3 Export development database (definition only, no data) to new database with production database's name 4 Export production (temp) database (data only) over to new production shell
We are having truncate errors due to FK constraints.
Need Recommendation on Tool for SQL Server Development
I have inherited a SQL Server 7 database (actually a SQL Server 6.5 database running on SQL 7 in compatibility mode) with 1000s of objects with triggers and stored procedures with zero documentation; i need to make major changes to this database; are there any tools available that will allow me to quickly search through the database objects and code (like stored procedures, etc) for keywords and other useful criteria? Do you recommend any SQL Server specific tools that will help me learn this database in the shortest amount of time?
I have a problem in that I have a live SQL 7.0 server as a back end to a web server for a large company in Germany and of course we also have a development server locally which is more or less the same apart from data. The ASP developers here want to upload some data to the live server and also download live data from the live server so that we have more or less the same data on both systems.
I have thought about doing this with DTS adn writing SQL scripts to merge the tables and then upload back to the live server etc. but before I do I wondered if anyone had experience of doing this before (I am sure) and could either tell me what are the best methods or package to do or point me or mail me an article that covers this sort of thing.
Of course extreme paranioa creeps in at the thought of copying down large tables from the live server etc. etc. so if anyone can help - great!
Wondering if anyone has pro's and cons for choosing a vehicle to build front end applications for a SQL Server 2000 back end.
Historically we used Access 97 with VBA code to create client side ODBC connections. In Code we would issue stored procedures through a connection string. This worked fine.
With Microsoft moving onward our company is transitioning to supported products. So DAO is dead.
My colleagues like Access 2000 and have imported some 97 databases doing small re-writes to make them work.
Everything I read says we should be interacting with SQL using ADO.
The other problem is not all users have admin rights on their machines, so adding application extensions for VB6 is a problem. We might be able to change this.
Should we just build with Access 2000 from scratch using ADO to avoid JET?
Should we use VB6?
Is there another application we should be looking at?
Is VB.net the most logical choice of a RAD tool to use with MS SQL Server? Is VB.net strictly for web apps or can you use it to create projects that run as executables off the server?
