DTS Error Involving Access
Dec 9, 2004
Hello,
I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:
Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.
I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.
I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.
Brian
View 2 Replies
ADVERTISEMENT
Apr 30, 2008
Hi All,
I have a table called [History] with the following fields and types.
Date(datetime), PriceZone(varchar), ProductID(varchar), ProductName(varchar), Category(varchar), SubCategory(varchar), Price(decimal 2 places), Cost(decimal 2 places), Units(Big Int), DollarSales(decimal 2 places), Margin(decimal 2 places), Profit(decimal 2 places)
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date.
Here is the sample of the records (Just for example I am just using one product in one zone)
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost, Units, DollarSales, Margin, Profit
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,10,20,25,5
2008-04-29,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,8,16,25,4
2008-04-28,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,15,30,25,7.5
2008-04-25,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,13,26,25,6.5
2008-04-22,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,11,22,25,5.5
2008-04-21,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-20,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-19,Zone1,001,Lays Chips,Snacks,Chips,1.95,1.5,18,35.1,23.08,8.1
2008-04-16,Zone1,001,Lays Chips,Snacks,Chips,1.9,1.5,20,38,21.05,8
2008-04-15,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,40,72,16.67,12
2008-04-10,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,32,57.6,16.67,9.6
2008-04-09,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,27,48.6,16.67,8.1
2008-04-08,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,29,52.2,22.22,11.6
2008-04-01,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,30,54,22.22,12
Now using the table information, I want to run a script to get the following fields;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
So output in the above example would be;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,46,224,92.00,424.70,25,22.30,5.00,88.70
Here;
Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output.
Price is the Price for that product in that zone on Date
Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates":
"Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30"
"Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
How Can I achieve this in SQL?
Can someone please help me as soon as possible.
Thanks,
Zee
View 3 Replies
View Related
Jul 23, 2005
We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies
View Related
Jul 20, 2005
Hi,I have a tricky sql statment I have to write (tricky for me) and I amstuck. I'm having trouble with the following problem.Table1 (Column a, Column b, Column c)Table2 (Column a, Column b, Column c)Table3 (Column a, Column b, Column c)Table1 contains a row of value (1, 2, 3)Table2 contains a row of value (4, 5, 6)Table3 contains a row of value (7, 8, 9)What I need to do is write a single SELECT statement (embedded selectsand so forth are allowed) to produce the table:TableNew (Column a, Column b, Column c)TableNew contains a row of value (1, 2, 3)and a row of value (4, 5, 6)and a row of value (7, 8, 9)I've been playing with the FULL OUTER JOIN and I getTableNew (Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c)TableNew contains a row of value (1, 2, 3, null, null, null, null,null, null)and a row of value (null, null, null, 4, 5, 6, null,null, null)and a row of value (null, null, null, null, null, null,7, 8, 9)Or I get only 1 row of data that looks like (1,2,3,4,5,6,7,8,9)If someone has the solution to this problem, please let me know.Thanks.
View 4 Replies
View Related
Jan 8, 2005
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go
create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go
create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--------- ---------------- --------------- ---------------- ----
HR 5 7 12 .84
Finance 10 15 25 1.75
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
View 2 Replies
View Related
May 11, 2007
I have three tables:
1. RubricReport 2. RubricReportDetail and 3. RubricReportTemplate
RubricReportDetail has columns ReportID and Age
RubricReportTemplate has columns IndicatorID and Salary
I want to insert a row in RubricReport Table with ReportID = @ReportID (RubricReportDetail) and IndicatorID = @IndicatorID (of RubricReportTemplate). How can I do this?
Kindly help me with this. Thanks in advance.
View 1 Replies
View Related
Oct 25, 2007
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Thank you, forum participants. You are the best!
View 4 Replies
View Related
Jan 6, 2008
Here are the tables I am dealing with:
Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls
software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls
software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls
dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls
purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls
dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END
The sproc compiles fine but I get no rows returned.
View 7 Replies
View Related
Jun 4, 2008
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
View 11 Replies
View Related
Mar 7, 2005
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)
View 7 Replies
View Related
Feb 22, 2006
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
View 7 Replies
View Related
Apr 8, 2008
Hello friends,
I am inserting non-english strings into my database table from my java program.
Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ×”×–×—' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
View 14 Replies
View Related
May 11, 2007
Table 1
ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date.
I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
View 4 Replies
View Related
Sep 28, 2007
Hi all,
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB.
Help from anyone is needed. Thanks in advance.
Sha.
View 11 Replies
View Related
Mar 19, 2008
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
View 3 Replies
View Related
Apr 1, 2006
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
View 6 Replies
View Related
May 22, 2008
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
View 16 Replies
View Related
Jan 20, 2007
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
View 1 Replies
View Related
Jul 22, 2004
I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.
Is it any solution to rollback transactions update already done before
the code breaks due to error ?
I was thinking about combining all stored proc on a big one and use
Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.
Any advise highly appreciated !
View 2 Replies
View Related
Sep 7, 2007
Hi -
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
Where do I change the security settings?
Thanks - Grace
View 5 Replies
View Related
Jan 14, 2007
When running the following statement in SQL 2005, I get the error message "Access is denied":
exec master.dbo.xp_cmdshell 'TYPE \SERVER-BSHAREFILE.TXT'
The following are true about the network:
The SQL Server is installed on SERVER-A.
SERVER-A and SERVER-B are Windows 2003 servers on the same Windows 2003 domain.
The SQL Server and SQL Server Agent services are running under the domain account SQLSERVICE.
SQLSERVICE is a member of the Domain Admins group.
The Domain Admins group is part of the local Administrators group on SERVER-B.
The SQLSERVICE account has also explicitly been given Full Control to the folder referenced by \SERVER-BSHARE
xp_cmdshell use has been enabled on the SQL Server.
If I run the following command in SQL:
exec master.dbo.xp_cmdshell 'whoami'the following is returned: DOMAINSQLSERVICE
If I change the command to access the c: drive instead of a network drive, it executes successfully.
Can anyone shed some light on why I still cannot access any of the files in this folder using xp_cmdshell?
Tim
View 5 Replies
View Related
Nov 23, 2006
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
View 3 Replies
View Related
Oct 19, 2015
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies
View Related
Aug 18, 2015
I have trying to execute the Sendmail task in my development envinorment i face this error..I have given the clear details error message below,Please have a look.
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.  System.Net.WebException: Unable to connect to the remote server  System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions ".Alternatively  i use the  SMTP connection to create the subscription as well,its working properly.
Here we are accessing SMTP connection manager as Virtually.Here in my client network we are using Macafee Anti virus ,We have excluded the Rsconfigration file in the excluded list.I dont know why this problem occures again using Sendmailtask in ssis?
View 2 Replies
View Related
Dec 2, 2005
I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.
View 3 Replies
View Related
May 22, 2002
I get this error in my Agent after starten to synchronise.
I see the access mdb been created and then deleted and renamed to
name_old.mdb
Do you have some help?
Richard
View 1 Replies
View Related
Mar 22, 2006
HI ,
This is a problem I encountered when I had to detach a database file (type .mdf):
1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.
2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful
3) However when I tried reattaching the database file, I got this error:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "CProgram FilesMSSQL ServerMSSQLData<databasename>.mdf'
Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?
Thanks much
Tonante
View 42 Replies
View Related
Jan 23, 2008
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
View 12 Replies
View Related
Sep 21, 2006
Hello there,
I recently started having trouble with my local SQL Server 8.0 database server. When I try to start it with Enterprise Manager I get the following error:
An error 5 - (Access is denied) occured while performing this service operation on the MSSQLServer service.
Nothing showed up in the SQL Server error logs. If I try to connect to it using the SQL Server Management Studio that came with SQL Server 2005 I get the following (slightly more informative) message:
Cannot connect to (local) ... (provider: Named Pipes Provider, error 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
I'm not sure which log to look at in the MSSQL folders: MSSQL.1, MSSQL.2 or MSSQL.3.
Furthermore, if I manually start the MSSQLSERVER service, then I can connect just fine with the SQL Server 2005 Management Studio but still get the weird error message with SQL Server 8.0 Enterprise Manager.
Any ideas?
Thanks
View 1 Replies
View Related
Mar 22, 2006
Hello everyone.
I am trying to install Project Server, and i'm having issues with sharepoint, and connecting to SQL:
dataserver is running sbs2003 sql2003 and analsys services.
server2 is running server2003 is to be the application server for project.
ProjectDb is the database that i have setup in sql.
username is the account that can control everything as administrer.
in Sharepoint is asks for the database server: <<dataserver>>
SQL Server database name: <<ProjectDb>>
I'm using windoes authentication and then i click ok, and get the error message.
I've also see the error message can not find the SQL Server, and access denied. Under ODBC i have installed the sql server information under System DSN.
Any help would be great.
Thanks
Everett Buel
View 3 Replies
View Related
Aug 1, 2004
Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.
CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)
Thanks,
View 2 Replies
View Related
Jan 16, 2004
Hi all,
I have a major problem. I have an mdf and ldf file that I have attached to a SQL Server 7 machine. However, when I try to do anything with the database, I get the following error:
Error 601: Could not continue scan with NOLOCK due to data movement.
When I say I can't do anything, I mean I can't even get Enterprise Manager to list the tables or stored procs. Everything
Everything I find online seems to deal with not being able to access a table here or there.
Any ideas on how to access the DB?
thanks
mark
View 2 Replies
View Related
Oct 3, 2007
I have team members collecting data in access tables. Ultimately my solution will be housed in SQL occassionally for reporting purposes, I collect all my access data, import into SQL Server and then Update my table with the Access data. The table I import into mirrors the final table. When I tried to run my update statement below I received the error "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error." I don't know how to correct since the date columns are the same datatype (datetime). Any suggestions welcome!
UPDATE tbl_lcCases
SET relm = b.relm,
datecontacted = b.datecontacted,
casestatus = b.casestatus,
comments = b.comments,
contacttype = b.contacttype
FROM tbl_lcCases a, nc_tmp_updcases102 b
WHERE a.mid = b.mid
and a.eventcode = b.eventcode
and a.taskcode = b.taskcode
View 3 Replies
View Related
