Code:
IF @ROW_TERM = '\n' SET @ROW_TERM = CHAR(10)
zachster17
Last Activity: Mar 4 '09, 08:03 PM
Joined: Dec 19 '07
Location: Indiana
-
We ended up figuring out a fix. We just added:
I'm not exactly sure why \n doesn't work by itself in this instance but that fix seemed to work. -
The problem is occuring inside a stored procedure. I think the sp_tables_ex only works since it is a system stored procedure for getting the table names of a linked server.
The thing I want to be able to do an INSERT INTO EXEC inside the stored procedure inserting into a local table and the EXEC being a dynamic open query (the same error also occurs just doing a simple INSERT INTO SELECT into the local db from the linked server)....Leave a comment:
-
When I do that above I get:
Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
I can do this with no errors:
Thanks,Code:INSERT INTO #TableList(Category, TableSchema, TableName, TableType, TableNotes) EXEC sp_tables_ex 'AMD'
Zach...Leave a comment:
-
Thanks Delerna--that seems like the only way. I'm kind of inexperienced when it comes to working in companies with SQL Server, but is it standard to do a lot of validating/error checking in stored procedures (instead of enforcing rules through table constraints and keys, etc.)
Zach...Leave a comment:
-
Distributed query [drawn out problem]?
Hello everyone again,
I'm working on a stored procedure that imports data from a linked server into a local database's tables. The local database is SQL Server 2000 and the linked server is actually the ODBC connection to a web-based application's data that we don't have any control over (except for using the web app and being able to view the data through the ODBC).
I want to be able to do this (in order to loop through... -
I checked out the TRY/CATCH but I'm stuck with SQL Server 2000 for this job, so I can't use this. Is there anyway to capture the name of a check constraint using the 2000 method (of checking @@ERROR) before the constraint actually stops the stored procedure (without doing a trigger of sorts?)...Leave a comment:
-
I used the adAsyncExecute and read more about it and it is working just how I wanted. Thank you!...Leave a comment:
-
Getting check constraint error from sproc?
Hello everybody,
I was wondering if it was possible to get the name of the check constraint that caused an error (if it was the cause of an error) in a stored procedure as a way to handle errors.
For example, I have a stored procedure that adds a record to a table and I'd like to have a more user-friendly error message than just showing the error name, which still is useful.
Thank you,
... -
Run sql server sproc in Access form w/o locking up?
Hi everyone,
Is there anyway to run a sql server stored procedure from access (using the ADODB.Command object) without locking up the access application?
For example, I want a list to populate when a user opens a form using a stored procedure. The only thing is that the stored procedure takes a little bit (~10 seconds) to run completely. I have it setup to display a label saying 'loading list...' before the sproc runs... -
SQL Server / Access commands/recordsets
Hey everyone,
I wasn't sure if this should go under SQL Server or Access since it is a hybrid or both.
I'm having a problem using the ADODB.Command and Recordset objects in Access to connect to SQL Server commands. I'm used to writing code to conenct to SQL in VBA (in Access) a certain way, and I recently changed to a new place where they use a different SQL Server among other things.
Anyways, the way... -
Whenever I have to quote a query (whether it is with OPENQUERY or EXEC), I simply type it regularly without the surrounding extra single quotes and then do a replace all on the query (replace ' to '')Leave a comment:
-
It looks like you should make employee ID, period_to, and period_from the fields for a primary key in the time table.
Using that as the primary key, you could only enter the employee once for each period (but still have the employee listed every pay period).
Also, it looks like you don't need the actual table 'payroll'--all the information there can be derived from the other two tables so I would actually just make a...Leave a comment:
-
thanks Delerna and ck,
I took your advice and tried to figure out again if there was a missing join condition. It turns out that, for the 2 dependent additions, joining to the dependents first and the lives second fixed the problem. I guess joining to the lives first was automatically adding the insured for each dependent, therefore the multiple records? I have no idea but it seemed to get rid of the duplicates---these joins are...Leave a comment:
-
I think that's the only way; below is a variation I actually to check records with no null values using ISNULL.
SELECT * FROM tablename
WHERE ISNULL(v1 + v2 + v3 + v4 + v5 + v6 + ... + v24,'n') <> 'n'
If anything inside is null it treats the whole thing as null.
where n are the fields you want to check for null values
All the fields I check are varchar fields, but I think you would...Leave a comment:
-
edit: n/m; i didn't see that you already know the password
You could also open up the management studio, right click on a database, click generate scripts, and script all objects to a new window (which creates the sql to re-create your whole database-sprocs and all) and just do a ctrl+f for phrases like 'pass' and 'password' if you don't know the exact password.
ZachLeave a comment:
-
Ok, here's some stuff I forgot:
The Client table I link to maps the companies of the employees and the dependents. I link to that in order to get the ExternalID which is how the vendor where I submit the file maps the information.
The output transaction log looks like this:
TransactionID;L iveType;Insured ID;DepLastName; DepFirstName;Re lationship;Outp utFile
Once the file is created, the program...Leave a comment:
-
DISTINCT UNION ALL query help (employees and dependents)
Hi everyone,
First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives table) that has all the dependents of the employees. I have also have an alternate ID table in case an employee has more than 1 id.
I have to export all the data into a fixed-text-length file with very combination... -
Hey guys,
I solved my own problem. I left the END out of the CASE clause (duh!).
Isn't it annoying when you always make the same mistake?
ZachLeave a comment:
-
Update query not working in SQL Server
Hello everyone,
I'm having difficult getting an update query to work. What I'm doing is search a table with filenames against another table that has filename wildcards that search against the filenames--if a match is found then it updates the status of the files.
Code is below; let me know if I can clarify anything for you.
Thanks,
Zach
...Code:UPDATE ed_tblFiles
-
SQL server multi-statement table function w/ openquery
Hello all,
I'm trying to write a multi-statement table function that returns a table of addresses from a remote database (Oracle) using OpenQuery and I'm having a hard time getting it to work with the 1 variable constraint it has (provider ID)
The code is below:
...Code:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION pmf_udfProviderChangeAddressTable
No activity results to display
Leave a comment: