Article On Speeding Up Your SQL Server Development With Easy To Use Tools
Feb 23, 2004http://www.aspalliance.com/349
View 1 Replies
ADVERTISEMENT
Tools Used For SQL Development?
Sep 20, 2004wondering 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,
SQL Development Tools
Feb 1, 2008Is there any way to utilize the 05 development tools without remoting into the 05 staging server?
View 4 Replies View RelatedSuggestions On Front End Development Tools
Jul 20, 2005I'm getting ready to start a fairly large application to be hosted onSQL-Server. In the past I have used Access 2002 (as a front end, wasn'thappy with the number of bugs I ran into), PowerBuilder 6.5, Oracle Forms 7years ago (also no happy with the number of bugs). I was curious what thestate of the art was these days and what current recommendations were for4GL type of development tools.Thanks,Doug Broadwell
View 5 Replies View RelatedSQL Tools :: How To Disable Customer Improvement Program From Menu For Server MGMT Tools 2012
Mar 25, 2014Am customizing SQL server MGMT tools 2012 for Mass deployment.Client had asked to remove Customer Feedback option from help menu.how to disable that.
View 6 Replies View RelatedDesign Tools / Sql Server Mgt Tools Cause Login Fail
Sep 20, 2007Login failed for user 'TOSHIBA-USERASPNET'
I know that the file persmission for the web application have to include aspnet, so i keep resetting the folder permission for aspnet in file manager, but the login failed keeps coming back every day or two
problem is after working with VS05 Pro, SQL Server Management Studio CTP, somehow the aspnet persmission get changed, use alot of sqldatasource wizards and often there is a conflict/hang between the datasource wizard and the need to have the mdf in a dettached state within VS server explorer,
not sure but the procedure to fix this seems to be to reboot, detach and re-attach the mdf in the Sql server Studio tool, re-apply the aspnet file permission on the web app folders (wonder should i be doing this in IIS instead), make sure the mdf within server explorer is detached, the it works
anyway, getting real tired of the resulting delays and design time derailment, clues greatly appreciated, thanks
sometimes i can use View in Browser when in VS05 form view and i wont get the aspnet folder permission error and other times i do.
last thing, is it a bad idea to give aspnet full permission for the entire web applicaiton??
Article On Advantages Of Sql Server Over VFP
Oct 9, 2007hi guys,
im new to sql server...
i am currently studying sql server 2000
i know this subject has been asked before...
i would just like to ask if you know some related articles regarding advantages of using sqlserver over vfp dbf/dbc?
i am going to discuss it to my class.
thanks very much!
any help is very much appreciated!
Joel
Server Tools Or Client Tools?
Jun 16, 2008Dear All,
how can we know that wether we had server tools or client tools on my machine?
Arnav
Even you learn 1%, Learn it with 100% confidence.
Speeding Up Inserts
Jul 23, 2005Hello everybody,Just short question:I have tables, which are only log tables (very less used for selects),but there is a lotof writing.I would like to have as much speed as possible by writing data intothis tables.create table [tbl] ([IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Time_Stamp] [datetime] NOT NULL ,[Source] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,[Type] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,[MsgText] [varchar] (512) COLLATE Latin1_General_CI_AS NULL ,CONSTRAINT [tbl] PRIMARY KEY NONCLUSTERED([IDX]) ON [PRIMARY]) ON [PRIMARY]GOQuestion:Is it better for inserts,, to remove PK but leave identity insert?How to make this table optimized for writing?If I will set fill level of the table with 0%, will I winn much?Once information: this table will be deleted with old data, dependingon row count (oldest ID's will be deleted each night).Thank You in advanceMateusz
View 2 Replies View RelatedSpeeding Up Inserts
Mar 29, 2006according to the mysql manual, multiple inserts can be sped up bylocking the table before doing them and unlocking the table afterwards.is the same true of multiple inserts in mysql? if so, how would thetable be locked?any insights would be appreciated - thanks!
View 3 Replies View RelatedSpeeding Up Dynamic SP
Jul 20, 2005We have a dynamic SP that dependant on a user name will run a selectedtailored to them.One of the criteria is the number of rows retrieved, which we include using'top @varNoOfRows' in the dynamically created select .As long as the number of rows is less than 130 the SP runs in less than asecond, but if you increase that value to over 150 the SP times out.It is being run from ASP in this way: DBCon.execute(SQLQuery)The main table that we are querying contains about 1.5 million records andis un-indexed. (eek - didn't realise that until I just checked) on SQLserver 2000.Does anyone have any pointers towards streamlining the SP - I can post it ifyou wish. Or can anyone explain how to use the execute plan to ouradvantage?I've already used it to change aSET @statement2 = (select T1_QueryPart from Table1 where T1_ID like (SELECTLoginTable_T1ID from LoginTable where @username = LT_UserName))toSET @T1ID = (SELECT LT_T1ID from LoginTable where @username = LT_UserName)SET @statement2 = (select T1_QueryPart from Table1 where T1_ID like @T1ID)But would , say, a join be more time efficient?Any help would be appreciatedJohn
View 3 Replies View RelatedHigh Performance SQL Server (article)
Jul 23, 2005I posted a link to a prior article in here, that one about highperformance hierarchies, and have the first two parts of a new series.Hopefully this is of value to someone.http://www.yafla.com/papers/SQL_Ser..._sql_server.htmThanks.
View 1 Replies View RelatedSpeeding Up Permalink Procedure
Apr 14, 2008I have a forum topic, that has comments. On the homepage, a widget shows the most recent 6 comments across all topics. Some of these topics have 7000+ comments. On the actual topic page, the comments are paged, 10 records per page. In the widget, if the user clicks on the comment, it should take them directly to the comment, and the page it is on. (The most recent comment is on the last page). So, to link it would be e.g. linktoforumtopic.aspx?p=177#commentID=999To get the page number the comment is on, I would have to return all the comments(7000+), get the rowindex of the comment, and figure out what page it is on depending on the page size. This all works, however it is extremely slow. Can't think of a better way....DECLARE @RowIndex decimal DECLARE @PageIndex int SET @RowIndex = (SELECT [RowIndex] FROM @Results WHERE CommonID = @BlogCommentID) SET @PageIndex = 1 IF(@RowIndex > 10) BEGIN SET @PageIndex = CEILING(@RowIndex / @PageSize) END SELECT @PageIndex AS PageIndex
View 1 Replies View RelatedSpeeding Up An Index Rebuild....
Dec 19, 2000Hi all...
I have a table with over 60 million rows (approx 20GB) which has an indexed column. I have tried using DBC DBReindex to rebuild the index, but after kicking it off on a friday, it is still running the following wednesday. Since managers and other finicky types access this database, that's not acceptable (it slows down their reporting).
Is there a way to speed up the reindexing process? Perhaps by adding space to the tempdb (it's 500MB) or putting it in RAM temporarily? I haven't seen any articles that specifically state that TEMPDB is used during an index rebuild, but it seems logical that it would be.
Any suggestions to speed up the process would be most appreciated!
Speeding Up SQL Query Time
Jul 20, 2005Consider this SQL Query:-----------------------------------------------------------------SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,c.QUEUE AS Expr1, x.CHECK_ACT_NBR, c.CUST_LN, c.SSN,c.CREATE_DTFROM (SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,SUM(cast(TRANS_AMNT AS float)) AS TotalLoss,glQueue.REFN_NM AS Queue,glStatus.REFN_NM AS Status,grRegE.REFN_NM AS RegE, grType.REFN_NM AS Type,grFraud.REFN_NM AS Fraud, cl.CHECK_ACT_NBRFROM (((((T_CASE_LST AS cl LEFT JOINT_INCIDENT_LST AS il ON cl.CASE_NBR = il.CASE_NBR)INNER JOIN T_GNRL_REFN AS glQueueON cl.QUEUE = glQueue.REFN_NBR)INNER JOIN T_GNRL_REFN AS glStatusON cl.STATUS_CD = glStatus.REFN_NBR)INNER JOIN T_GNRL_REFN AS grRegEON cl.REGE_CD = grRegE.REFN_NBR)INNER JOIN T_GNRL_REFN AS grTypeON cl.CASE_TYPE_CD = grType.REFN_NBR)INNER JOIN T_GNRL_REFN AS grFraud ON cl.FRAUD_CD =grFraud.REFN_NBRWHERE (((glQueue.REFN_DESC) = 'Queue')AND ((glStatus.REFN_DESC) = 'STATUS_CD')AND ((grRegE.REFN_DESC) = 'YesNo')AND ((grType.REFN_DESC) = 'Fraud_Code')AND ((cl.STATUS_CD) = 0))GROUP BY cl.CASE_NBR, glQueue.REFN_NM, glStatus.REFN_NM,grRegE.REFN_NM, grType.REFN_NM, grFraud.REFN_NM,grFraud.REFN_DESC, cl.CHECK_ACT_NBR,cl.SSN, cl.CREATE_DTHAVING (((grFraud.REFN_DESC) = 'YesNo'))) xLEFT OUTER JOIN T_CASE_LST c ON x.CASE_NBR = c.CASE_NBR-----------------------------------------------------------------1. Is there anything that can be done to speed up the query?2. This part of the query: ... AND ((cl.STATUS_CD) = 0 ... where the 0is actually a variable passed in via a VB application. 0 would be newcases, and normally return around 4000 - 5000 records.3. The SQL server, Web Server, and users, are all in different states.4. The time to return this query where cl.STATUS_CD = 0 is about 7 -12 seconds.5. Is this a reasonable time for this query? What can be done toincrease the time?6. The SQL server is indexed on T_CASE_LST.STATUS_CD andT_INCIDENT_LST.CASE_NBR, but not on any field from T_GNRL_REFN sinceT_GNRL_REFN is only a general lookup table, and contains less than 50records.7. I've built the query as a stored procedure, and it works, though nomeasurable speed increase was obtained.8. I have not attempted building a view to aid this, as I don't seethat helping... or will it?9. Well: any ideas?10. I would gladly rewrite the SQL Query if it could return the samedata faster using another method.11. Is there a way to accomplish the joins involved with theT_GNRL_REFN in another manner to make it quicker?12. Is there a better way to add the values in T_INCIDENT_LST than:.... SUM(cast(TRANS_AMNT AS float)) AS TotalLoss ... ?13. I don't care if its pretty, I just need it faster.14. How can I get the summing of T_INCIDENT_LST.TRANS_AMNT without aderived table...? I know that using the derived table is slowing itdown some.**** Any Ideas ****David
View 3 Replies View RelatedSpeeding Up Store Procedures Using EXEC?
May 26, 2004Hello, can anyone offer any advice on this problem related to store procedures.
The following 2 chunks of SQL illustrate the problem
--1
declare @lsFilt varchar(16)
select @lsFilt = 'fil%ter'
select * from sysobjects where name like @lsFilt
--2
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
exec (@lsQuery)
When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.
Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of
create proc SP2Style
@psFilter varchar(16)
AS
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
exec (@lsQuery)
GO
instead of
create proc SP1Style
@psFilter varchar(16)
AS
select * from sysobjects where name like @psFilter
GO
Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?
thanks
Paul
Speeding Up Site By Storing Query Results In File
Mar 4, 2008Hello,
We have some queries that are long and intensive. We have thought about running the queries and storing the data in a text file for lookup from our website.
Example: Our online store only displays items that are in stock so when a user selects a category a query runs and grabs only items that are in stock and then displays them. There could be thousands of items the query needs to sort through before displaying the items that are in stock. What if we ran this query once every hours an stored the results in a txt file? The asp page would then go to the text file to grab the results instead of having to run the query every time a user selects a category. Will this speed up the site by not having to query every time? Would this be a correct way to eliminate queries that run thousands of times a day?
thanks
Andy
SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article
Oct 22, 2015There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
Oracle 9i -&&> SQL Server 2005: Schema_option Parameter When Adding An Article To A Publication
Jul 13, 2006First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.
I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.
I've tried to specify the schema_option parameter as "@schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).
Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253
The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?
If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?
Thanks!
Best regards,
JB
Article: Database Mirroring Not Ready For Production When SQL Server 2005 Ships
Sep 16, 2005Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?
View 1 Replies View RelatedSQL Tools :: R2 Management Tools Install Fails - Specified Account Already Exists
Oct 20, 2014The installation SQL 2008 R2 Management Tools on a Windows 7 workstation fails with the error, The specified account already exists.
Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
Exit code (Decimal): -2068052700
Exit facility code: 1212
Exit error code: 1316
[code]...
Backup Strategy - SQL2005 Tools Versus 3rd Party Tools
Jun 1, 2007I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
Its Only A Development Server But ...
Jun 2, 2005OK, 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!
DataBase Development Server
Aug 9, 2005Hi,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.
View 2 Replies View RelatedBooks On Sql Server Development
Apr 7, 2008Hi,
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...
Transerfering Development From MS SQL Server Windows2003 Server To SQL Express On A Vista Machine?
Mar 23, 2008Hi all Pros I am transfering from windows2003 server and MS SQL 2000 to Windows Vista and SQL Express ...... Before I allways created a database manually and I could allways use as many databases I needed so in that way I could have several versions of a database for a certain software during development. Can you use separeted databasenames when developing in SQL Express? I am using a dotnetnuke installation file and inside the project there is a file /App_Data/Database.mdf should this file be renamed everytime I make a new testinstallation or setting up a separate dev environment /Johan
[Moderator Edit: unrelated links removed from post body.]
SQL Server 2008 :: Import Table Data From Production To Development Server
Feb 18, 2015Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedSQL Tools :: Tools To Determine Version From BAK File
Apr 24, 2014Is there - apart from the notorious RESTORE HEADERONLY - an tool which is able to tell which SQL Server version created a specific BAK file? I'm looking for a tool that can be used w/o an available/running SQL Server installation.
Alternatively, is there any documentation about what is read with RESTORE HEADERONLY so I could write a tool myself?
Where would I find the version "bytes" in a BAK file?
Transferring Existing SSIS Packages Saved In A Shared Folder Location From Development Server To Live Server
Dec 20, 2007Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1.
Both has SQL server 2005 running and has visual studio 2005
Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server.
Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this.
Thanks
George
Tracking Changes To A SQL Server Database During Development
Apr 24, 2008I 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?
Jagdip
Need Recommendation On Tool For SQL Server Development
Sep 12, 2000Need 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?
Thank you!
--llyal
Development Tool Choice For SQL Server
Nov 16, 2006Is 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?
JustStartinOut
Books For Sql Server Development Beginners
Dec 22, 2007Hi,
Can anyone suggest me good books for learning sql server development ie stored procedures etc..