Hello, I have just started working with Microsoft SQL Server 7.0 and was wondering if anyone could recommend a great book for learning T-SQL. I was looking for something that would provide several examples on triggers and stored procedures.
View 5 Replies We've been working hard with our teams here to get better/more/good information out to our users. We€™ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback:
http://search.live.com/macros/sql_server_user_education/booksonline
We want to generate as much traffic as possible here so that we can see if this is useful to our users. We€™d love to hear back from everyone we can!
I'm looking for a book or a couple of books to learn SQL (in general aswell as various versions from different vendors). A couple of books that would take me from beginner through advanced topics. Any good suggestions?
/ ludde@freebox.com
Though I have been using SQL Server for some years, it is just recently that I have need to learn how to write SP's. I would like a recommendation for a book that is rich with examples (my best learning style).
Many thanks...
Todd
I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.
View 3 Replies View Relatedhi, im currently on sql hell right now. im having a hard time learning this sql thingie....
...the thing is this: im currently using the book ASP.NET Unleashed and most of the examples there are on SQL. what i was trying to do before was convert everything to OleDb to fit the ms access which i have right now.
unfortunately, some of the codes seem not to work properly. maybe its because of im using OleDb...
so what i did was i downloaded the MSDE sp3 package and installed it on my PC. now that i have an sql server for my WebMatrix, i just dont know what to do next? i mean, where do i put the sql sample databases like northwind and pubs???
im really confused about this sql thing. i really hate it.
help!!!
Does anyone know of any links to some great MS SQL sites I can check out to learn from?
Thanks for your thoughts.
Sincerely,
Tim
Im not getting data when I execute the below 0 Rows:
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'
However the below gives results...
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '01/01/2013' and '02/08/2014'
I have two servers both with different collation. Server A being SQL_Latin1_General_CP1_CI_AS and the live server and Server B being Latin1_General_CI_AS and a dev server. Now i have a load of data on the dev which i'm query to see if its on the live server.
select * from ServerA.Table1 where Col1 in
(select Col1 from ServerB.Table1)
I get this Error message.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I am SQL server DBA for 4 years. ALways i am getting help from TARA and other SQL gurus. Salute to all
View 1 Replies View RelatedHope someone can solve my problem
I have a working version of the above installed on a win 2000 machine with SQL ver (MSDE2000A.exe)
I purchase a laptop with vista home premium installed on it
MSDE2000A.exe would not install hence I installed SQL Server Express I managed to install the server and connect but when trying to login through Great Plains it says that I need SQL version 7. I have done a little research and found that SQL 7 is not supported by Vista.
Which way do I go now ?????
Do I install a copy of xp, update Great Plains or wait for an updated version of SQL Server Express SP ???
Please can anyone help !!!
and please dont get too technical Im not an expert
Many Thanks
Mr Magoo
Greetings,
We currently use GP 8.00 with the SQL Server 8.0.
We are trying to develop a view based on manfacturing orders - when a finished good is placed on hold and then calculating the componet parts that are on hold.
The calculation runs fine, we multiply end quanity x componet quanity found on the BOM. But we are not getting the correct componet item numbers to display.
Here is the syntax we are using:
SELECT DISTINCT
TOP 100 dbo.BM010115.QUANTITY_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.ENDQTY_I * dbo.BM010115.QUANTITY_I AS QTY_REQHOLDMO,
dbo.BM010115.CPN_I, dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.ITEMNMBR, dbo.WO010032.MANUFACTUREORDERST_I
FROM dbo.TEC_MOSumm RIGHT OUTER JOIN
dbo.WO010032 ON dbo.TEC_MOSumm.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
dbo.BM010115 ON dbo.WO010032.BOMCAT_I = dbo.BM010115.BOMCAT_I
WHERE (dbo.WO010032.MANUFACTUREORDERST_I = 4)
We have tried several different types of joins but still no luck. The upshot is we need to know that when a finished good is placed on hold, - how many of its componet parts are placed on hold.
Any assistance you can provide will be appreciated.
Sam
Here is my problem.
I am setting up a new server with Great Plains v8.0 (I have migrated all the databases from the other server, which has a working version of Great Plains v8.0 at the present moment). We are planning on getting rid of that server as it causing a lot of problems at the moment. I start the install of Great Plains v8.0 on the new server, install the components. After Great Plains v8.0 installs I go into Great Plains Utilities, and get the following message (I have installed SQL Server 2005 on the new server, and SQL Server 2000 is being used on the old server).
After I run Great Plains Utilities, I get the following error message -
"The stored procedure verifyServerVersion() of form duSQLinstall: 111
Pass Through SQL returned the following results: DBMS: 0, Great Plains: 0."
and when I click OK, it gives me another notification message -
"Your current SQL SERVER is not a support version
Req: Microsoft SQL Server 7.0
Act: Microsoft Server 2005
You need to upgrade to SQL Server 7.0 before continuing".
Why am I get that error message when I have SQL Server 2005 installed?
Can someone point me in the right direction.
Thanks
can anybody give me some nice links with a detailed explaination of the various replication errors and solution
thanks in advance
Jacx
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
Does any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.
View 1 Replies View RelatedHi All,
We have the requirement to replicate financial data to Aus from the UK, however I dont know if Replication is the best solution around?
Reason why I ask this, is that to create the publication, there are in excess of 10000 articles, which takes forever and a day to create, then when setting up the push subscription, this takes equally as long.
DB's physically range between 100MB and 2GB. Link to Aus is 2MB E1.
The accounts server isnt the most powerful of beasts (HP DL380, 1x1.4Ghx CPU) and with 4 DB's to setup and replicate, it's going to take some time.
With this in mind, I would also be looking to script out the publication should there be any failures and put it into SourceSafe, however this would also take a vast amount of time.
I've thought about using Log Shipping, however I dont know if there are any better ways
?
Thoughts appreciated.
Steve
Our company has a database server (Windows Server 2003 x64 EE w/ SQL Server 2005 x64 EE) that has Great Plains installed. We have a database for one of our companies that I would like to begin replicating to another server. The reason behind this is that we want to run the GP/CRM Connector which requires 32-bit ODBC connections.
Anyways, what I need to do is enable replication to our 32-bit server. We have been successful in making this happen from 64-bit to 32-bit with a much smaller database. When I go to enable peer-to-peer in the properties of the publication (which I set up using all of the default settings), SQL Management Studio will hang and run for days while appearing to be working. However, when we end up killing Management Studio days later (we let it run for a week once) the peer-to-peer option does not seem to have been set properly.
Any ideas?
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
http://www.download.com/Firefly-Dat...j=uo&tag=button
View 1 Replies View RelatedMy small business has about 21GIGs of data in our database. This entire week we've been running absolutely slow. Posting transactions have been taking up to 30+ seconds when they're normally instant.
We ran the profiler and found that certain stored procedures were taking an absolutely long time to run. We checked for fragmentation, indexing, etc. Keep in mind our hardware was constantly pegged at 80%+ all the time. We have about 60 users connected to our server at any given time.
After much effort to no avail, we finally resorted to just restarting the software. Some have even suggested powering down and rebooting our hardware, but I halted that. We stopped and restarted the server via the management studio and now everything is just peachy. We're funning super fast and smooth.
My concern is that we're just bandaid-ing the problem and not resolving it; so eventually we're going to be faced with this problem again. Even though restarting the server is quite easy and not too effective on our downtime, personally, I'd like to know what the real issue is. Isn't sql server 2005 a self-tuning software to begin with?
Has anyone experienced this and share some insights on what they did and didn't do to resolve this quirk?
I am using SQL 2005. I have created a SSIS package that basically executes another SSIS package (as part of a larger package) . It runs fine in SSBIDS but will not run if I save it and schedule it using SQL Agent. I should mention I am using a domain/admin account with SQL Agent, so I don't think that is the problem.
When I execute the job in SSBIDS, the Execute Package Utility window pops up, at which point I click on the Execute button, the job runs successfully and then I click on the close button.
I suspect it is not running via SQL Agent because of the user intervention required to complete the task (i.e. clicking on execute as described above). Is this correct? If so, is there a way to override the requirement for any user intervention. Or, could it be from something else?
Thanks for any insight.
This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.
If you want it in miles, change the average radius of Earth to miles in the function.
create function dbo.F_GREAT_CIRCLE_DISTANCE
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE
Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.
Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2
*/
begin
declare @radius float
declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float
declare @a float
declare @distance float
-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E
-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )
set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )
set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))
return @distance
end
Edit: corrected spelling
CODO ERGO SUM
In dexterity iam using table_compare() function to check if tables in datasource and dictionary are same but its not picking up the primary key change but it does pick up the column change
any suggestion
thanks
Hi there. We're doing a save on a MS Great Plains with SQL Server screen and get the following error:
"A save operation on table 'WS_Time_Sheet_TRX_WORK' failed because a database constraint was violated."
If I hit More Info it says:
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_WS10702_UPR00100'. The conflict occurred in database 'NSP', table 'U00100PR', column 'EMPLOYID'.
I know these messages probably make perfect sense to you guys, but I'm a newbie. Can you point me in the right direction? My thinking is that maybe I should force the foreign key constraint using the "WITH NOCHECK" option or maybe the wrong data type is mapped between the two tables sharing the Foreign Key and the key should be deleted and recreated.
Any help you can provide would be most appreciated! Assume that I'm a newbie and that I know very little. You won't hurt my feelings if you "dumb it down" so I can understand where to begin! lol I'm not even entirely sure I know where to look to edit the connection in the first place.
Not sure if there's a GP 8.0 forum, so giving this one a go.
I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB.
The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown:
"Unhandled database exception:
A Save operation on table €˜RM_Customer_MSTR€™ failed accessing SQL data
EXCEPTION_CLASS_DB
DB_ERR_SQL_DATA_ACCESS_ERR€?
The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record.
Code for the trigger follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER CTrig_Logic_Update_Customer
ON RM00101
AFTER UPDATE
AS
IF UPDATE(CUSTNMBR) or
UPDATE(CUSTCLAS) or
UPDATE(CNTCPRSN) or
UPDATE(STMTNAME) or
UPDATE(SHRTNAME) or
UPDATE(TAXSCHID) or
UPDATE(ADDRESS1) or
UPDATE(ADDRESS2) or
UPDATE(ADDRESS3) or
UPDATE(CITY) or
UPDATE(STATE) or
UPDATE(ZIP) or
UPDATE(PHONE1) or
UPDATE(FAX) or
UPDATE(SLPRSNID) or
UPDATE(PYMTRMID) or
UPDATE(PRCLEVEL) or
UPDATE(SALSTERR) or
UPDATE(INACTIVE) or
UPDATE(HOLD) or
UPDATE(CRLMTAMT)
BEGIN
DECLARE @Server_Name Varchar(25),
@Logic_DB_Name Varchar(25),
@SQLStr nvarchar(1000),
@CustomerN int,
@SICCode int,
@ARContact Varchar(35),
@LongName Varchar(50),
@CustomerName Varchar(24),
@SalesTaxCode Int,
@AddrLine1 Varchar(40),
@AddrLine2 Varchar(40),
@AddrLine3 Varchar(40),
@City Varchar(30),
@StateProv Varchar(4),
@PostalCode Varchar(15),
@TelephoneN Varchar(25),
@FaxTelephoneN Varchar(25),
@SalespersonN Int,
@TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field
@CustRateSched Int,
@SalesAreaCode Int,
@InactivePurge Tinyint,
@CreditStatus Tinyint,
@CreditLimit Int
------- Get the new Customer data from Inserted table
SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer),
@SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0),
@ARContact = RTRIM(i.CNTCPRSN),
@LongName = RTRIM(i.STMTNAME),
@CustomerName = RTRIM(i.SHRTNAME),
@SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0),
@AddrLine1 = RTRIM(i.ADDRESS1),
@AddrLine2 = RTRIM(i.ADDRESS2),
@AddrLine3 = RTRIM(i.ADDRESS3),
@City = RTRIM(i.CITY),
@StateProv = RTRIM(LEFT(i.STATE,2)),
@PostalCode = RTRIM(i.ZIP),
@TelephoneN = RTRIM(LEFT(i.PHONE1,10)),
@FaxTelephoneN = RTRIM(LEFT(i.FAX,10)),
@SalespersonN = RTRIM(i.SLPRSNID),
@TermsCode = RTRIM(i.PYMTRMID),
@CustRateSched = RTRIM(i.DEX_ROW_ID),
@SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0),
@InactivePurge = i.INACTIVE,
@CreditStatus = i.HOLD,
@CreditLimit = i.CRLMTAMT
FROM inserted i
------- Get Logic server name and database name
SELECT @Server_Name = RTRIM(l.Server_Name),
@Logic_DB_Name = RTRIM(l.Logic_DB_Name)
FROM tbl_Logic_DB l
------- Insert new Customer record into Logic database
SET @SQLStr = 'UPDATE [' + @Server_Name + '].[' + @Logic_DB_Name + '].dbo.[Customer] ' + '
SET SICCode = ' + CAST(@SICCode as varchar(10)) + ', ' + '
ARContact = ''' + @ARContact + ''', ' + '
LongName = ''' + @LongName + ''', ' + '
CustomerName = ''' + @CustomerName + ''', ' + '
SalesTaxCode = ' + CAST(@SalesTaxCode as varchar(10)) + ', ' + '
AddrLine1 = ''' + @AddrLine1 + ''', ' + '
AddrLine2 = ''' + @AddrLine2 + ''', ' + '
AddrLine3 = ''' + @AddrLine3 + ''', ' + '
City = ''' + @City + ''', ' + '
StateProv = ''' + @StateProv + ''', ' + '
PostalCode = ''' + @PostalCode + ''', ' + '
FaxTelephoneN = ''' + @TelephoneN + ''',' + '
SalespersonN = ' + CAST(@SalespersonN as varchar(10)) + ', ' + '
CommentN1 = ''' + @TermsCode + ''', ' + '
CustRateSched = ' + CAST(@CustRateSched as varchar(10)) + ', ' + '
SalesAreaCode = ' + CAST(@SalesAreaCode as varchar(10)) + ', ' + '
InactivePurge = ' + CAST(@InactivePurge as varchar(10)) + ', ' + '
CreditStatus = ' + CAST(@CreditStatus as varchar(10)) + ', ' + '
CreditLimit = ' + CAST(@CreditLimit as varchar(10)) + ' ' + '
WHERE CustomerN = ' + CAST(@CustomerN as varchar(10))
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
EXEC sp_executesql @SQLStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I have an SSRS file with 2 total reports - the first is a sort of template - a matrix with about 6 varying parameters, and the second report uses the first as a subreport - ultimately, there are about 5 subreports on the second page, all using the main "template" report, with the only variance being certain parameters. The client wanted an excel file as the end result, and I decided the quickest way to populate the data they needed would be SSRS.
My problem is when I export this stuff to excel. The template report, by itself, comes out beautifully. The expand/collapse boxes on the groups are all aligned and the data just fits.
However, when I export the "combined" report, the whole thing turns to caca....
For one, subtotals are auto-magically added in for certain groups (these aren't in the main report, and they don't show render in IE, and subtotals are showing for groups where subtotals aren't even on. ) Annoying, but not major.
More importantly, the expand/collapse buttons for the matrix groupings seem to align to the top, and not the actual row of the grouping. So, if I wanted to expand the data on say, row 11, the '+' button is actually on the previous row (row 110). This is the real showstopper and just not acceptable on a usability level.
Again - the only difference between the two spreadsheets is that one is its own report, and the second one renders the same report from a subreport. On IE it looks identical. Any reason why simply using a sub would cause such drastic differences in excel appearance??
Hello All,
I am new to this forum as well as sql. I want to learn skills with SQL. Can anyone point me to the right direction for some free pdf's/learning notes.
Thanks in advance.
sqlnewbie.....
Hi, not sure if this is the right place to put this but as its the beginners forum I thought I'd take a chance.
What's becoming more evident to me is my weak SQL skills. I'd like some advice on the best place to learn properly. It's starting to become a part of my role and i'm struggling a bit.
Should I go on a course? I have a number of teach yourself books and an evaluation version of the software but is this enough? I've worked through the books but struggle to apply the book to the real situation i.e subqueries
How did the more proficient of you learn SQL?
I want to get to a stage where I can help others with their SQL problems rather than always being the one helped.
Any advice much appreciated.
Hello
I am considering taking an on-line course in SQL, such as...
http://www.transcender.com/product.aspx?product%5Fid=Pak%2DMCDBACORE&dept%5Fid=100403
I do not work with SQL but I do have good computer knowledge and experience of MS Access and visual basic. Would it be plausible for me to complete such a creditation without the work experience or is it asking too much? I have a lot of time to focus on it at the minute. Does anyone have any knowledge of this particular company or can you recommend a better one?
Also, is there any way I could complete this course on a Mac (pre intel)?
Any help/info will be greatly appreciated.
Thanks
I am thinking about doing a computer course.It is learning about sqlto become an MCDBA.I am trying to find out how difficult this could beas I will be doing it from home and it is going to cost over £4000 forall the CD ROM's,books etc.You understand I don't want to start thecourse only to find I can't do it.I have owned my own computer for 18months and enjoy it.If anyone with experience can reply it would beappreciated.
View 4 Replies View RelatedI started a course and got to the lab section. It asked for a password giving me the name of student. I don't know if I was given a password when subscribing for the course. Certainly no in the confirmation email.
Where did I go wrong?
John.
I have four questions about Tsql language:
1. Is it important for a web developer to learn Tsql ,if he uses Data driven sites or just learn (select ,update,insert,order by ...I mean the most popular statements) ?
2. Where to learn Tsql ,is there any free books ,videos,tutorials,articles ,websites (ofcourse sql server 2005 with it's enhanced Tsql syntax?
3. Can I use a Tsql generator tool or Builder instead of learning the language ?
4. Where I can find such a tool?
If you do not know all the answers ,tell me what you know ,I will appreciate all the answers,and plz need help from the moderators also.
Thanks