I'm very familiar with Oracle at a conceptual level and need to
understand MSSQL as well. Can anyone point me to a document/web page
that describes the different files used in MSSQL and a discussion of how
they relate? (ex. with Oracle, there is tempdb, redo, archive, db files,
rollback, etc.)
The tables:
TBLPROVIDERS holds the info on game providers
TBLGAMES holds information on the main game
TBLGAMESFORMAT holds information on the various formats the games exist in.
I have a simple view to list the PROVIDERNAME and TITLE of all our current downloadable games (which are FORMATID = 1). What I need to add is an additional column that shows the value 'web' if that GAMEID also has an entry in the TBLGAMESFORMAT table with a FORMATID = 2.
SELECT dbo.TBLPROVIDERS.PROVIDERNAME, dbo.TBLGAMES.TITLE
FROM dbo.TBLGAMESFORMAT INNER JOIN
dbo.TBLGAMES ON dbo.TBLGAMESFORMAT.GAMEID = dbo.TBLGAMES.GAMEID INNER JOIN
dbo.TBLPROVIDERS ON dbo.TBLGAMESFORMAT.GFDEVELOPER = dbo.TBLPROVIDERS.PROVIDERID
WHERE (dbo.TBLGAMESFORMAT.FORMATID = 1)
Is there anyone out there that has a simple solution?
Thanks,
Doug
I'm sorry, but I'm a definite newbie. How can I store really long pieces of text other than as the 'text' datatype, which is too short in SQLS7?
Please help! It's key to my site admin system for importing third party wysiwyg-generated pages into our system....
Thanks,
John E.
In Integration Services I am trying to extract pieces of information from Excel. I would like to select just information from one cell but when I do that I get an error. What I'm typing in is:
SELECT *, Now() FROM [Sheet1$A1]
Here is the error:
------------------------------
Error at Data Flow Task [Excel Source [170]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine could not find the object 'Sheet1$A1'. Make sure the object exists and that you spell its name and the path name correctly.".
Error at Data Flow Task [Excel Source [170]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
The only way that I can get the contents of one cell is if I type in a range and indicate in the Connection Manager that the first row has column names. Given that, to get the contents of A2 I am having to type in:
SELECT * , Now() FROM [Sheet1$A1:A2]
This gets me what I want but by using this method I am never able to get information in Row 1. That could pose a problem in the future and I'm sure that there is a better way to do it.
So my question is, how can I get something from a single cell without having to name a range?
Two other questions I have are: 1. Is there a way to get information in one statement from cells that aren't adjacent to one another. For instance, if I wanted the contents of just A2 and C4?; 2. Is there a way that you could select a named range from Excel? That would be good because then I could have Excel control the range and then just call that.
I know this is a lot but I have an impending project where I'll need to do much of this and I have only ever imported the contents of an entire sheet and not specific cells from a worksheet.
Thanks.
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
i am using web parts with drag and drop feature , i did not use aspnetdb for user membership
while it is published it gives eror as follows
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
Please give me ur valuable advice , thanks in advance
Hi!
Mgmt just decided that the designer (me) was more valuable than the developer (now me). I hope there are some gurus out there who can help.
What would be the best way to duplicate certain parts of my RS? Suppose
I have a table with three columns: ID, adName, and priority. When I serve the ads, I am randomizing them, but would like to serve some more frequently than others. I am hoping there is a single query I can use, that will seed the RS with multiple instances of high-priority records.
Should I stick to play-doh?
I am still using SQLv 6.5 and have a need to do monthly reports. It is my understanding that there is no inherent capability in SQL v6.5 for functions such as MONTH getdate() as exist in SQL 2000. Does anyone know of a script that will simulate this so I can extract information from a database such as date_last_update IN MONTH 02?
Thanks
Dale
Hi everyone,
I want to get year, month, day and time parts of a DateTime field in sql,
Can help me?
Hi, gurus:
Please tell me some third parts monitoring tools for MS SQL server 2000.
I have 5 records in a table called "testing" as such:
LetterCdcontact_id
x1
x2
x3
x4
x5
How can I split this dataset so that three records have LetterCd = 'y' and the other 2 records have LetterCd = 'x'? The number of records can vary, but the idea is the same: split the values in the LetterCd column evenly (50% = 'x', 50% = 'y'). LetterCd is varchar(25) and contact_id is int. I can't update the data, it would have to be a SELECT statement with some sort of CASE statement to get the remainder of the record count?
The issue is that I need to delete parts of this string using SQL Server.This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".
The title string reads:
2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714
I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"
Hey guys,
I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.
Any ideas how this can be achieved?
Thanks.
The issue is that I need to delete parts of this string using SQL Server.
This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".
The title string reads:
2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714
I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"
Hey guys,
I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.
Any ideas how this can be achieved?
Thanks.
Hi,
I'm having troubles connecting the webparts on SharePoint 2007. I installed the RSWebParts package from SQL Server 2005 Reporting Services on my MOSS 2007 box. The installation went smoothly and I can add both Report Explorer and Report Viewer on a page. But when I try to connect RE to RV I get the following error:
An unexpected error has occurred.
Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.
So I have to go to the maintenance page and remove the web parts.
Have anyone managed to bypass this and make them work together?
Thanks,
Hi,
I prepared a report including sale information of a company. There are different offices of the company in different locations. And in each offices there are more than 1 saleman. I have to send the report (but just the associated part of the report to the saleman, not the whole one) to each saleman and his/her manager. I prepared a subscriber data (name and mail addy) for each office. How can i send the associated part of the report to the salemen and his/her manager by using just the original report (I dont wanna seperate the report depending on the saleman and send each report one by one, cuz there are many offices and salemen)
Thank you
This is the thread
http://msdn2.microsoft.com/en-us/library/ms998292.aspx
Here it mentions the following
Deployment Considerations
When using Windows authentication to connect to SQL Server in production
environments, consider the following:
Use a custom service account.
Create a SQL Server login for a Windows group.
Assign database permissions to a database role.
Use a Custom Service Account
This is my problem we are trying to convert all our web pages to use Windows Authenication.
DOMAINWEBGROUP (for example).
Tracey and Jack part of this group
But sometimes there may be anothers added to this say John
The security comes from SQL right now i give data reader to the database.
Example
1. ASP.NET uses the service account DOMAINWEBGROUP
2. Assign WEBGROUP to database PERSONEL
Table Employees
Salary
Web Page
Lets say Tracey logs into the web page.....and i have two pages
1. Employess
2. Salary
As the WEBGROUP is data reader viola Tracey sees all salary information.
Im trying to figure out that only Tracey can see Employees
But im not seeing the relationship when using WEBGROUP
Is there a way in SQL to say yes we have the DOMAINWEBGROUP
but in sql also put in Tracey and know that Tracey is part of DOMAINWEBGROUP
So that in my tables i can only give tracey part of this employee table (say the first name last name) and not salary information. (I know how to use the roles).
The part im confused about is the mapping WEBGROUP to tracey in sql as the web developers only pass in WEBGROUP
I want to add some information to a report that I do NOT want to be included in the printed output. Is there any way to designate a rectangle or list or table to NOT print?
Thanks.
I'm having problems with handling a very large amount of user records - about 100.000 - 150.000 records. Instead of selecting all of them at a time, how do I f.ex. select 1000 of them? (f.ex. get nr. 1 - nr 1000, then get nr. 1001 - nr. 2000) ???
View 1 Replies View RelatedI have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?
Thank you for any help.
Hello,
I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:
FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007
I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):
SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs
FROM APs
WHERE (completed IS NOT NULL)
GROUP BY [File]
Total count should be: 4
Hi all,
New to replication
I need suggestions on how go about keeping 2 sql servers from different parts of the world to be in sync all times, some latency may be accepted.
Here is the senario,
we have website hosted in NA and customers from all parts of the world log in to oursystem and does what ever they have to do which will update the sql server databases. as our company is expanding we want to have a same website hosted in eu, so when ever a person logs from eu he will be hitting the webservers in eu and anything he do will go to the SQL database servers that are in eu. Now the problem i have is when one site goes down say NA site goes down, we need to route all the traffic to site in EU that means sql servers in EU. This brings the challenge of both the servers need to be in sync all times.
I did some research on merge replication, this might not work because in this concept we have to make 1 server as the master and the others as slaves. Also i need to think in a way that if they want to expand more that means to asia, i will be having another server, these all need to be in Sync.
We are using SQL Server 2005 standard edition.
I was reading that this can be achived easily with peer-to-peer replication,but is only available in Enterprise edition which is 4times the cost of standard edition.My company is not going to agree for this, so got to do the research and propose a solution
Any help will be greatly appreciated
i'm struggling to write an sql select statement that will return records on this criteria:
any time a certain word (not part of a word) in a certain cell, i.e. if i'm looking for 'and' i dont want to get 'the sand was hot' but i do want to get 'cats and dogs'. i cant presume that there will always be a space on iether side of the word because of punctuation marks and times when the word is at the begining of the cell 'and then we...'
anyone have any advice??
thanks,
vauneen
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec
And this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
[Code] ...
I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.
I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>
After the query is run, rows like the above would be changed to
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>
I have a field that will look something like this. I need to read a file and when the first 3 positions are REM, the number following is the Order number, and the initials, are the rep. this is how they are tracking for the rep, if they sent reminders to customers and when.
REM 0482348 LV 04/13
I would like to do this in SQL and also join to the order header file that will give me the invoice total for that order. what is the best approach here via SQL for these first 2 fields' needed?
The file of about 30,000 records contains an address field where addresses are entered in the following form:
128 NW 8 ST
3444 SW 19 AVE
32 SE 122 TERR
12604 SW 148 PL
Then there are anomalies such as
123 E BLVD
(I can ignore the anomalies until the main challenge is overcome.)
The challenge is that I have been asked to sort the addresses in the following order:
quadrant (e.g., NW, SW),
street number (e.g., 122),
street name (e.g., TERR)
house number (e.g., 128)
It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing.
How do I get these part into a four-column view? Or, is there another way to do this?
Esther
I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:
Item: Trailer
Model: model1, model2, model3
Category: Chassis
Subcategory: Upper frame, lower frame, electrical
Part: could be any thing that falls under one of the subcategories
Item: Trailer
Model: model1, model2, model3
Category: Gen set
Subcategory: engine, power, electrical
Part: could be any thing that falls under one of the subcategories
repeat...
My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.
Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.
Thanks!
yAy
Hi,I have a date/time field in a SQL2000 database, and what I would liketo do is to filter on a specific part of the field, for example thetime or hour.Supposing I have a set of data for the last 5 years and would like tofilter out any records which are outside working hours i.e. I wouldlike to show records where the time is between 9am and 5pm.Does anyone know if there is a simple way to do this in SQL? If yousimply don't specify the date part in the where clause (e.g. WHEREissuedatetime between '09:00:00' and '17:00:00') it defaults it to1900-01-01 so basically no data is returned.The only way I can see to do this is by using the DATEPART function,converting it to a varchar, appending 1900-01-01 on to it andconverting it to a datetime, and then using the where clause as statedabove. This is quite a long-winded way, however. Any other suggestions?Thanks,Matt
View 3 Replies View RelatedIn a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.
Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'
I have been messing around with datepart and dateadd with no luck.
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.
CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO