Script Your Data In A T-SQL Format (which Can Be Executed In Query Analyzer)
Jul 20, 2005
Hi all,
i'm working with SQL Server for about 4 years, and i have ever
searching for a tool which can script my DATA in a T-SQL format to
copy them very easy from one server to another.
For example scripts like that:
IF NOT EXISTS (...)
INSERT INTO (...)
ELSE
UPDATE (...)
I have never found a tool like that, so i write a it by myself.
This software is a free version in the moment, you can download it on
my website:
http://www.sqlscripter.com
..NET Framework on your client PC is required.
Have fun with it ...
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
HelloI have a SQL 2000 server, the server setting is default language :French and all Collation names in FrenchIf i launch the 'Query Analyzer' from the SQL Entreprise Manager on mydatabase, and run a T-SQL my dates are in English.If i do a : DBCC USEROPTIONS, my results arelanguageus_englishdateformatmdyNow if i launch 'Query Analyzer' from the startup menu, my date formatis ok in French, if i do DBCC USEROPTIONS, results are:languageFrançaisdateformatdmyIn the Query Analyzer i checked the option in Tools>Options>Connections(Use regional settings is unchecked) and they are the same in both case.How can i resolve my problem ?Thanks
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Hi All !I open a table in Query Analyzer by right clicking on it andselecting 'Open' and when the data is displayed in the view pane Iwould like to be able to edit it. It seems however that tables markedas read only (as shown on the top bar e.g. SQL Query Analyzer - [OpenTable ServerName.DBName.dbo.TableName(read-only)] ) can not be editedwhereas the one that do not have that attribute infront of the name Ican edit. Is there a way I can change the read only attribute of atable so that I can edit data directly in the view pane?Please let me know if there is !Thanks a heap ! :)Harkirat
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
Hi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer: When i try as Select * from Address it said invalid object. But when i explored AdventureWorks database in the Management studio i see it as Person.Address!!! i don't understand what is a Person in Person.Address??? any clues on this? So as a test when i ran select * from Person.Address it did work and i saw the results in query analyzer. Can any one help me understand about this confusion? Thanks-L
we have a database hosted online on sql server 05. at work, we have a sql 2000 query analyzer on a machine, and sql 2005 management studio on a machine. both machines can connect to the database, both can pull up the data from a certain table. but there are two columns that display info in query analyzer that show up as empty on management studio. not null, just empty. any ideas why?
plz help me to upload my sqlserver data base with query analyzer method plz write the code i should write in query analyzer box to uplode my database(db) in my server(h_server) and is it neccessary to uplode log file or not ,or guide me with intruducing a site
In my report I have two date parameters, both are of type DateTime. The problem is that when the report is called for the first time the report parameter value is not shown in the expected format (de-DE, 01.01.2007) but in en-US 01/01/2007 Only when I change a Date using the calendar popup and click on the "show report" button, the right format will be taken over.
Language properties of the report are"=User!Language" IE language is de-DE. Report Server is a MS Windows Server 2003 R2 Standart Edition SP2 with MS SQL SERVER 2005 SP2
P.S. on a server with MS SQL SERVER 2005 SP1, this report works fine. may be this bug is a new feature of SP2?
I have a table that stores the period and year as two seperate fields the problem that i have is that when the data is entered in the data base the period is sometime entered as 6 or 06, hence i have a table thaat looks like as follows:
Period Year ====== ==== 6 2006 06 2005 12 2006 3 2005 2 2005 4 2005 04 2005 03 2005
when i currently query the information using the period i use the LIKE command in SQL which gives me the results but the problem is when the like is done with 2 as the period value it also retrieves the 12, 02, and 2, in that case the period 12 was not that was requested.
I was wondering is there a way to reformat the data so that all the data is in a consistent format?
Or is there a better way of quering the information in the current format?
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer. Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16). Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned. If someone is able to help me figure this out, I would appreciate it. 1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList); 13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months) 2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin) 2. SVCSQL service use DomainSVCSQL2000 to run 3. SVCSQL agent use DomainSVCSQL2000 to run 4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
My first post here. I have an Analysis Services Cube and I need to export some of its data into a flat file (a semicolon separated value file) with fixed length columns.
What I have in Integration Services now is:
DataReader Source (ADO.Net conection) Data Transformation (NTEXT -> WSTR) Derived Column (WSTR -> STR, and some ISNULL validation) Flat File Destination (Delimited by ";")
the thing is I have all the measures and calculated measures in the cube formated with format strings and they work fine when I do a query through the SQL server Managment Studio, but in Integration Services before the columns are written into the file they lose its format.
for example, I have a calculated member called "Deuda Total Nacional" that returns something like this +0001234,00 and I need to take off the decimal separator so it is written into the file like this +000123400 , I'm doing it this way
(ISNULL([Deuda Total Nacional]) ? "+00000000000" : REPLACE([Deuda Total Nacional],",",""))
Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype.
My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL.
So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below.
--snip-- SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "' --snip--
That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there.
Bold: I get it now. It is all automatic as MS-SQL does it automatically...
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
This is a request for help fixing a SQL Server 2000/ADO problem on WindowsXP. I would appreciate any useful assistance.PROBLEM: SQL Server access on my machine fails as follows:1. All of my VB6 apps reference the following ADO typelib:Microsoft ActiveX Data Objects 2.7 LibraryLocated at:c:Program FilesCommon FilesSystemADOmsado27.tlbWhen I open one of these projects in VB6, the reference to msado27.tlb isshown as MISSING. If I remove the reference then open the references dialogagain, msado27.tlb is listed normally. When I check it and try to close thereferences dialog, I take an "Error in loading DLL".I can remove the reference to msado27.tlb and add a reference to msado15.dll(Microsoft ActiveX Data Objects 2.8 Library).When I run the app, I take a trappable error on the first ADO line my appexecutes:Dim lo_Connection As ADODB.ConnectionSet lo_Connection = New ADODB.ConnectionThe error is:Automation errorThe specified procedure could not be found.2. In Enterprise Manager, when I do "Open Table/Return All Rows" on anytable, I take the following error:"The query cannot be executed because some files are either missing or notregistered. Run setup again to make sure the required files areregistered."CURRENT CONFIGURATIONI develop software in VB6 that accesses SQL Server via a dsn-lessconnections and disconnected recordsets. I am running SQL Server 2000Standard locally, version installed from the MSDN install CD and updated byservice pack download.SQL Server version: version 8.00.760 (SP3)VB version: VB6 SP5Component Checker 2.0 shows no problems, and reports:The following product releases were matched: MDAC 2.8 RTM.My OS is Windows XP Home. As far as I can tell, it is completely updatedwith all hotfixes, including the following:KB810218KB820291KB821253KB822603KB823182KB824105KB824141KB826939KB828035KB829558Q322011Q327979Q814995Q815485ATTEMPTS TO FIXA. I have uninstalled and reinstalled SQL Server and client tools *numerous*times with no change.B. Queries in Query Analyzer execute just fine.C. The last four lines out of sqlstp.log (located in c:Windows) are asfollows:12:40:04 StatsGenerate returned: 212:40:04 StatsGenerate (0x0,0x1,0xf000000,0x100,1033,303,0x0,0x1,0,0,012:40:04 StatsGenerate -1,George Copeland)12:40:04 Installation SucceededD. The only services running during setup are those specified by MicrosoftKB article 192710. There are no relevant errors in the event log.E. I tried reinstalling MDAC 2.7 SP1 Refresh and MDAC 2.8. No change.F. Microsoft KB Article 315868(http://support.microsoft.com/defaul...Ben-us%3B315868) saysthe following:If the reinstallation of the SQL Server 2000 Client Tools does not correctthe problem, then re-register the following DLLs by using Regsvr32.exe, andthen restart the computer:C:Program filesCommon FilesSystemADOMsado15.dllC:Program filesCommon FilesSystemOle DbOledb32.dllC:Program filesCommon FilesSystemOle DbSqloledb.dllC:WINNTSystem32Atl.dllAttempting to register the first three dlls fails with"LoadLibrary("C:Program filesCommon FilesSystemADOMsado15.dll")failed - The specified procedure could not be found.". In any case, thisdoes nothing to fix the problem. And since these link libraries are notregisterable, this KB article is just a little, shall I say, unhelpful.Please note that the MS newsgroups are FULL of requests for help on thisEXACT problem. All attempts to help have failed--NOBODY has figured out thefix. Most people trying to help are treating it as an installation issue,because that is what the error message says. However, I suspect insteadthat a Windows XP hotfix has CLOBBERED ADO.BTW, this is a BIG issue for me. My development machine is hosed, and untilI get this fixed, I AM COMPLETELY DOWN.
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00 0.50 0.00 0.00
To be displayed in the below format
1.25 0.75 0 0 1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values  example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query  or SSRS expression.
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
I have also posted this in microsoft.public.sqlserver.programming.
I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.
The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.
However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer. Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.
I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.
The query is of the form:
WITH RowPost AS ( SELECT ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row, ItemId, Title, .... FROM Items_View WITH(NOEXPAND) WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY ) SELECT *, (SELECT Count(*) FROM RowPost) AS [Count] FROM RowPost WHERE Row >= @minRow AND Row < @maxRow
Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).
This is running against SQL Server Developer Edition.
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default. I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _ "User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _ "database=fidojoe"
Here are the 3 ADO executes: conn.Execute (sqlstr_create_view) Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID) conn.Execute (sqlstr_drop_view)
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
Hi , is it possible extract file name from script executed in query analyser ?
Example steps
1. open QA 2. open c:mySuperScript.sql code in c:mySuperScript.sql use master select @filename as [file executed] select count(*) as xfrom syslogins 3. run
-- result file executed ------------ c:mySuperScript.sql
x -- 112
Why we need it. Each database update created using sql compare tool from red-gate Script saved in Vss When script executed in production using QA we save output as proof of deployment currently we add to each script
select 'script= put you file name'+ char(13)+ 'server = ' + cast(@@servername as char(20))+ char(13)+ 'dbname= ' + cast(DB_NAME()as char(20)) + char(13) + 'execution date= '+ cast(getdate()as char(24))+ char(13)+ 'executed by = '+ system_user
We want wrap everything in procedure that will extract executed file name and other values
this procedure should be first line of code in each script
