I need a solution ( if this is possible) to automatically generate create scripts for my replications on our live server and put that into a file.
I dont have permission to scrip that from the Enterprise Managger, but the support have and can make a job in the live server for this.
So that would be enough for me to put a job to the live server what puts the scripts to a file.
I want to use the same replications in my test environmnet, and they are always changing and I dont want to wait for the support.
I have an idea, but I still dont try it:
If I run a trace, when i push the generate sql script button, maybe I can get the code for the generating the create script.
:confused:
Is there a simple solution for this problem, or I have to get it from the trace ?
Thanx!
In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)
So I want to know, is there any automatic query generator ? that could work like: 1) We enter all control names 1 by 1 2) we enter postfix text ( like .Trim(), .ToString() ) etc 3) Result should be automatically generated query
Is there any software/program for that ???
Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists
We have two seperate domains (connected via a VPN Tunnel over the internet) We want to replicate one table with a push publication between these two domains (both have sql server 2005 default instances) how do you figure out what ports to open up in order to be able to do transactional replication (of one table) between them????
I’ve recently taken on a SQL Server related assignment with a client running a very large database in a very poor environment.
We have a 200GB database with virtually no failover support. I’m interested in transactional replication, hardware redundancy, failover support and find that before moving forward on that we probably need to seriously re-evaluate our whole logical and physical design. My experience is more on the TSQL side rather than full dba hardware, optimization, tuning areas. Any suggestions for quickly ramping up my skills on the dba side (books, courses, etc.) and also recommendations for short term support resources would be appreciated. I’m want to make sure I cover all my bases with regard to future scalability, disaster recovery, high availability etc.
I have installed SQL server 2005 Express and SQL server Mobile, and I was reading the Books online section to find out how to maintain the two databases consistancy.
Found : replication Option.
Problem : It only shows Local Subscriptions in SQL Server and In SQL Server Mobile only Subscription. I dont get a Publication Option.
Please correct if Wrong : SQL Serv Mobile is the one should Publish and SQL Serv hould Subscribe ya?
I have just setup my first Transactional replication between two servers. Next I needed to test what would happen if the live server went down.
I disconnected the live server and connected it to the backup server (doing all the ip setup bits as well). Then I tried running my web app.
The web app ran great. I can look at the data that was replicated. My problem is that I cannot seem to add any data. Ever time I try to run a stored procedure, the web app crashes with 'sa' account login failed. I tried running the stored procedures through SQL Query Analyzer, and it says the same thing. I know there is no problem with the sa account, as I can view the data using this. There are no permissions set on any of the objects in the database. The only thing I can think of is that the replication has somehow disabled the ability to add data to the replicated database.
i need help. i have a merge replication running every 10 minutes. the replication is for 1 database consisting around 50 tables and all 50 tables are marked as article to be published. let's say the publisher is server A and the subscriber is server B. for explanation purpose, let's pick 1 table from the database, let's say table X
in server A, there are applications running that insert and update table X. in server B, there are applications running that select and delete table X. table X at average, has tens of thousands rows.
now comes the problem, every 10 minutes whenever the sql merge agent is running, sometimes whenan application insert a row to table X in server A, it returns an error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". when i stop the replication (delete the subscriber), the row insertion to table X in server A seems fine. then i come to a doubtful conclusion, that the timeout error happened because when the insert statement executes, the replication locks the table for merging.
could somebody verify this? if my conclusion is true, then how am i suppose to do this? (see bold text above). really2 appreciate the help
The VB code was ok if you wanted it to be basic but when I was using it and trying to get it to work into my project it was quite painful. So in retrospect I have gone back to C# ... so I can have operator overloading plus smaller code anyhow ... will post here new routine ... working on one that will convert a stored procedure to a function as well... ... I still have the old code if anyone is interested, but like I said ... it's not very robust and getting it there in VB.NET just was a bigger pain than I need at the moment ...
Hi,This may be a little OT, but I don't know exactly where else to postit. I am writing a little parser that generates valid SQL using"English like" text. I want to use this to allow non-technical users tobe able to quickly write their own queries, to search through adatabase that stores information about the sales of differentcompanies. I can provide more information for anyone who wants to help.Currently, the syntax is :Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon.... and ITEM_DATE date_conditionWhere:product_filter specifies the product type to be included in the searchcompany specifies the company whose data is to be searchedfuncname is an aggregate functioncondition specifies the criteria for the aggregate function (i.e. aHAVING clause)date_condition specifies the criteria for the dates to be used in thesearchNote: there can be more than 1 aggregate functionA typical query then may look like this:Select all 'toys' from 'Walmart' where average_sales(100) 100000 andavaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and"01-Jan-06"I would like to know what the underlying SQL statement will look like,so that I can use this knowlege to build a generic parser that createsSQL statements from the "English like" text, using the syntax Idescribed above.
HI all im new to sql reporting services, im in need of a rdl (report definition language)generator are there any resouirces out there on the net ? if so , can u suggest some links for the same? thanks in advance !
Hello, I am new to MS SQL. I primarily used MySql, but my employer has MS SQL so i am trying to learn this. I kind of assumed that this was going to be similar to MSACCESS. My question is this. In access its easy to make forms and generate reports. Is there an "add-on" that makes it just as easy in MS SQL?
I see Crystal reports mentioned, but don't think my company would spend more money on this project.
I can't seem to find it and the link is no longer valid. Does anyone happen to have a copy of this script? I'd like to check it out again (finally starting to do regular C# programming here at work).
Thanks!
Kyle Heon PixelMEDIA, Inc. Senior Application Programmer, MCP kheon@pixelmedia.com
Hello,Since SQL Server has no sequence generator, I wrote my own. (I claimno ownership of it as it is closely modeled after earlier discussionson this topic.) I have included the sql statements below.While it works independently on its own, It seems to lock in multi-userenvironments or in nested-transactions. It s funny really: I have mymain transaction, but the sequence generator below forces anothertransaction, which I do not really care for. I cannot remove the extratransaction from the sequence generator because I would like to discardany values it retrieved, regardless of whether the main transactionsucceeded or failed.Any suggestions?--------------------------------------------------------------------------------create table my_sequence (name varchar(10), seq int identity (1, 1))godeclare @next intbegin transactionupdate my_sequence set seq = seq + 1 where name = 'abc';select @next = seq from my_sequence where name = 'abc';commit transaction---------------------------------------------------------
i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily. I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column. Will appreciate help on this
Hello,- Is any body aware of a random workload/query generator such as theTPC-H query generator (QGEN) ? I am looking for a query generator thattakes a schema as an input, and produces several queries.I need it mainly for performance evaluation. the generator I am lookingfor should produce SELECT queries, including joins, and not only INSERTINTO, DELETE, UPDATE queries such as the "SqlQueryGenerator" athttp://www.tucows.com/preview/297930ThanksRegards,Abdur-Rahman
I have only been into SQL Server for a few months, and written severalstored procedures. Is there a good software product available that willgenerate the SQL code for a stored procedure based on a visual interface?Any help is appreciated.
In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated. I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.
Just a quick note to let you all know about a shareware app I have written called SqlSpec. It generates docs for any SQL Server 2000 or 2005 database, together with dependency diagrams, and lots more. It has very good reviews from the people who have tried it out so far.
If you are looking for a very good and reasonably priced doc generator for you databases, why not take a look?
You can find out more here: www.elsasoft.org (http://www.elsasoft.org/default.htm?referrer=jezemine).
I want to run DBCC INDEXDEFRAG(Db_name, Tab, Idx) for many of the databases . Number is huge and it is near impossible to go to each server and do a manual run. Can someone provide me a scrip to generate the above syntex for all the tables in a db?
This little handy algorithm provides a truth table for any range of numbers between 0 and 65535 of any base between 2 and 36! As a twist, you can either return the digits concatenated or as separate columns.CREATE PROCEDURE dbo.uspTruthTable ( @FromNumber SMALLINT = 0, @ToNumber INT = 255, @Step INT = 1, @Base TINYINT = 2, @Concat BIT = 0 ) AS
WHILE @Step <= @Diff BEGIN INSERT#Numbers SELECT@Step + Number FROM#Numbers WHERENumber <= @ToNumber - @Step
SET@Step = @Step * 2 END
IF @Concat = 1 SET @SQL = 'SELECT Number, ''''' + @SQL + ' AS Digits FROM #Numbers ORDER BY Number' ELSE SET @SQL = 'SELECT Number' + @SQL + ' FROM #Numbers ORDER BY Number'
EXEC (@SQL)
DROP TABLE #NumbersYou can use the algorithm like these examplesEXEC dbo.uspTruthTable EXEC dbo.uspTruthTable 0, 255, 1, 8 EXEC dbo.uspTruthTable DEFAULT, 15, 1, 2 E 12°55'05.76" N 56°04'39.42"
I am working with a report generator that is based on SQL Server 2000 anduses ASP as the UI. Basically we have a set of reports that end users canexecute through a web browser. In general the model works fine, but we arerunning into some scaling issues.What I'm trying to determine is, what is the optimal configuration for thissystem. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. Wehave been using the "fixed" memory configuration, allocating 864M to SQL.This is on a Windows 2003 server box.This works fine when a "small" query or two is executed, but the performancesuffers terribly when several users try to run reports in parallel. A singlequery might take 10 minutes to run if nothing else is happening on the box,but if additional users log on an run reports, it's almost impossible topredict when the queries will finish.I am also looking at the effect of database size on performance, runningtests against a database with 1 month, 3 months, and say 12 months of data,running the same query against 2 databases in parallel. With the originalconfiguration, the results were all over the place, with the 12 monthdatabase outperforming the smaller dbs, while other times there was littledifference. It seems that once the system starts paging, and paging heavily,it's over; the system never "recovers" and queries that previously ran in afew minutes now take hours.I added 3 G more memory to the system, and modified boot.ini to include the/3GB switch. Now when I run the same tests, the results are much moreconsistent, as the system rarely ever has to swap. Then again I've neverseen it go past 1.7G in Task manager, making me think that any more than say2.5G of memory is a waste?Things we are trying to determine are:- in the SQL Server memory configuration, is Fixed better than Dynamic? Wehave read that Dynamic is not good at returning memory to the OS once it'sbeen allocated- What else can we do to optimize the performance for this application? Itseems to me if the indexes are properly designed, the database sizeshouldn't have that much impact on performance, but this appears to be trueonly to a point. In comparing the execution plans between say a 12 month anda 3 month database, the plans are sometimes dramatically different. I assumethis is due to the optimizer deciding that going directly to the base tablesand not using an index will result in better performance, when in reality,this doesn't always appear to be true.- Are there other SQL Server switches I should be tweaking? Is there somenumber of simultaneous queries that this configuration should be limited to?- What about other versions of SQL Server (e.g. Enterprise, Data Center,etc) would these buy us anything?Thanks for any advice,-Gary
I am given the task to make an application in C# of filling the database ( made in sql server 2005) so that we can afterwards use those records for mining etc.. I dont have the slightest clue of how to go about making the data generator. Any ideas???
I have a windows forms app and I'm trying to add functionality that would allow the user to enter a number into a textbox control on a setup form and then that number would autoincrement by one each time a new record on another form is inserted into the DB .
I've searched high and low and can't seem to find much information on how I can put this together. I'm sure I'll need a "numbers" table in the DB that stores the numbers and is joined with the parent table where the records are being inserted into. After that, I'm pretty much lost on how to generate the numbers in the "Numbers" table.
If anyone could assist or point me in the right direction of a resource, I would really appreciate it!!
This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)
How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?
CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS DECLARE @NewOrderId int, @nSQLError int, @nRowCount int BEGIN TRAN UPDATE CUSTOMERS SET ORDER_NUMBER=ORDER_NUMBER + 1 WHERE COMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -999 End SELECTORDER_NUMBER FROMCUSTOMERS WHERECOMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -998 End COMMIT TRAN
...why the script generator in 2005 Management Studio is SO FREAKING SLOW!?
2000's EM would script out all the objects in a databases in 15 seconds. The new GUI is taking three-to-five seconds per object on my 1000 object database.
I have an existing (working) SP that when I try to drop into the reoirt wizard gives me errors. The procedure runs in SQL management Studio, works in an existing rdl, even returns data in the repor wizard but also returns errors and therefore I can't continue. My work around is to drop it into an existing rdl; then I lose the Report Wizard features of generating a slick, quick repport.
We have Sequence generator (Next Value) enabled for surrogate keys (SQL Server 2012). When we use the new SCD task, we get the following error:
[Insert Destination [42]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. ".
Is there any know issue that these two features [SCD task and Next Value] cannot be used together?
In SSIS, I created a flow and within that flow i need to create an automatic number generator. The flow is simple: Ole db source part, a multicast part and a flat file destination and a ole db destination. In the Ole db source there is no field that is the starting point for this generator. This automatic nr should start with 10000 and each time a row is found it should be increased by 1.
First question: is it possible to create a column in SSIS flow which is the starting point for the generator (in this case 10000) or should it be created in the source table?
Second question: where in SSIS and with what component can i create such a generator??
Hi, I am using SSRS to generate a 4 page statement... So i have a plan whose no. of Participants is 3271 rows... so While we try to export them to a PDF we will have around 13084 pages... but we cannot export so many pages at one time (it craps out). so right now we are breaking it up by start part and stop part and creating a pdf..
Is there a way that SSRS can handle so many pages at one go without any problems... if so how ??
