I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer
Dim oJobServer As New SQLDMO.JobServer
Dim oJob As New SQLDMO.Job
Dim oJobSch As New SQLDMO.JobSchedule
Dim oJobStep As SQLDMO.JobStep
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT: "insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf. I tried sp_attach_db specifing only mdf file but geting errors ------- microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223) ..... You cannot attach a database that has been created with multiple log files without also attaching all the log files. ----
how can I remove those big ldf files and create new ldf (defult(1mgb)
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100) declare @SQL VarChar (2000) Declare spot cursor scroll for select GM from BIM_Historical_Performance.dbo.Performance_Master open spot fetch first from spot into @@GM While @@Fetch_Status =0 BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
I have created a installer for my application and database. When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON ________________________________________________________________________________________________ "I won't last a day without SQL"
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet ALTER procedure spCopyTemplateElementTypesToIssues @TemplateRecno integer, @ProjRecNo integer, @IssueRecNo integer as declare @ElementTypeRecno integer declare @ElementTypeDepRecno integer declare @ProjTypeRecno integer declare @PreElementRecNo integer declare @PostElementRecno integer declare @Count integer DECLARE element_Cursor CURSOR FOR SELECT ElementTypeRecNo FROM dbo.tblTemplateElementType where TemplateRecno = @TemplateRecNo OPEN element_cursor FETCH NEXT FROM Element_Cursor into @ElementTypeRecno --delete from tblElementCPO WHILE @@FETCH_STATUS = 0 BEGIN select @count = count (*) from tblElementCPO where ProjRecno = @ProjRecNo and IssueRecno = @IssueRecno and TemplateRecno = @TemplateRecno and ElementTypeRecno = @ElementTypeRecNo if @Count = 0 begin insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno, ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase, TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson) values (0, @ElementTypeRecno, @IssueRecno, @ProjRecno, 5,0,6,99, 99,@TemplateRecno, getdate(), current_user) end FETCH NEXT FROM element_Cursor into @ElementTypeRecno END CLOSE element_Cursor DEALLOCATE element_Cursor select @Count = count (*) FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo if @Count > 0 then begin DECLARE ElementTypeDep_Cursor CURSOR FOR SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo OPEN ElementTypeDep_cursor FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno WHILE @@FETCH_STATUS = 0 BEGIN select @Count = count (*) from tblElementDepCPO where ElementTypeDepRecno = @ElementTypeDepRecno and PreElementRecNo = @PreElementRecNo and PostElementRecno = @PostElementRecno if @Count = 0 begin insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo, PostElementRecno, ChangeDate, ChangePerson) values (@ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno, getdate(), current_user) end FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno END CLOSE elementTypeDep_Cursor DEALLOCATE elementTypeDep_Cursor end
called by Code Snippet Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev) Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "spCopyTemplateElementTypesToIssues" cmd.CommandType = CommandType.StoredProcedure Dim spmTemplateRecNo As SqlParameter = _ cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int) spmTemplateRecNo.Value = _ Me.cbTemplate.SelectedValue Dim spmProjRecNo As SqlParameter = _ cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _ Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _ cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _ Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414 cnQI02414.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. cnQI02414.Close()
I have been in the process of creating some new pkgs in BIS. My question is, once I am done writing the pkgs, what next?
That is, how do I get the pkgs into SQL Server, and how do I schedule them to run?
Also, what if I have the following scenario:
I have created a new SSIS pkg that has several EXECUTE DTS 2000 tasks in them. The DTS packages that are called from the EXECUTE DTS 2000 task are stored in the database under "Legacy". These should still run OK once I "load" the packages to SQL Server 2005, yes?
Where do the SSIS packages "live" in SQL Server 2005? That is, where can I view them.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/ IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
I loaded the import/export wizard into my Tools menu. I ran it and exported a view to an excel worksheet with mixed results (I got the headings, but no data). I want to take a look at the package that should have been created (I selected that option, but the export didn't complete and I couldn't decipher the log to determine whether the package was created). If it was successfully created, where would I find it in SQL 2005 Express?
in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
Does anyone knows what this account does? I heard that it is used by Visual Studio and Query Analyser. However, I couldn't find much documentation about it. This account has a random password which is generated by the Server. The password cannot be changed and will never expire.
I am installing SQL Server on a large number of servers. MSDB was not created on 7 of the 36 servers. MSDB was created just fine if I uninstalled SQL Server and reinstalled it and also when I created MSDB using the instlmsdb.sql script out of the MSSQLInstall directory.
There is no mention of this problem on TechNet but there is an article on it on www.support.microsoft. However, the article says that the bug happens if SQL Server is started in single user mode. I am not starting any of these servers in single user mode.
Someone else has tracked this problem to BIOS. What do you know?
I am getting a return code of 12 (""File not created"") from sp_trace_create. I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account. SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it. The following is the relevant portion of what I am attempting. DECLARE @RC int, @TraceId intExec @RC = sp_trace_create @TraceId OUTPUT, 0, N'C:TraceFile' I have tried other paths that also do not work.
I am trying to make it that a user can just click on my form and generate a custom sql query.
for example: If I had three list boxes,I would like my user to be able to click on any of the items in the three boxes and come up with a datagrid displaying the query results.(in effect creating an individual query
i.e ListBox1 robert redford dustin hoffman steve mc queen ListBox2 butch Cassidy and the Sundance Kid the Sting straw dogs pappilon List 3 1965 1966(through to) 2008)
So then I want my user to come along click on Dustin Hoffman then click on a film(the sting perhaps) then the year,to find out if Dustin was actually in that film in that particular year...and if he was produce the details. This will create a "custom" sql statement like
select * from lst 1 ...ect.
What I would like to know is can this actually be done.
This is only a small example of the datbase.I have thousands of films and name ect so it can get quite big.
I get the following error message from Server-Agent-Jobs:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxx". [SQLSTATE 42000] (Error 7302).
The linked server already exists a long time and has always worked. I can still see all the tables and can query the data via select. The stored procedure that is called in the job, can I call manually.
Hi Guys, Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Hi I am facing an issue running an SSIS package on a SQL Server 2005 SP2 machine
Here's more information:
1) The SSIS package that dumps data returned from a stored procedure call to an excel (CSV) file. The package owner is a SQL Login called AppAdmin. The login has user memberships/roles in two databases:
1.1) Database AppDB - Has execute and select permissions.
1.2) Database msdb - SQLAgentUser role
The package has one step that is run as a SSIS Proxy account PrxApp. The Proxy is based on a credential CrdApp which is based on a windows account <domain>AppAccount. This account is part of the Local Administrators group and also has SA rights in on the server. The same account is also used to run all the SQL Server Services on this machine. The machine is part of a 2-node cluster (A/P).
When I try to execute this package (as a schedule or one-off), I am getting the error mentioned in the Subject. I have checked a few posts using google and have acted upon as many suggestions that I possibly could (e.g. change this, change that...did not do any registry modification, etc). Many of these posts have been talking about changing local security policy and adding the user to several groups. Did that, but still does not work in a SQL Agent Job.
The account currently has the following rights (local security policy)
Act as part of OS Perf. volume maint tasks Lock pages in memory Replace process level token
One thing that I did notice and don't know if that affects the issue is that the following groups (and SQL Logins) are missing from my Windows groups listing (Computer ManagementUsers and GroupsGroups). Note that this is a Windows 2003 machine.
(a) SQLServer2005MSSQLUser$<machine_name>
(b) SQLServer2005SQLAgentUser$<machine_name>
Now these groups (and logins in SQL Server) do exist on some other SQL2005 machines that I have checked to see what could be wrong.
Any help at all is greatly appreciated. If you need any more information please let me know.
I have a .txt file with the data fields below. I am trying to create a SQL query or CF statement that will convert the data into a a SQL INSERT statement. The plan would be to run the query, which would create a TEMP table to pull in the created INSERT statement, then place the TEMP table data into an already created table named FinalTable.
data1,data2,1,1 of 1,"data5",data6 data1,data2,1,1 of 1,"data5",data6 data1,data2,1,1 of 1,"data5",data6
I'm writing a query and I would like to pull records that show me any tickets that may have been created on a specific account within 72 hours of the first ticket. Each order/ticket is date and time stamped. I have been given a list of order numbers, and the requestor would like to see any orders that were created within 72 hours from the date/time the initial order was closed. I'm having a little difficulty figuring out how I would set that parameter within my query. I was thinking I could use:
I've been adding new databases recently, and obviously adding these to the backups. All is fine - the BAK files are being generated and 27 of the TRN files of 30, but 3 are still missing, in 3 empty directories! There is no reason for it. I have been over and over the settings for all databases and nothing ties this together. One database with no TRNs being created is our oldest one and has never had a TRN. The other two are recent and are similar - a live and a test instance - all the same data and setting - no difference - no TRNs. Other examples are similar data - Facilities Maintenance data at different sites in the country - test and live instances. It shouldn't matter what is in that data - just make the TRN of it! Some DBs don't generate it. What could be wrong?
There are never any errors in the jobs. The transaction backups happen every 4 hours, and the BAKs once overnight. At present 3 of 30 TRNs are missing for unsubstantial reasons and I expect this to rise as I add more databases.
Why can't a simple job make all the files it's meant to? Why does it get the hump with particular databases?
I created a new login name. I checked 3 databases in User Mapping. I tried to mimic an existing login name. When I right clicked at the database and selected Properties. I selected the Permission tab at the left. It showed both login names under users or roles. I selected the existing login name and clicked the Effective Permissions button. The login has all permissions: Alter, Create, Select, Insert, Update, Delete, Execute, Connect, etc. When I selected the new login name and clicked the Effective Permission button, the login has only Connect. Why the new login name does not have all other permissions? And how can I assign to it?