Hi.
I've installed and configured a SQL Server 2000 box on our local network. Now that I've moved it up to the Live network I've had to change it's name and IP.
Everything is fine apart from the SQL Server Agent, specifically the maintenance plans and jobs.
When I go to change or delete the existing plans or jobs I have the following message:
Microsoft SQL-DMO (ODBC SQL State: 42000)
Error 14274: Cannot add, update or delete a job (or it's steps or schedule) that originated from an MSX Server.
In typical MS style, I have actually been able to delete the maintenance plans from the "Maintenance Plans" area and create new ones. However I am unable to delete the related jobs under the SQL Server Agent - Jobs menu.
As far as I'm aware I have never added a Master SQL Agent Server (MSX) and looknig under the Agent properties - Job System it does say (none).
I am trying to write a T-SQL script to change about 200 SQL Agent jobs to send notification to a different email notification list. I could do this manually but it would take me a lot more time. Is there a way to script this out in T-SQL? Thanks!
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
Does anyone know if it is possible (and if yes, how) to create a trigger that creates a folder for every inserted record? In a specific root folder, I want a subfolder for every record in the table. How does the trigger have to look like to make the folder automatically with every insert of a new record???
I have an "insert into" statement that creates two identical rows in a table, with this statement: delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values]) This is the code-behind that performs the insert: Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?
I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?
I'm very new to SQL Server. Please help. I need to create a FUNCTION that creates a view. Then call this function in a SQL which is passed as a parameter to BCP. In Oracle, it would be something like:
create function CREATEVIEW
return number as
begin
create view SampleView as SELECT a,b,c from Mytable;
return 1;
when others then return 0; -- for exception handling
end:
create function DROPVIEW
return number as
begin
Drop view SampleView;
return 1;
when others then return 0; -- for exception handling
end:
Then my BCP will have something like:
BCP "select CREATEVIEW from dual"... QUERYOUT ..
then
BCP "select * from SampleView"... QUERYOUT ..
then drop the view again:
BCP "select DROPVIEW from dual"... QUERYOUT ..
I know there is no DUAL table in SQL SERVER. I just want to know how to code this in SQL Server.
The origin of my problem is that my SQL statement is too long to fit as BCP parameter, hence, am creating a view and reading there and dropping it again. If you can provide me with a better workaround, that would be great. Thanks in advance.
I have several SSIS packages which are run in sequence. But some strange reason sometimes a SQL Dump is created. If I restore database and restart the same ETL is works, or it creates another SQL Dump in a different place. Any idea on how to debug this problem?
SQL Server 2005 Developer Edition SP1 ( should I upgrade to SP2? ) Windows 2003/r2 64b ( latest patches )
Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) Microsoft .NET Framework Version 2.0.50727
Installed Edition: IDE Standard Microsoft Visual Studio 2005 Premier Partner Edition - ENU Service Pack 1 (KB926601) This service pack is for Microsoft Visual Studio 2005 Premier Partner Edition - ENU. If you later install a more recent service pack, this service pack will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/926601
SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00 SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 9.00.2047.00
Is it possible to pass 5 variables to a proc and have IT do the thinking and query structuring? An example of what I'm try to do is have one proc for getting vehicles by make, model, and years example of what I'd like to accomplish:veh_list_vehicleInfo_byDetails @TypeID int, @MakeID int, @ModelID int, @begYear int, @endYear int AS BEGIN
declare @SQL as nvarchar(500) set @SQL = 'SELECT a.ID, b.Model, c.Make, d.Name, a.Year, a.Mileage, a.Price, a.Sale, a.Certified_Pre_Owned FROM veh_vehicles a INNER JOIN veh_model b ON a.ModelID = b.ID INNER JOIN veh_make c ON a.MakeID = c.ID INNER JOIN veh_location d ON a.LocationID = d.ID'
decalre @ATTRIBUTES as nvarchar(500) if @TypeID is not null AND @TypeID > 0 begin set @ATTRIBUTES = @ATTRIBUTES + ' a.TypeID = ' + @TypeID end if @MakeID is not null AND @MakeID > 0 begin set @ATTRIBUTES = @ATTRIBUTES + ' a.MakeID = ' + @MakeID end
....etc etc.......
if Len(@ATTRIBUTES) > 0 begin EXEC(@SQL + ' WHERE ' + @ATTRIBUTES) End Else BEGIN EXEC(@SQL) END
END But I keep getting some errors regarding converting 'a.TypeID = ' to int ????? Please help!! I figured this would be easier than writing stored procs for EACH situation
hey out there, just started playing with this so bare with me... i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables. it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data! this is my code:1 Public Sub executeDts() 2 3 Dim oPkg As DTS.Package2 4 oPkg = New DTS.Package2 5 Dim oStep As DTS.Step 6 Dim sMessage As New StringBuilder 7 8 9 oPkg.LoadFromSQLServer("serverNameHere", "userNameHere", "passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "packageNameHere") 10 11 For Each oStep In oPkg.Steps 12 13 sMessage.Append("<p> Step [" & oStep.Name & "] ") 14 15 If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then 16 17 sMessage.Append(" failed<br>") 18 19 Else 20 21 sMessage.Append(" succeeded<br>") 22 23 End If 24 25 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description & """</p>") 26 27 Next 28 29 Response.Write("sMessage = " & sMessage.ToString & "<br/>") 30 31 oPkg.Execute() 32 33 oPkg.UnInitialize() 34 35 oPkg = Nothing 36 37 End Sub 38
am i missing a step?! it seems very odd that the tables are created but the insert fails... any advice anyone can offer would be great! cheers, jake
Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null. I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct. if request.QueryString("add")<> "" then Dim rsUpdateEntry Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset") rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
We noticed SQL Server 2005 is creating Program FilesCommon FilesMicrosoft SharedDW on our largest drive for each 64-bit installation. Does anyone know what this is? It appears there is no Microsoft documentation regarding this installation and if we need to keep it. It may be .NET related, but I have no idea why it is needed.
I am creating a database for an application through script. After the tables, views, and sp's are created, the database is populated with data. After all of this (and before the application is even run), the log file is about 700MB. If I shrink the database, it takes the log down to 1MB. The mdf file is about 165 MB before and after it has been shrunk.
I have two questions: 1. Is there something I should look for in my database scripts or is there a setting that could prevent this from being created so large.
2. Is there a script I can run in my sql code after the database has been created and populated to shrink it.
The following are the output lines from my code which is constructing some T-SQL queries on the fly. The query highlighted in yellow is the problem query. The code upto the problem query is working correctly and I am able to see the output tables in the Query Analyzer
STEP 1 : Create 3 Tables In Dynamic T-SQL : Showing 3 Strings from Exec statement
Create Table ##Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80) ) Create Table ##OUT_Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80), KeywordID int, rank float ) Create Table ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val float)
Step 2: Retrieving A Value Into Another Global Table (table has only 1 row and 1 column) Insert Into ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val ) Select IsNull( t , 0 ) from ##Test_word28July2007185548990201 (1 row(s) affected)
Step 3: I need the value (var_val) in ##STVR_FLOAT_Test_word28July2007185548990201 Update ##Out_Test_word28July2007194827580759 Set t = Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 where t > Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Select'. Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Select'. (1 row(s) affected)
Problem Definition
Part 1
The update query is trying to retrieve a value in a dynamically constructed table. ##STVR_FLOAT_Test_word28July2007185548990201 (1 column var_val , 1 row)
Update <Table_Name> set t = @var_val where t > @var_val The update query is simple, except I need to "SELECT" @var_val from the dynamic table
The @var_val is a float value.
I have outputted the contents of the table that is holding the @temp_val variable It has the correct value and the table has only 1 row and 1 column.
WHAT IS THE SYNTAX OF A QUERY TO SELECT A SINGLE VALUE FROM A TEMP TABLE WITH ONLY 1 ROW OR COLUMN AND USE THE SELECTED VALUE AS A VARIABLE IN AN UPDATE STATEMENT
I have created a few reports and linked them to a URL on a web page. (sample link is : http://servername/ReportServer/Pages/ReportViewer.aspx?%2fMy_Reports%2fBacklog+Report&rs:Command=Render )
When the user clicks on the link above it renders correctly but when the user tries to export the report to any format on the list, it launches another window with the following URL : http://servername/ReportServer/Reserved.ReportViewerWebControl.axd?ExecutionID=czq4c355dmsxdy55dif1nm55&ControlID=ad74d68e-2a9c-430f-8655-dd0e6c46f831&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=Backlog+Report&ContentDisposition=OnlyHtmlInline&Format=EXCEL
which then prompts the user to Open or Save the report.
How do I stop this window from opening ? or how can I make it close automatically ?
Please advise.
I know if I can change the ContentDisposition somehow to AlwaysInline then this extra window will not show during the export but it keeps defaulting to OnlyHTMLInline for ContentDisposition.
i am using SQL server for turkish language and i have a problem with turkish character "i". Whenever "i" comes in any word of query it gives wrong result. It also not able to recognize the small and capital "i" of turkish.
DDL and then write data using INSERT INTO ... DDL.
There are three strange things that i've encountered and will be glad to get help from anyone:
If I use as a table name expression with spaces, the name is changed to expression with underscore '_' symbol instead of spaces. I can not set trailing '$' when creating a table, but when inserting data I have to use the name with traling '$' otherwise the exception is thrown. Moreover, if i get the scheme of the Excel file later I am getting TWO tables instead of one: a first with the name without '$' and another the same with trailing '$'. Nedless to say that visually Excel shows only a name without '$' If I am trying to do same operations using OLEDB 12.0 (Office2007) I get invalid file. If anyone knows how I can overcome above issues, please write me a code. I am coding in C# but VB examples are as good as any other.
So i am trying to make a simple database and with one table and use a gui to make rows. When i run the program in debug mode it creates a new copy of my DB in the debug folder and saves only their, i cannot find a way to save back to the original DB no matter what i do including changing the Copy to Output Directory.
Now the more i try to fix this the more new DB it creates one in the Release Folder, one in the bin/Debug/bin/Debug one in bin/Release/bin/Release.
I am going crazy trying to scoure the entire project looking for someplace i can fix this.
All i want is to create a DB and be able to make changes to it and only it.
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat! ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components
Public Class ScriptMain Inherits UserComponent Dim sw As StreamWriter 'In addition to using the Imports System.Xml statement a reference must be added to the 'System.Xml assembly (Select Project-Add Reference from IDE) Dim xWriter As XmlTextWriter Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid' Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_' Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv' Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment' Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment' Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE' Dim fileName As String = gsPickUp & "" & gsPickUpFilename fileName = fileName & (Format(Now(), "yyMMdd").ToString) 'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn If OutputFileType = ".csv" Then fileName = fileName & gsPickUpFileExtn sw = New StreamWriter(fileName) 'connection to dest file
'Header records sw.Write(gsRunMode) sw.Write(Environment.NewLine) ' end of line sw.Write(gsMemoText) sw.Write(Environment.NewLine) sw.Write(gsStatementText) sw.Write(Environment.NewLine) sw.Write(Environment.NewLine) 'Spacer End If
If OutputFileType = ".xml" Then fileName = fileName & gsPickUpFileExtn 'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing) 'xWriter.WriteStartDocument() 'xWriter.WriteComment("Customer file parsed using script") 'xWriter.WriteStartElement("x", "customer", "http://some.org/name") 'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString) xWriter = New XmlTextWriter(fileName, Nothing) xWriter.WriteStartDocument() xWriter.WriteComment("Customer file parsed using script") xWriter.WriteStartElement("x", "customer", "http://some.org/name") xWriter.WriteAttributeString("FileName", fileName) End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then Dim delim As String = ","
If OutputFileType = ".csv" Then 'Create the trailer sw.Write(Environment.NewLine) ' blank line sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible sw.Write(Environment.NewLine) sw.Flush() 'send the stream to file 'Close file sw.Close() End If
If OutputFileType = ".xml" Then xWriter.WriteStartElement("RecordCount") xWriter.WriteString(Me.Variables.giSuccessCount.ToString) xWriter.WriteEndElement() xWriter.WriteEndElement()
I am profiling a web application that is using the Microsoft JDBC driver, version 1.1 to connect to a sql server 2005 database. Each java.sql.Statement that is created, within the application, gets a query timeout value set on it ( statement.setQueryTimeout(...) ).
I have discovered that the JDBC driver creates a new thread to monitor each Statement and the query timeout value. When the application is under load these threads are getting created faster then they are being destroyed and I am concerned that this will cause a performance problem in production.
One option I have is to remove the query timeout value and the monitor threads will not be created, another is to change JDBC drivers.
I'm curious is there any way to control this behavior so that these threads are not created or are managed more efficiently. Is there a workaround that anyone is aware of? Is this considered a bug?
I have found a similar bug here for the 2000 driver: http://support.microsoft.com/default.aspx/kb/894552
I have a solution with a couple of ssis projects in it. Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file. The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.
What causes this and how can I prevent this from occurring?
There are 2 of us working on the solution and the other fellow does not see this behavior.
The files do not show up in the VS2005 solution explorer. If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.
I have noticed that when I created SOAP endpoint and a webmethod uses ROWSETS_ONLY format €“ if a warning is raised in T-SQL implementing webmethod then response is being terminated. Either nothing is returned, and calling application (build with VS 2005) gets error €śThe underlying connection was closed€?, or part of response is retuned but terminated in the middle, and calling application gets error €śThere is an error in XML document (1, 8001).€? This can be caused by any warning, in my case it was €ś€¦NULL values eliminate by aggregate€¦€? warning. It will behave same way with errors. Is there any way to make SQL Server send a proper response in case of warning? Should this be reported as bug to Microsoft?
I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here:
I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows.
1 - Excecute SQL Task - DELETE FROM STOCK 2 - Data Flow Task 3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE 4 - OLE DB Destination (Creates table STOCK)
The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed".
I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts.
If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report.
Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words.
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
