I have installed the MS SQL Server (Developer Edition) that comes with Visual Studuo 2005 Professional Edition into my computer. Seems like install is OK and I can see both SQLEXPRESS and MSSQLSERVER in configuration manager. I can start/stop both of the servers OK. However, when I try to make a connection from Visual Studuo 2005, I can only see SQLEXPRESS in the pick list in "Add Connection" window. SQLSERVER is not in the list.
The very first task I wanted to do is to convert and Access database to SQL. For that reason, I also installed "Microsoft SQL Server Migration Assistant for Access". In that application I cannot even see any server name when the wizard asks which server to choose. I also tried to start/stop "Sql Server Browser" service. When I stop the service, the pick list becomes empty and when I start the service the pick list has only SQLEXPRESS.
So, how can I see or pick MSSQLSERVER to make a connection in either applications? Please note I am VERY NEW to SQL Server and I appreciate your answers with that consideration.
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!
Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)
Hi, I have a table in SQL Server 2005 which has [Id] and [Name] as its columns. I also have a Oracle database which has a similar table.
What I want to do is as follows: In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Hi, I have a question that may seem simple to you, but I am having trouble locating the answer.
I want to install sp1 for sql server 2005. The directions tell me to select the correct link and give me three choices. The choices are differentiated with x86, x64, and IA.
I installed the 32 bit sql server 2005 on a window xp professional gateway personal computer. I'm just learning, so I did not involve my home network.
hi all, anyone has any idea in what i want to accomplish here :- how can we sort the select statement, say, we want to compare integer inputqty=60.. how can we match the suitable sum of column qty to reach the 60 figure...
EG -> qty column : 50, 25, 15, 10
i)how can we prompt the select statemnet to choose 50 and 10 to make it 60... ii)for another eg, say inputqty=90, then how can we prompt it to select 50 + 25 + 15 = 90 iii)and for some cases, if no round qty, the sum could be more, but the least from the qty list... eg : inputqty=80, how to automate the select statement to choose the closest figure, 50 + 25 + 10 = 85
i dunno if this is possible for any select or function or sp... thankss....
I need a way to select records at random from a SQL Server 2000 table. If the record I've selected at random meets a few simple conditions (column X <> 'A' and column Y == 'Z') then return this record and get the next. Ideally I'd be able to do this in a stored proc where I'd call it with:
parameter 1: table name parameter 2: number of random records to get parameter 3: value for condition 1 parameter 4: value for condition 2
I have the below data with two columns (Posting Date and Pond Crop, construct an SQL where i will pick and display only the TOP 3 Posting date of EACH Pond Crop. I only attached less data but in reality there are lots of Ponds and posting date in this table.
Raw Data Posting Date PondCrop 2011-11-21 00:00:00.00001PA01-15 2011-11-28 00:00:00.00001PA01-15 2011-12-02 00:00:00.00001PA01-15 2011-12-05 00:00:00.00001PA01-15
I have a table as below: user_id app_id form_id 1 a 1245 1 a 1345 1 b 1548 2 a 1245 2 a 1345 2 a 1411 2 b 1678 2 c 1245 3 b 1678 Now I want compare user 1 with user 2, and find out the records for user 2 where there are no app_id and from_id the same as user 1, so the output should be" 2 a 1411 2 b 1678 2 c 1245 Please advise how I can do this. Many thanks.
We have two tables (one has DTS import of external spreadsheet - the other is the online table) The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table). I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table where Description=@FirstTableFieldText) I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how)..... Even the base structure (pseudo code- whatever) would help
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo (patID varchar(20), enc_date datetime) go insert into #tmp_CustInfo select '111','2015-01-01 09:25:05.000'
ID AppName DepCode DepName Group ModifiedDate YearlyAmount 1 Nestle NS Foods Products 01/12/14 451 1 Nestle NS Foods Products 01/17/14 495 2 Oracle OR Software Info 01/24/14 279 2 Oracle OR Soft & IT Info 01/26/14 310 2 Oracle ORL Software Info 01/25/14 219 2 Oracle ORL Soft IT 01/28/14 600
MonthlyAmount Funded AppCategory Research 37.5623 Yes NE NA 41.2365 No N NA 23.2568 Yes OR InProgress 25.8333 Yes ORL NA 18.2189 Yes SOF Approved 50.0000 No IT RejectedExpected Output:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount 1 Nestle NS Foods Products 01/17/14 946 2 Oracle OR Soft & IT Info 01/26/14 589 2 Oracle ORL Soft IT 01/28/14 819
MonthlyAmount Funded AppCategory Research 78.7988 No N NA 49.0901 Yes ORL NA 68.2189 No IT Rejected
I want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.
select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group], B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount, B1.[FuBded], B1.[AppCategory], B1.[Research] FROM Business B1 INNER JOIN (select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount, SUM(MonthlyAmount) as MonthlyAmount from Business Group by ID) B2 ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate
I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Ok, I want to write a stored procedure / query that says the following: Code: If any of the items in list 'A' also appear in list 'B' --return false If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF (SELECT values FROM tableA) IN(SELECT values FROM tableB) Return False ELSE Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2 Windows Server 2003 1GB Ram....(I know, I know)
TIA
Code Snippet GO CREATE TABLE [dbo].[Tokens]( [TokenID] [int] IDENTITY(1,1) NOT NULL, [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED ( [TokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens] ( [Token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE FUNCTION [dbo].[SplitTokenList] ( @TokenList varchar(max) ) RETURNS @ParsedList table ( Token varchar(255) ) AS BEGIN DECLARE @Token varchar(50), @Pos int SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ',' SET @Pos = CHARINDEX(',', @TokenList , 1) IF REPLACE(@TokenList , ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1))) IF @Token <> '' BEGIN INSERT INTO @ParsedList (Token) VALUES (@Token) --Use Appropriate conversion END SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos) SET @Pos = CHARINDEX(',', @TokenList, 1) END END RETURN END GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList] @TokenList varchar(max) AS BEGIN SET NOCOUNT ON; create table #Tokens (TokenID int default 0, Token varchar(50)) create clustered index Tind on #T (Token) DECLARE @NewTokens table ( TokenID int default 0, Token varchar(50) )
--Split ID's into a table INSERT INTO #Tokens(Token) SELECT Token FROM SplitTokenList(@TokenList) BEGIN TRY BEGIN TRANSACTION --get ID's for any existing tokens UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token) OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old SELECT TokenID, Token FROM #Tokens WHERE TokenID <> 0 UNION SELECT TokenID, Token FROM @Tokens COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @er nvarchar(max) SET @er = ERROR_MESSAGE(); RAISERROR(@er, 14,1); ROLLBACK TRAN END CATCH; END GO
I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.
Hello, How do I clear the Recently Used Server list in SQL Server Management Studio? I am talking about the "Server Name" field that is part of the "Connect To Server" drop down when you initially open SQL Server Management Studio. Thanks.
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
--get output of command in temporary table INSERT INTO #services (fld) EXEC master..xp_cmdshell 'sc query state= all' --remove 'state= all' for displaying only running services
--keep only needed records (service code, service displayed name, state DELETE FROM #services WHERE ISNULL(fld, '') NOT LIKE 'SERVICE_NAME%' AND ISNULL(fld, '') NOT LIKE 'DISPLAY_NAME%' AND ISNULL(fld, '') NOT LIKE '%STATE%'
--display services, names and state SELECT tbl1.fld AS srv_code, tbl2.fld AS srv_name, tbl3.fld AS state FROM #services tbl1, #services tbl2, #services tbl3 WHERE tbl2.ident = tbl1.ident+1 AND tbl3.ident = tbl1.ident + 3
DROP TABLE #services
--HTH--
/*
For those working with Linked Servers, and dont want to worry about the state of Distributed Transaction Coordinator service, the following query can be used for ensuring that MSDTC is running before any action takes place
I am setting up a number of the same model laptops (Dell Latitude D600) to connect to a SQL server. I am going through ODBC to do this. My problem is that when setting it up sometimes the server is not part of the drop-down list and cannot be added though other times using the same model laptop w/ seemingly nothing different there's no problem. In addition when I do manually enter the server information and try to connect I receive a "Connection Failed: SQL Server Error 18456...." Then on the next laptop there's no problem at all. In addition when I attempt to add it through System DSN I receive a different error "SQL Server Error: 53" followed by "Error: 6" Any help would be greatly appreciated. Thanks.
