I'm trying to grasp the idea of transactions fully. Therefore the following question... (ofcourse newbie, so don't laugh :D ) I have set up a (simplified) transaction in PHP (using the PHP SQL driver from microsoft). I want to get the rows I'm going to delete for some extra processing later: sqlsrv_begin_transaction($conn); $sql = "SEL...
When running a SQL query with output to text we typically get back output like this. AssetID Occurs -------------------- ----------- (0 row(s) affected) Since I am doing thousands of select statements to audit data in my table is there a way to suppress this output on SQL server? ...
Could you recommend a good resource on learning MS Access from the perspective of a developer with a good background in SQL Server. Also some best practice tips would be welcome, in areas such as multi-user access support security; both the database and the front end Access interface (Forms,Reports) deployment Basically if you have ...
I am trying to read in a text file from an SQL query (SQL Server 2005) but am not having any luck at all. I've tried various things with EXEC and xp_cmdshell, but all aren't working. This is the general way I've tried to approach this: CREATE TABLE temp (data varchar(2000)); INSERT temp EXEC master.dbo.xp_cmdshell 'type file.txt'; I ...
I wrote a trigger that needs to do some different work on a table based on which columns in a row actually updated. I accomplished this using IF UPDATE(column-name) That part works fine. It turns out, however, that there are other parts of the code that update rows by setting every single value whether the value actually changed or n...
I have a need to make a remote connection through a DSL line (getting about 16k/s) to a SQL Express 2005 instance much faster. I think I can do it by having a SQL Express 2005 instance on the client's local machine and have it be a cached version of the data on the server. I'm planning to allow changes to the remote server only and the...
I set timeout to 0 but the connection close prematuraly, what is wrong with this statement ? Using odbcconn As New OdbcConnection(DataShared.gstrCNN) odbcconn.ConnectionTimeout = 0 odbcconn.Open() Dim OdbcCmd As New OdbcCommand( _ "{ ? = CALL [proc_Cp_GenEstadoCta](" & _ PCOD_EMPR...
I have a database where all of the tables are prefixed with a set of the same characters. This was done because at one time they were in a shared database set up for pet projects with hundreds of other tables. The application, and thus the database, is now ready to be moved out of that phase and ready to be out on it's own. I would like ...
I've got a query like the one below: Select ser.key From dbo.Enrlmt ser Where ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND ser.EnrlmtStatCode = '4321' AND ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND ser.Key not in (select Key from Enrlmt ser2 ...
I'm trying to use the Resource Governor in SQL Server 2008 but I find it hard to debug the classification function and figure out what the input variables will have, i.e. does SUSER_NAME() contain the domain name? What does the APP_NAME() string look like? It's also hard to verify that it's working correctly. What group did the functi...
I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but i want to be able to transfer the data that already exists. So i'm doing something like [1]. This works but the offset set is 0 when i do the dattime to datetimeoffset assignment. Fortunately the offset that i want to set it to i...
In a table there are like 113 columns. and there are two default records in the table, one is for unknown and another is for inapplicable. So, each column has its own default value to represent unknown and inapplicable. I dont wanna write regular insert statement to get those two records. so, I tried to insert each column using a cu...
What is the best way to append spaces at the end of a char variable in SQL Server? I have found 3 ways. Any ideas which one is better? Here I am trying to pad 2 spaces at the end of FOO 1) declare @var char(5) set @var = convert(char(5),'FOO') 2) declare @var char(5) set @var = cast('FOO' AS char(5)) 3) declare @var char(5) set ...
I have a problem I am trying to resolve. We have a SQL Server 2005 running a commercial ERP system. The implication for this is that we cannot change the database structure and all of the character fields are CHAR or VARCHAR rather than Unicode types (NCHAR, NVARCHAR). We also have multiple instances of the ERP software, based on countr...
Edit: using SQL Server 2005. I have a query that has to check whether rows from a legacy database have already been imported into a new database and imports them if they are not already there. Since the legacy database was badly designed, there is no unique id for the rows from the legacy table so I have to use heuristics to decide wh...
So at the root of my DB, I have a table "Customer." Customer has foreign keys going to about 6-7 other tables such as Receipts, Addresses, Documents etc. If I were to delete a customer using SubmitChanges(), would that seek out all those records with the foreign key association and delete them too, or would I need to do like 6 queries? ...
I'm a java newbie, want to test out some hibernate goodies! I have netbeans installed, and I included the Hibernate libraries. I then created a new package named Model. I will drop my Class and xml config files in there. Do I need a special library to connect to sql server? (windows machine) ...
How can I get the @@IDENTITY for a specific table? I have been doing select * from myTable as I assume this sets the scope, from the same window SQL query window in SSMS I then run select @@IDENTITY as identt It returns identt as null which is not expected since myTable has many entrie in it already.. I expect it to return the...
I have the following select statement that finishes almost instantly. declare @weekending varchar(6) set @weekending = 100103 select InvoicesCharges.orderaccnumber, Accountnumbersorders.accountnumber from Accountnumbersorders, storeinformation, routeselecttable,InvoicesCharges, invoice where InvoicesCharges.pubid = Accountnumber...
I've read up on this on MSDN forums and here and I'm still not clear. I think this is correct: Varchar(max) will be stored as a text datatype, so that has drawbacks. So lets say your field will reliably be under 8000 characters. Like a BusinessName field in my database table. In reality, a business name will probably always be under (pul...