Thursday, March 7, 2013

[SQL Server] Creating a reference table.

[SQL Server] Creating a reference table.

Creating a reference table.

Posted: 07 Mar 2013 04:08 AM PST

I am very new to SQL Server. I have a single table database, by this I mean I have created only one table. I have columns that I think would do well holding the data in it's own table and then create a PK to FK relationship. Also, is there anything special I have to do to query the primary table to give me the data that is in the new table?Thank youBrian

looping thru views

Posted: 07 Mar 2013 06:29 AM PST

I have a database with a large number of views. I would like to to be able to loop through the views and run a select on first record in each table from each of the views. Any advice or suggestions would be greatly appreciated.Charlie

Alpha character in SPID? Disk I\O Bottleneck Troubleshooting.

Posted: 07 Mar 2013 03:42 AM PST

Hi.OS is Server 2003 R2 Enterprise Service Pack 2.SQL is Microsoft SQL Server 2005 - 9.00.3353.00.I was tasked with troubleshooting the following repeating error:[code="plain"]Source spid4sMessageSQL Server has encountered 30 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\DataBase_data.mdf] in database [DataBase] (8). The OS file handle is 0x00000960. The offset of the latest long I/O is: 0x000000d80d8000[/code]However, I don't know how to troubleshoot "spid4s".What is the alpha character 's' doing in the spid?There spid is just the session ID, right?Session IDs only have numbers.There is a session 4, but that's been asleep while the error has happened.It's happened multiple times while I've watched it because of 'SPID4' which I can't even investigate.Can anyone shed some light for me?

Using variables in an IF statement?

Posted: 07 Mar 2013 12:00 AM PST

Hi Guys! I'm attempting to write a query that is using two variables, one will specify an ID to pull back, and that seems to be working fine... The second variable allows the user to either specify a column TransType: 'BUY' 'SELL' or 'ALL' Obviously if the user specifies 'BUY' I'd like only the 'BUY' from that column to be returned and vice versa...I've attempted to do this by creating temp tables to solve this query... it however is not functional, any suggestions? Begindeclare @idnum varcharselect @idnum = 1declare @TC varchar(50)select @TC = 'BUY'select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = @idnumWHERE TransCode = @TCIF @TC = 'all'BEGINselect B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = @idnumENDEND

Import records in MS Access table to native SQL Server 2008 table

Posted: 06 Mar 2013 08:18 PM PST

Hi SQL server expertsI am trying to use the SQL Server Import and Export Wizard to perform this simple task. The Access DB is on the same server, so I think it isn't a network problem.Everything proceeds smoothly up to the final stage, which is preceded by the following:Click Finish to perform the following actions: Source Location : E:\RISC\Access Imports\RefDiagnosis_20130306.mdbSource Provider : Microsoft.Jet.OLEDB.4.0Destination Location : <server name>Destination Provider : SQLNCLI10Copy rows from `Diagnosis_Load` to [dbo].[Diagnosis_Load]The new target table will be created.The package will not be saved. The package will be run immediately. Provider mapping file : C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\JetToMSSql9.xmlProgress was as follows:Initialising data flow task - successInitialising connections - successSetting SQL command - successSetting source connection - Error. Text in error messageCould not connect source componentError 0xc020801: Source - Diagnosis_Load[1]: SSIS error codeDTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquiteConnection method call failed.Additional information:Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)What actually has gone wrong, and what do I do about it? The error message makes heavy weather of telling me.PS. Something like this should just work IMHO. I feel embarrassed for SQL Server - how hard can it be for two mature MS technologies to talk to to one another?Yours hopefullyMark Dalley

Query hangs on table variable

Posted: 06 Mar 2013 02:07 PM PST

I have a query that consistently runs fast and returns the expected output, but when I try to insert the output into a table variable it just runs indefinitely, and I can't find the problem in the code. I can't really post the query or the output (this is a data mining task with healthcare data and there are strict privacy rules) but I will describe the situation as best as I can.The query that runs fine takes these steps:Declares datetime variables for StartDate and EndDateDeclares a table variable (an ID to ID crosswalk) with 2 fields and inserts 691,969 records with a select queryDeclares another table variable (simple list of codes) with 1 field and inserts 465 records directlyFinally, there is a union select that pulls 3 fields each from 3 different tables, each inner joined to the crosswalk table variable and where Date is between StartDate and EndDate and the code is in the code list table variable.This query returns 53,463 records in about 50 seconds.When I try to insert this output into another table variable, it doesn't throw an error, it just runs - I have let it go over 26 hours before just to see what would happen - it just keeps executing...Am I pushing my luck with all the table variables? I'm stumped.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog