I have to post this problem again because I didn't get the correct way.
I have SQL Server 2000 on client (Windows XP) and server (Windows 2003). DTC is running on both side. There is a transcation like,
BEGIN TRAN
INSERT MyTable SELECT * FROM MyView
COMMIT
MyView get data by:
SELECT * FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=PROD;UserID=DBDev;Password=WORLDWID').PRODD ata.dbo.CService
PROD is the production server.
When I run this query, there is the error message,
Server: Msg 8501, Level 16, State 1, Line 2
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(....) statements are there in the transaction ? Each stored procedure is made up of an action query.
begin tran exec("storeprocedure1('2')") if @@error=0 begin exec("storeprocedure2") if @@error=0 begin exec("storeprocedure3(122)") if @error=0 commit tran else rollback tran end else rollback tran end else rollback tran
I have two databases lets call them A & B, they reside in two PCs, each pC has its own C/C++ program working with A or B, Two PCs are linked by PPP (for example), but the link is not permonent available, the link is only available at certain time period, for example, at midnight.and the link is only last certain period, during this period, A & B will do the transcation replica( just heard about that!) to make them identical, A=B.
Now are my questions.
(1) Can database use dial up modem itself ? For PC program it is not the problem, but I am not sure whether MS SQL server database can be configured to do such job automatically ?
(2) Can database have awareness of whether the link is available or not ? For example A is dialing modem, and B realize that there is a incoming call.
(3) Once link is established, how two databases start to do the transcation replica ? Does SQL server has such ability to do this automatically ?
(4) Can database notice PC program that changes has occured ? so PC program can process it, see display new data.
Hi!Can anybody tell me what I'm missing here?I'm trying to insert a new record into a sql-database from code atruntime (not stored procedure),but get the following error message:"The 'strUn' is not permitted in this context.Only constants, expressions or variables allowed here.Column names are not permitted."//Create sql connectionSqlConnection con = new SqlConnection("server=LocalHost;database=Users;uid=geir;pwd=geir");//Open database connectioncon.Open();//Create variables to hold values from textboxesstring strUn = txtUsername.Text;string strPw = txtPassword.Text;//Create a sqlCommand to insert textbox values into sql-databaseSqlCommand sqlcmd = new SqlCommand();sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)VALUES(strUn, strPw)";sqlcmd.Connection = con;try{sqlcmd.ExecuteNonQuery();}catch(SqlException ex){lblInfo.Text = "ExecuteNonQuery failed because: " +"" +ex.Message;}finally{con.Close();}
I am talking to SQL Server CE through LINQ to SQL in Visual Studio 2008. I have a table that uses location information as the primary key. The state associated with the location may change so sometimes I add new records and sometimes I update existing records. Before adding records to the table I verify that no existing record exists with the same primary key.
When I query against SQL Server Compact 3.5, I don't pick up records in the active transaction. I thought that the previous version of SQL Server CE did pick up records in the active transaction.
I have a table .. has 3 fields.. first is ID (Autoincrement) the other two are texts like ID | Text1 |Text2 1 |aaa |aaa2 2 |bbb |bbb2 3 |ccc |ccc2
etc.. now there is a change in my requrement I need to add 4 more rows at the beginning like. ID | Text1 | Tex2 1 |xxx |xxx2 2 |yyy |yyy2 ., . 5 |aaa |aaa2 6 |bbb |bbb2
How can I add rows in the beginning by auto adjusting the ID colum. I'm using the enterptice manager to do this>. HELP!
I`m using SQL Server 6.5 and my application does a weekly batch load of data into a database. Since it`s a re-runnable batch process, there is no need for logging, the log files just fill up, cause problems and slow the process down.
Is there a way to do an Insert into a database and have it NOT write to the transaction log?
Alternately, if I just want to dump the transaction log, is there a stored proc for doing that?
I wanted to workout with while loop as an alternative of cursor. I am working in SQL Server 2005.
I have a table TEST with following values
tid tname
1 John
2 Sam
3 Peter
I wanted to insert these records into same table or another table using following while loop. But I ended of inserting only last record in the loop, not each one above.
--DELETE FROM test1
DECLARE @id int,
@lc int,@rc int
--SET ROWCOUNT 0
SELECT tid,tname INTO #Temp FROM Test
SET @rc=@@ROWCOUNT
--SET ROWCOUNT 1
--SELECT @id=tid FROM #TEMP
SET @lc=1
WHILE @lc<=@rc
BEGIN
SELECT @id=tid FROM #TEMP
INSERT INTO TEST
SELECT @id,'ppp'
FROM (SELECT tid FROM #TEMP WHERE tid=@id) T
--SELECT * FROM #Temp
SET @lc=@lc+1
END
Any help how to insert each record using while loop will be much appreciated.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
there is a table - TableA in my DB which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables. my admin is creating a View which is just like TableA.
my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.
HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards
If anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***
I am in a scenario where my tables are refreshed every morning by a batch update. I have built a few views off of one table. To increase speed I would like to take all the rows from one of the view s and insert them into their own table. I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate
I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.
In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:
1) Is it possible to perform a JOIN on a view in the way that I would need to here?
2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?
Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.
Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns.
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Hi all, What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following? Example for: -- TABLE: set statistics io on select * from TABLE_NAME -->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0
-- VIEW: Create view VIEW_NAME as SELECT * FROM TABLE_NAME set statistics io on select * from VIEW_NAME -->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.
Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
I am working on a program for the Pocket PC platform, which will piggy-back on a helpdesk program which stores it data in an SQL server. I have no problems with getting information from the server, or any connectivity issues.
I have found that it is easy to synchronize an access .mdb file, or an ODBC Source to a .cdb file on the PPC. I have a data provider for the SQL database in question, and the "Import Database" function works fine; it pulls up a listing of all the tables and fields in the helpdesk database.
My problem is as follows. The table used to store the memo information is at least 20-30 megs. I only need a few key entries from that table. I also need to make the sync process as simple as possible (that's why i was looking into activesync), but there does not appear to be ANY way to import an SQL View, as opposed to a table.
SQL CE appears to be one possible solution, but we are running SQL 7, and the CE server tools require SQL 2000.
So, is there a way to trick activesync into sync'ing a VIEW instead of a table?
Do you have ANY OTHER ideas, which would be simple come time to sync?
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
Have a table with Data and Dependencies(Foreign Keys) and Stored Procedures, views etc. Need the Data in that table to put in different order. For exampl, put older years in the begining or end so when sorted by year, you will get right data. Question is: If i use view it will be based on the table with Wrong entry order and if i use Temporary Table each time stored procedure is run, it will be created and overhead.
Example of the table is below with Wrong order. I should have entered the old years first. Note this is example and not the actual table!
If this example is not right, my bottom line is this: I have entered the Data in a table and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!
I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
--Query 1
SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] d WHERE TransferFromToProgram<>'' AND d.Status=12 ;
I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'.Also when viewing the original table and the new view the structures are slightly different.
CREATE VIEW j25_availableteamlist2014 AS SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status FROM j25_teamlist2014 WHERE Status NOT LIKE 'N%'
MVP's and the likeI am looking for suggestions , confirmationLet me start by saying bar none, performance is paramount with thequeries to be retured off this view/table query.To that end I am completley donormailzing the data into a view (or atable) with all possible legal combinations ( I have seen this refferedto as Croation Method ?) Its ends up at 3 total rows per account onaverage.THis query is to be executed only from the web, so once again theperformance is paramount.Ok here is what I have so far I have a set of related tables 5 or sothat are for the most part 1 to 1 but in the case of one particulartable its an average raqtion of 20 to 1.Broken all out with 150k customer rows it ends up at 500k total rows inthe viewAbout 7 cols are dynamic aggregates (Sum(tran_AMT)) , min(tran_amt) ,etcI had it in a view and with 1/5 million rows (In the view) it performedadequatley, I will most likeley get to somewhere around 10 milliontotal rows (In the view)I looked into indexing it but thats not going to happen because I amdoing several subquery's , outer joins etc.The View with a Select * returns in 40 SecondsNow i ran into an issue with recursing this query into several seperatesubqueries and hit the 256 table limit, after only about 15 recursions.Now the data only gets updated once , perhaps twice a day, in a batchtransaction.So I Put all the data from the view into a table, and indexed that, Ican return all rows in 12 seconds and any refining conditions I throwat it, like Date > 12/1/2003 (then it executes in under 6 second_ onlyslices the query time down big time (A very good thing)Since its only updates 2x a day I drop and recreate the table in my DTS, and I have triggers on the other tables for Online updates (usuallyno more that 100 a day) but to ensure the table is ALWAYS current itgets recreated on import of additional rows.Is there a name to this maddness ? My co-workers aree leary of it butthey come from a dbase background, denomailzing it into a table andthen indexing the table I couldnt be happeier with ther performance.Are there any lurking gremlins in this design ?I cannot see any pitfalls with doing this , or are there some ?,basically I am denomailizing for read-only query performance.They are concerned about the denorilization for performance, eventhough its only on a read only table (was and could be a view I justcant index this particualr view)So I guess Im looking for a , "Sounds OK" or "Sounds Great" from theSQL Gods ....Chris
I have a list of users that I want to restrict access to tables in a database. The goal is to allow the users to use select statements on the views instead of the tables. How can this be accomplished?
