I need someone that knows something about SQL queries.
I have a client that is running a Database known as ProLaw. It is in part a
document management system for Law Offices.
They have an SQL 2005 database that tracks per client all the documents they
create.
We had to replace there server with new server. The new server is running
sbs2003 and had to have a different Netbios name then the old sbs2000
server. (Small Bus. Server has some weird quirks that make simply using the
same netbios name impossible. Google search it if you don't believe me.)
The database holds in a single column the full network share path to each
document.
Different documents may have different names and more subdirectories but the
root path of "\lawwillsbs2000ProlawDocuments" is shared by all.
The new server is named \sbs2003 I need to change
the first part of almost 3000 path statements to the new server. The rest
of the path is unchanged.
I have had several people running prolaw tell me that I should run this
query:
UPDATE Events
SET DocDir=REPLACE(DocDir, '\\lawwillsbs2000', '\\sbs2003')
WHERE EventKind='O'
This doesn't work. Nothing is changed. I'm guessing it is because this
query assumes the value will be ONLY \lawwillsbs2000 I see nothing in here that tells the query that this is only part of the string. No wild card or other marker.
I need some kind of string function here do I not? Anyone know enough to
help me craft a proper query?
Excel 2007 Documents Not Displayed in Windows XP Professional sp2 Start Menu's Recent Documents List:
Dear Microsoft Support: I can't figure out how to get recently used Excel 2007 (new file formats) documents to show up in the Windows XP Professional (sp2) Start Menu's Recent Documents List. I checked the Internet, the knowledgebase, many parts of the MS web site, etc. for an answer but can't find one. Are Excel 2007 documents supposed to show up in the XP Start Menu's Recent Document List? Is this a bug or do I have to do some sort of configuration to make it do so? If it's a bug, when will a fix be available?
Are these new Excel 2007 files filtered out like EXE files are which will not appear in the Recent Document List?
The Excel 2007 file types are listed in the Registry.
This question is also posted on Experts-Exchange...No solutions yet.
I have a question on how to change a hyperlink path. I have an ADP that has our scanned files linked to it the path is on SQL server. The problems is that we are migrating sql server and the files to their own seperate server, \gcfs01databaseBackgroundsSCANNED ACROBAT DOCUMENTS (BACKGROUNDS)SCANNED ACTIVE FILES5630.pdf
The server is called \gcfsql Instead of gcfs01. does anyone know how we can change the unc path to gcfsql without
\gcfsQLdatabaseBackgroundsSCANNED ACROBAT DOCUMENTS (BACKGROUNDS)SCANNED ACTIVE FILES5630.pdf
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type. I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty) FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID GROUP BY [client table].name ORDER BY [client table].name
Hi: I'm using SQL SERVER 2005 EXPRESS My server name is SERVER1SQLEXPRESS When I create a new data base on my server it saves at C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData How Can I change this default path? Thanks!!
I instance where customers want to migrate SQl dB to a new server. I want to change part of the path to lowercase.
The part to be change is always a computer name i.e. ComputernameDatareportA24440 testUser.doc I want to define the computername and the script changes only the computername to lowercase
Using Update Table set Path =LOWER(Path) -- etc. Go
Changes all the path to lowercase,
Note the Computername will always be different depending on the customer. The reason for changing the PCname to lowercase each client can type the PC name in various ways
Hi, In my system, for creating a databae the default path is in C: drive, but iwant to change into D: drive as a default path.Please tell me how to change??
Currently we have SQL instance on C: drive.We are now going to upgrade SQL Server 2008 RTM to SQL 2008 SP4.Can we change the path of SQL instance during upgrade?What services need to be stopped during upgrade.
acutally my network dept. has change the backup file server IP address.. i m now hving problem for taking backup. i have around 85 backup that runs everyday.. what i m doing now running every single command to drop backup device and then adding again.. but it's taking agess to do..
is there any simple script that just update the device path folder..
I want to redirect the logshipping primary backup folder to another drive, how to change the configurations steps to move the primary logship folder to another location within the same server!
I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?
Hello, This might be a simple thing, but I'm not able to find out the solution.
We have an application that accesses data from a SQL database. This works as long as the exe is located on the local drive of the user, however if the exe is stored on a unc path, we get the following execption thrown:
"System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
The action that failed was: Demand The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission The Zone of the assembly that failed was: Intranet"
On our developer machines we have a control panel for .net 2.0 configuration and can get it to work by setting the intranet zone to full trust. However the users don't have this control panel. 2 questions:
a) Is there a way to get this control panel on the users station?
I am new to XML in SQL Server and X-Path Query Recently I have done the following virtual lab in techNet site SQL Server 2005 XML Capabilities The following query is worked fine in the lab SELECT TOP 10 Demographics.query('declare default element namespace= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" /IndividualSurvey/YearlyIncome') FROM Sales.Individual But later I installed SQL Server Express in my system and also the Express Manager CTP but i am getting the following error if I execute the above query in my system Msg 9317, Level 16, State 1, Line 1 XQuery [Sales.Individual.Demographics.query()]: Syntax error near '=', expected string literal.
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..
Who can I send the Log File to for analysis and the fix feedback?
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.
-- Add the parameters for the stored procedure here @CSV as varchar(255) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Try Begin Tran Declare @sql as nvarchar(max) Set @SQL = 'Insert into dlrComp ( SN, CM, PM, MC, Comp_Total, XU, GM, From_Date, To_Date) Select [Serial Number], CM, PM, MC, [Comp Total], XU, GM, [From Date], [To Date] FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')' --print @sql Exec sp_executesql @stmt=@sql Commit Tran End Try Begin Catch Rollback Tran --Do some logging and stuff here End Catch END
If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as DomainUser1 and execute
exec usp_ImportComp @CSV='Comp.csv'
It completes successfully
However if I open SSMS (while logged into Windows on my PC as DomainUser2) using runas to run it as DomainUser1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
If I add With Execute as 'DomainUser1' and modify the stored procedure I get the same error message above.
If I log onto the Server that is running SQL as DomainUser2 I can successfully run
exec usp_ImportComp @CSV='Comp.csv'
Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.
how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):
Select DM.DriveID [DriveID], DM.FromDateTime [FromDateTime], DSD.ShiftID [ShiftID], Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic], Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType], Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
[code]...
SQL Server Newbie or here either. I'm a newbie as well.
hi experts, i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab. the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table. i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page. is this possible and if not, what are the other approches that can i make to get the report i want. also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors. thanks
I wrote a simple sql query to get the shortest path length from node 1 to all the other nodes. Since there's a loop in the graph, I want to prevent it from going back to some nodes it has expanded before.
I got the following error:
Msg 253, Level 16, State 1, Line 2 Recursive member of a common table expression 'CTE_Sample' has multiple recursive references.
It is referring to "and Table1.t NOT IN (select fr from CTE_Sample)"
Can somebody help me to solve it?
btw. How to use the UNION, EXCEPT or INTERSECT operators when doing the recursive join? It seems I must use UNION ALL.
WITH CTE_Sample (fr, t, level) AS ( SELECT Table1.fr, Table1.t, 1 AS level FROM Table1 WHERE fr=1 UNION ALL SELECT Table1.fr, Table1.t, level+1 FROM Table1 INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t and Table1.t NOT IN (select fr from CTE_Sample) ) SELECT CTE_Sample.t, min(CTE_Sample.level) FROM CTE_Sample group by CTE_Sample.t
Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika
I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.
Everything was fine before I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.
Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.
Here is the table:
create table ABMcontactlink ( classifier varchar(20) not null, /* Classification of contact. */ transmitter varchar(36) not null, contact integer not null, /* Link to ABMcontact (detail) table */ primary key (classifier,transmitter,contact), foreign key (contact) references ABMcontacts(identifier), group_name varchar(20) null,
last_modification_date datetime, /* Date/time record last touched */ last_modification_id varchar(40) /* Who last touched record */ )
go create index IndexABMcontactlink on ABMcontactlink(classifier,transmitter) go
create index CandidateABMcontactlink on ABMcontactlink(transmitter) go
As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number, and two other indexes.
Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.
Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.
** Now to the Problem at Hand **
I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.
I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.
Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"
This one is:
select * from ABMcontacts where ( (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and EXISTS(select contact from ABMcontactlink where contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
or (EXISTS(select contact from ABMcontactlink where (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)
Hello,I need to create a database to hold documents information.1. Basically, I need the following information for each document: Title, Description, LastUpdated, Category, Type, Url Should I create tables for Category and Type? And link them to my documents table? What type of relationship should I use?2. I also need to know how many downloads each document had Should I add a column in my documents table? Then I would increase it one by one. Or should I create a new table which would register each download.3. I need to let users to rate each document from 1 to 5. How should I implement this?Thank You Very Much,Miguel
I'd like to create an XML document from within SQL 7.0. Is the do-able? I know it's build into SQL 2K. But how is it done (or can it be done) in SQL 7.0.
Yesterday I installed MS SQL 2000 for the first time and have no idea what I'm doing.
I have been sent a database and asked to convert this to MS Access, for most of the data that is ok and I have already managed to do this. My problem is that the database contains MS Word documents stored in some of the tables (field type - image). I need to extract these from the database and get them back to individual Word files, ideally with a file name that relates them to the primary key of the table from which they came.
I have less that 24 hours experience with SQL server and would be very grateful if anyone can explain how I can do this.
I have been experimenting with SQL Server 2005 partitions. I loaded a terabyte of information into 2 tables. The first holds the document information and the second holds the actual binary document (in this case pdf). Most of the documents are about 1 megabyte in size, but the largest is 212 megabytes.
SQL Server has no problem storing the blobs. The problem occurs when I attempt to get the data.
I did some quick tests to test how fast I could pull the documents out. The largest took about 24 seconds. The 1 meg documents are sub-second.
Here is how the 212 meg doc breaks down:
Time to load datatable: 18.79 seconds Time to load byte array: 3.84 seconds Time to Write and open document: 0.01 seconds
If I access the file from a file server, the time is 0.04 seconds to begin showing the document.
As you can see, the longest time period is related to retrieving the data from SQL, and it is much slower that launching it from disk across the network. (note: the sql server and file server used to test are next to each other).
My question is, how can I speed up the access from SQL Server? I believe the keys are "partition aligned". Any suggestions would be appreciated.
I will add the table definitions and partition information as a reply since only 5000 chars are allowed in the post.
