I am attaching a database with 3 data files.
When I execute "exe sp_attache_db..." I obtain this error:
database 'POINT' cannot be opened because some of the files could not be
activated.
I have deleted its LDF file.
Usually I detach my db, then I delete transaction log, and reattach 3 data
files...
Now it doesn'work!!!!!!!!!!
Someone can help me?
Thanks.
I have an Matrix report in which report output is completely numbers. That is fine but the problem is when i am trying to export it to Excel ,the data is exporting wih error:
Converting numbers stored as text.
i dont know why numbers are Exporting like text format.
please let me know whether it is problem with exporting in ssrs tool itself or else i need to change any properties in RDL file.
Note: This Error i am getting when i am using expression IIF(IsNothing(Fields!Parameter.value),"0",Fields!Parameter.value)
Hello everyone,
In reports ,My customer requirement is to display column based on selected criteria in UI .
The columns which are not selected by him will hide.
for that we kept an expression in Visibility --> Hide
Code Snippet
= NOT Parameters!Parameters.Value.ToString().Contains("Name")
then coming to HTML Report
It is working fine,but white space coming at end of the Table.
can't we supress the white space?
The white space width is exactly the width of the column which is hidden.
My designing in layout is wrong?
Else is that Problem with the SSRS?
Experts Please let me Know!!!!
Give me Solution!!!
Customer is strictly focusing on that requirement.
***Note: white Space is Some what Acceptable.But My Reports are very big like 45 columns around.When he selects 10 out of 45 then you can assume how much space is coming????????****
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @dbname = N'DS2004',
@filename1 = N'd:ds2004_data.mdf',
@filename2 = N'd:ds2004_log.Ldf'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks
Here is the error, I receive:
Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_log.LDF' because it already exists.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'TestDB'. CREATE DATABASE is aborted.
Here is my attach script:
USE [master]
GO
sp_attach_db @dbname= N'TestDB',
@filename1 = N'S:Program FilesMicrosoft SQL ServerMSSQLDataTestDB.MDF',
@filename2 = N'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF'
This server has a Raid 5 Disk Array partitioned into three Logic Drives C:, S:, L:. Not sure, why the server was configured this way. If I attach the database with the data and log files on the same logical drive, the DB attaches successfully. When I try to put the data and log files on separate logical drives, it fails. Issue seems to be with the drive partitioning. Any Ideas?
Hi,
I've created a dataset with 27 measures and 20 query parameters. When attempting to load the report containing this dataset I'm shown the message;
'Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file.'
If I do open the file it does indeed respond very slowly or even hangs.
I can manually format the XML code but amending the code in any way (i.e. using the layout designer to move a chart) removes my formatting and re-introduces the problem.
Are these an unreasonable amount of measures / parameters?
Environment;
VS2005 v8.0.507
MSSQL 2005 9.00.1399.06 Build 3790 SP2
Windows Server 2003 SP2
Many thanks.
Can anyone spread any light on the following.
I've created a database of 2 tables which exists over 2 drives. I had a 6 Gig log file on one drive and a 1 Gig log file on the other so I used sp_detach_db to detach it. I then deleted the log files and attempted to sp_attach_db with the following:
"sp_attach_db @dbname =N'cwc_load', @filename1 = N'f:cwc_loadcwc_load_data.mdf', @filename2 = N'e:cwc_loadcwc_load_data2.mdf'"
which are the locations of the .mdf files.
I get the error messages 5105 and 945 (which cannot be tracked down using either the Microsoft site or on-line help) but the messages are "Device Activation error. The physical file name 'F:cwc_loadcwc_load_log.ldf' may be incorrect"
"Database 'cwc_load' cannot be opened because some of the files cannot be activated"
which to me tends to suggest that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create database cwc_load on primary (filename = 'etc) which gives the same error message, please tell me someone out there has a resolution for this.
As part of moving to SQL2K from SQL7 I'm doing sp_detach_db and sp_attach_db on the source server. For those databases that have multiple .ldf files do I need to add a separate filename= line to the sp_attach_db command for each .ldf, or only reference the original .ldf in a single filename?
Thanks,
AL
P.S. Given that I've done a few databases already, what's the effect of entering only the single filename line if multiple filename lines were required?
how do i use "sp_attach_db" properly so that i can make a new database with only the .mdf file?
View 2 Replies View RelatedDoes SQL Server Agent need to be running in order to execute the
sp_attach_db stored procedure?
I am getting device activation errors while running this stored procedure.
Is there any other way to attach those files back to the SQL Server.
PLEASE HELP
I am trying to use sp_attach_db SP.
I have a dadafile and a log file at D:Asset
which I am trying to extract using
EXECUTE sp_attach_db 'Asset',
'D:AssetAsset_data.mdf',
'D:Assetasset_log.ldf'
can any one of u tell where i went wrong
I am getting this errror "Device activation error. The physical file name 'D:AssetaaAsset_data.mdf' may be incorrect."
I am trying to use the database provided from
http://www.trigonblue.com/sp_download.htm#Diagram
to practise stored procedures.
Thanks in advance
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error
Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119
Connection Broken
Hi,
I'm doing some work for a client who has not been backing up
his databases. After a server crash he has been left with
the .MDF and .LDF files. I have managed to successfully
use sp_attach_db on all but two databases. When attempting
to use sp_attach_db I get the following error message :
1> sp_attach_db @dbname = N'CSGDEV', @filename1 = N'e:SQL_DataDirDataCSGDEV_Data.MDF', @filename2 = N'e:SQL_DataDirDataCSGDEV_Log.LDF'
2> go
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
I'd appreciate any ideas. I'm not a SQL Server expert (heck, I'm not even
a Windows expert...) as you can probably tell.
Thanks and cheers,
Deeran
HiWith the sql server on line help , The syntax for the sp_attach_db hasthe file name .It is aslo given that max of 16 files can be geven. Iattached a db with a single d.mdf .could u pls tell what r the 16 file types or how a db can be attachedwith 16 files.With thanks
View 1 Replies View RelatedHello,
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
hi,
My problem is, I am going to ship MSDE with our product. Now the problem is that if some one copies the .MDF and .LDF files and uses "sp_attach_db" to attach the db files to his own server then my database will be exposed to him. I have tried this and it gets exosed. I cant deploy NT Authentication for the database, bcoz if the end user installes Enterprise manager then my database will be exposed to him. Is there any way that i can sequre the LDF and MDF files of my database so that no one can use that with sp_attach_db? I m using MSDE(Sql server 2000).
I successfully install SQL Express silently.
Then I try the following
-S .INSTANCE -Q "sp_attach_db 'ATTACH_AS_NAME', 'C:Program FilesAPP_NAMESQLDataAPP_DB.MDF'"
This works...sometimes. But Many times I wll get a permissions error on the folder "C:Program FilesAPP_NAME". If I go and give "Everyone" "Full Permission" to the folder...it works fine.
My questions are:
1) What system users need access (and at what level) to this folder to do an attach? Because obviously giving everyone full access is bad.
2) Is there a better method for deploying a populated database with my application that this?
Here's one. :)
I have Database A in Server 1 which contains objects (tables and SPs) that are owned by users other than dbo. Then I detach this database from Server 1 using sp_detach_db and attach it to Server2 (sp_attach_db). Even if the same logins exist or are created in Server 2, I still won't be able to access the objects in Database A using these logins. I know that this happens because of different SIDs for the same login names across the 2 servers. So given that I want to achieve this without having to use the Import Wizard or any other method, how do I go around this lil bit of a problem? Any thoughts? :)
Thanks!
Hi,all.
I tried to issue:
sp_attach_db pubs,'F:anthonypubs.mdf','F:anthonypubs.ldf'
where the pubs.mdf and pubs.ldf are on a remote mapped drive F:,
I got error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'f:anthonypubs.ldf' may
be incorrect.
Is attaching a database ONLY supported for local servers ??
What about creating database on remote drive? (I have same error msg back)
Thx
Anthony
Hi,
I am very new to SQL, and am basically self-taught(apart from SQL for Dummies and a couple of other books). I am hoping someone can help me with the 'CONVERT' statement.
My system outputs the date format as '12345'. What I have written so far is this;
select Resprj.Re_code, Res.re_desc, resprj.Pr_code, projects.Pr_desc,Res.Re_status1,
Projects.active, Projects.Pr_start, Projects.Pr_end
from res inner join Resprj on (Res.Re_code = resprj.Re_code)
inner join projects on (projects.PR_code = resprj.Pr_code)
and Projects.Pr_desc like '%C9%'
where projects.active =-1
order by Projects.Pr_code, Res.Re_desc
Could someone please help in regards to using the 'CONVERT' statement to change the date from '12345' to dd/mm/yy.
Thanks
Rob
I have a test db in SQL Server Express 2005 with a 65MB dat file and a 1.1GB log file!! The production db has a slightly larger dat file and only a 6MB log file. I haven't updated by test db from the production db in a couple of months.
I tried Shrink Database and Shrink File in the Management Studio Express, but the log file size hasn't changed.
How did the log file get so large and how do I fix it?
Thanks for any help.
Hello everyone,
I'm not sure if this is a problem but I've got a database which is about 1700mg in size (at least that's the allocated space on disk) and the log file is over 4600 mb. I've truncated the log file but it still keeps growing. None of our other databases are this large and there are a lot of transactions performed regularly but it looks odd to me that the log is this big when the data is half the size. How can I find out exactly how much space is being taken up by the data and is there anything I can do that will shrink the size of the log file? I am not really a dba so I'm not sure how crucial this is in the grand scheme of things.
Thanks
Hi guys.
I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).
The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.
The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.
by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status
i have tried everything and cant seem to get this problem fixed
Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You
Gil
Hey Guys
I have a huge speed issue on one or two of my SQL Tables. I have included the basic design below.
Structure
Id
ParentId
Name
Group
Id
ParentId
Name
Weight
Products
Id
Name
StructureProducts
StructureId
ProductId
Imported
StructureGroups
StructureId
GroupId
GroupProducts
GroupId
ProductId
AnswerDates
Id
AssessmentDate
Scores <-- This table is the slow table
AnswerDateId
StructureId
GroupId (nullable)
ProductId (nullable)
Score >= 0 && <= 100
Ok, Structures are the start of everything. Structures, have children. If a group/product is Linked to a parent or child structure then that group/product is visible along the structure tree flow path. Groups, like structure have children. And also like structures, if a group is given a product, then that product is visible through the structure tree flow path.
Example:
Earth [Structure]
- Asia [Structure]
--- China [Structure]
--- Japan [Structure]
----- Computer Stuff [Group]
------- Desktops [Group]
------- Servers [Group]
------- Laptops [Group]
--------- HP [Product]
--------- Dell [Product]
--------- Fujitsu [Product]
- Europe [Structure]
--- Germany [Structure]
----- Berlin [Structure]
--- Italy [Structure]
----- Rome [Structure]
----- Venice [Structure]
- America [Structure]
--- United States of America [Structure]
----- New York [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- HP [Product]
----------- Dell [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Washington [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- HP [Product]
----------- Dell [Product]
----------- Acer [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Chicago [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
- Africa [Structure]
--- South Africa [Structure]
----- Johannesburg [Structure]
------- Computer Stuff [Group]
--------- Desktops [Group]
--------- Servers [Group]
--------- Laptops [Group]
----------- Acer [Product]
------- Home Stuff [Group]
--------- Kitchen Stuff [Group]
--------- bedroom Stuff [Group]
----- Durban [Structure]
----- Capte Town [Structure]
- Australasia [Structure]
So the initial steps that happen (with regards to Scoring) are as follows.
1. Insert root score (which would be for a structure, a group, an answer date and either a product or no product
2. Score the next group up along the treeview, using the scores for the groups at the same level as the original group (0 score if no score exists).
3. Continue this till GroupTree is at root (parentid == null)
4. Using the next structure up along the treeview, repeat steps 2 & 3.
5. Continue steps 4 until Structuree is at root (parentid == null)
Example
Scoring a product for Johannesburg Acer Laptop would go as follows
1. Initial score for [Acer] product against Group [Laptop] for Johannesburg.
2. Calculate Score for all products (productid = null) against Laptop for Johannesburg
3. Calculate Score for [Acer] product against Group [Computer Stuff] for Johannesburg
4. Calculate Score for all products against Group [computer Stuff] for Johannesburg
5. Calculate score for [Acer] product against all root groups for Johannesburg
5.1. Group [Comptuer Stuff] and [Home Stuff]
6. Calculate score for all products against all root groups for Johannesburg
6.1. Group [Comptuer Stuff] and [Home Stuff]
7. Calculate score for [Acer] Product against Group Laptop for South Africa
8. Calculate Score for all products (productid = null) against Laptop for South Africa
9. Calculate Score for [Acer] product against Group [Computer Stuff] for South Africa
10. Calculate Score for all products against Group [computer Stuff] for South Africa
11. Calculate score for [Acer] product against all root groups for South Africa
11.1. Group [Comptuer Stuff] and [Home Stuff]
12. Calculate score for all products against all root groups for South Africa
12.1. Group [Comptuer Stuff] and [Home Stuff]
13. Calculate score for [Acer] Product against Group Laptop for Africa
14. Calculate Score for all products (productid = null) against Laptop for Africa
15. Calculate Score for [Acer] product against Group [Computer Stuff] for Africa
16. Calculate Score for all products against Group [computer Stuff] for Africa
17. Calculate score for [Acer] product against all root groups for Africa
17.1. Group [Comptuer Stuff] and [Home Stuff]
18. Calculate score for all products against all root groups for Africa
18.1. Group [Comptuer Stuff] and [Home Stuff]
etc. etc. etc...
This basicly coveres the concept behind the basic scoring methodology. Now the methodology splits into 2. The first Methodology 1, say it should do these calculations using the Exact same date as the original scored date. (Ie. if i do a score today, only scores on today will be used in the calculations). The other, Methodology 2, says that it should do the calculations on the latest available date. (Ie. If i do a score today, only scores from today and the latest before today will be used in the calculations).
Now to add another problem to this already complex process, is that each Group and each product within a structure can have either of the 2 scoring methodologies assigned to it. Also, products can only be scored against the structures and groups that they are assigned to. Ie, Acer exists in Laptop Group, in Johannesburg or South Africa or Africa, but doesnt exist in New York.
Ok, so now that i've explained briefly how this scoring works, let me get to the heart of the problem. Basicly its speed (can clearly see why), though the speed issue only comes
up in 1 Place. And that is where it has to look backwards for the latest available score for the required group, structure and product.
For this to happen i wrote a function
ALTER FUNCTION [dbo].[GetLatestAnswerDateId]
( @StructureId INT,
@GroupId INT,
@ProductId INT,
@AnswerDateId INT )
RETURNS INT
AS
BEGIN
DECLARE @Id INT
DECLARE @Date DATETIME
SELECT TOP 1 @Date = [Date]
FROM [dbo].[AnswerDate]
WHERE [Id] = ISNULL(@AnswerDateId, [Id])
ORDER BY [Date] DESC
SELECT TOP 1 @Id = ad.id--gs.[AnswerDateId]
FROM [dbo].[Scoring] gs
INNER JOIN [dbo].[AnswerDate] ad ON ad.Id = gs.AnswerDateId
WHERE [StructureId] = @StructureId
AND ISNULL([GroupId], -1) = ISNULL(@GroupId, -1)
AND ISNULL([ProductId], -1) = ISNULL(@ProductId, -1)
AND [Date] <= @Date
ORDER BY [Date] DESC
RETURN @Id
END
Now on small amounts of data (1000 rows or so) its quick, though that is due to the fact that the data is minimal, but on large amounts of data this function runs for along time. Specificly in the context of the following when there is 6 months of scoring data (100 000+ rows) to peruse.
SELECT [StructureId], [GroupId], [AnswerDateId], [ProductId], [Score]
FROM [Scoring]
WHERE AnswerDateId = GetLatestAnswerDateId([Structure], [GroupId], [ProductId], null)
AND [StructureId] = South Africa
AND [GroupId] = Computer Stuff
AND [ProductId] = Acer
Any idea's on how to make this quick? or quicker?
My Current runtime for calculating the 2500 base scores (totals 100 000+- rows) takes 15 hours. Though this is an initial calculation and is only supposed to be done once.
Also, this calculations are all correct, so my only issue itself is the speed of the entire process.
Thanks In Adance
Jonathan
WARNING: Running on cold coffee!
We have SQL Server running on a Windows 2003 server, only because Backup Exec requires it. AT the location : C:Program FilesMicrosoft SQL ServerMSSQLData
there is this file: SuperVISorNet_log.LDF which is 15 Gb and is accessed daily. I apologize because I don't know what this is!
My question is: can this file be 'pruned' (for want of a better word) because it's taking up a lot of backup space.
Hi,I have a table defined asCREATE TABLE [SH_Data] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Date] [datetime] NULL ,[Time] [datetime] NULL ,[TroubleshootId] [int] NOT NULL ,[ReasonID] [int] NULL ,[reason_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[maj_reason_id] [int] NULL ,[maj_reason_desc] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[ActionID] [int] NULL ,[action_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[WinningCaseTitle] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[Duration] [int] NULL ,[dm_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[ConnectMethod] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[dm_motive] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[HnWhichWlan] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[RouterUsedToConnect] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[OS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[WinXpSp2Installed] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[Login] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,[EnteredBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[Acct_Num] [int] NULL ,[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,CONSTRAINT [PK_SH_Data] PRIMARY KEY CLUSTERED([TroubleshootId]) ON [PRIMARY]) ON [PRIMARY]GOWhich contains 5.6 Million rows and has non clustered indexes on Date,ReasonID, maj_Reason, Connection. Compared to other tables on the sameserver this one is extremely slow. A simple query such as :SELECTSD.reason_desc,SD.Duration,SD.maj_reason_desc,SD.[Connection],SD.aolEnteredByFROM dbo.[Sherlock Data] SDWhere SD.[Date] > Dateadd(Month,-2,Getdate())takes over 2 minutes to run ! I realise the table contains severallarge columns which make the table quite large but unfortunately thiscannot be changed for the moment.How can i assess what is causing the length of Query time ? And whatcould i possibly do to speed this table up ? The database itself isrunning on a dedicated server which has some other databases. None ofwhich have this performance issue.Anyone have any ideas ?
View 5 Replies View RelatedHi,
When i open a project in ssis show the message:
"Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file."
I click Yes and my project open normally. Someone know why this happen? My project is small, have one package with any imports excel files to Sql Server 2005.
Thanks
André Rentes
Hello! I have a query that join five tables and returns around 45000 rows that takes no more than a minute to execute, in management studio, on a SQL Server 2005, 2CPU 32 bit(dual core), 4GB and RAID5 disk system. The O/S is Windows 2003 sp2 Standard Edition.
When the same query is executed in SSRS2005, with some drilldown and summary of drilldown levels, it never stops to execute.
Looking at the hardware in the performance monitor reveals nothing strange except that % CPU-time is around 40 percent. Memory resources over 2 GB are available all the time.
Any suggestions is appreciated!
Any problems with SQL Server 2005 source database running on SQL Server 2000 compatibility level?
Thomas Ivarsson
Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.
I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:
record_id-----org_id-----live-----version
====== ===== === =====
1-------------1----------0----------1
2-------------2----------0----------1
3-------------1----------1----------2
4-------------2----------0----------2
as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.
however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)
in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.
many thanks in advance,
phil
I have a dtsx package that works fine with one exception. When I open the dtsx package in BI, it gives me the following message:
Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?
When I respond yes, the package opens and I can edit or execute with no problem. Still, I want to understand what could cause this message to occur and, more importantly, how I can get rid of the message. When I try to simply execute the package I still get the same error and it seems this will be a problem for trying to run the package from SQL Server agent.
It seems likely to me that this message refers to the dtsx file (in xml format) itself. Does that make any sense?
On a dual opteron server, viewing the properties of a task inside a data flow was immediate up until this morning.
We just installed SP2, rebooted, and now you right-click on any task and it takes between 30 secs and 5 minutes before the context menu pops-up.
The server is 99% idle, most of the 16 Gigs of memory are idle as well.
I have a package that reads the contents of 11 Excel files into various tables. Opening this package in the designer, or with DTExecUI is extremely slow. In both cases when I open the package is takes over 10 minutes to do anything. Visual Studio gives the "Visual Studio is Busy" message for 10 minutes and DTExecUI just hangs. DTExecUI actually hangs twice, once when opening the package and a second time when clicking "Execute" (totalling over 20 minutes). It seems like no matter how I try to run the package it will always hang for 10 minutes before running with no status message or anything. Once it runs it completes quickly with no errors.
The various tasks in the package are fairly simple, most being Source > Data Conversion > Destination.
Any suggestions?
DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.CREATE PROCEDURE [dbo].[spu_Import_Export_Image](@srvr varchar(50),@db varchar(50),@usr varchar(15),@pwd varchar(50),@tbl varchar(50),@col varchar(50),@mod varchar(1),@imgpath1 varchar(1000),@pk varchar(50))ASBEGINdeclare @path varchar(50)declare @whr varchar(200)declare @fil varchar(100)declare @cmd varchar(1000)declare @imgpath varchar(800)declare @ext varchar(5)--declare @pk varchar(50)declare @KeyValue varchar(8000)declare @image varchar(50)--declare @imgpath1 varchar(1000)declare @imgpath2 varchar(1000)declare @sellist varchar(2000)set @path = 'c: extCopy.exe'select @sellist = 'DECLARE curKey CURSOR FOR SELECT ' + @pk +' FROM '+ @tbl + ' ORDER BY ' + @pkexec (@sellist)OPEN curKeyFETCH NEXT FROM curKey INTO @KeyValueWHILE (@@fetch_status = 0)BEGINset @whr = '"where '+ @pk +' = "' + @KeyValueset @fil = @imgpath1 + '' + @KeyValue --+ @extset @cmd = @path + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr+ ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr + '/F ' + @fil+ ' /' + @modexec Master..xp_cmdShell @cmdFETCH NEXT FROM curKey INTO @KeyValueENDCLOSE curKeyDEALLOCATE curKeyENDGOAbove srcipt runs fine with image data type in one table but when irun for some other table it gives me Error MessageTEXTCOPY Version 1.0DB-Library version 8.00.194SQL Server 'WSQL01' Message 170: Line 1: Incorrect syntax near '99'.(Concerning line 1)DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.ERROR: Could not use database 'test1'NULL-----------Aslo it only runs on server console if i run it from workstation uingsame files and tables it gives me an error again. Can anybody help meand reply me at Join Bytes! asap.thnx,dharmesh
View 1 Replies View Related