I am trying to pull data out of an application database and transform it to another medium.
I have direct access to the database, but I cannot alter the program code.
What I want to have happen is that when an insert occurs on a particular table, execute a statement that will put the data it needs into a string and export the file.
I have the code working with one small problem. If something goes wrong in my...
bwestover
Last Activity: Nov 12 '07, 09:30 PM
Joined: Jul 3 '07
Location:
-
Commit Transaction Even on Trigger Failure
-
You can do it pretty much like you've got it set up with the input parameter.
The only thing is you cant execute a select statement directly from your string variable like that.
Instead, build the entire statement where you hope to use that variable into new string variable and then EXEC the whole thing
Example:
...Code:Declare @sqlstr varchar(250) @sqlstr = 'Select * From
-
Sure, just put that into your SQL string with the rest of the SQL statement.
So if I had an ASP variable called strVar1 containing a date string of '2007-08-16' to insert that into sql I would do the following.
Code:SQL = "Insert into DateField Select convert(datetime," & strVar1 & ")"
Leave a comment:
-
And does that work, or are the computers on the second router still offline?
it seems like you shouldn't use the WAN port on the second router at all, but rather try and just utilize the switch portion.
Modem plugs in to WAN port on router1, 3 cpus plug in to switch ports on router 1, last switch port is plugged into switch port on router 2, final 2 cpus plug into switch ports on router 2.
What kind of...Leave a comment:
-
If you can pull up pages in Firefox but not in IE than it must be a settings difference between the two. In most configurations the defaults will work.
To set IE to defaults do the following.
Select Tools->Internet Options
Click the advanced tab.
Click restore defaults
Also check the connections tab. For most users there shouldn't be anything checked or showing on this...Leave a comment:
-
Ok to do this you should only need 1 router.
A router is used to join different networks and 'route' between them. In your case Im going to assume you only want two networks 1)The internet and 2) your 5 local computers. You want the 5 computers to talk to each other, and you want all 5 to talk to the other network (the internet).
To connect the 5 computers together what you actually need is a switch with at least 5...Leave a comment:
-
SQL doesn't use "End IF"
Instead put BEGIN and END statements around your IF blocks.Leave a comment:
-
Have you tried this?
Code:Convert(datetime,'YYYY-MM-DD')
Leave a comment:
-
Sure. Theres probably lots of ways to do it.
I would use the MS Jet provider, but I've seen it done using the OLE DB provider.
I created a test.mdb on the root of my SQL server. In that MDB I created a simple table called tblTest. Here's how I selected from that table.
Code:Select * From OpenRowset('Microsoft.Jet.OLEDB.4.0', 'C:\Test.mdb';'admin';'', tblTest)Leave a comment:
-
2 things.
1) you must be 100% sure your data in fact CAN be converted to numeric (i.e. no characters)
2)you want to make sure your not trying to convert it to something smaller than it actually is.
Does your data have any decimal's in it? If so, then (18,0) isnt going to work. Try something really big at first like (30,15) and then scale it back as necessary.Leave a comment:
-
Here is the Microsoft SQL language reference.
Also a very good book I recommend is called SQL Bible by Alex Siegel and Boris Trukhnov. ISBN # 0764525840
You can get it here if your interested.
Barnes & NobleLeave a comment:
-
You could also use CASE to execute conditional logic (like IF and ELSE) inside a block statement.
For example:
MSDN Reference (CASE)Code:Select Case [type] When 'P' Then 'Passport' When 'V' Then 'Visa' Else [type] End as [aliasname] --or orginal fieldname, this can be whatever you want.
Leave a comment:
-
Try this
...Code:If Exists (Select empuserid From .....) Begin Update dbo.employee ... ... End Else Begin Insert ..... .... .... EndLeave a comment:
-
Leave a comment:
-
Do you want subtotal's by day, or do you want a single line with the final total on it? You should be able to just eliminate date from your select, eliminate your group by altogether and change your HAVING into a WHERE....Leave a comment:
-
Ok, so I figured out why excel reads 2 days off of MS SQL.
The first day is lost because Excel starts counting from 1, rather than 0. Enter a 0 into excel and have it convert it to a date and you get 1/0/1900. This isn't a real day, so it is assumed that Excel really starts its counting on 12/31/1899.
The second day is lost because of a bug between Microsoft and Pope Gregory the 8th. In 1582 the Roman Calendar was...Leave a comment:
-
bwestover replied to Subquery returned more than 1 value. This is not permitted when the subquery followsin SQL ServerI think when doing a multi-line insert based on a Select statement, you dont specify your columns in the insert statement.
Example (single line insert)
Example 2 (mulit-line insert with subquery)Code:Insert into FOO (ID, Data1, Data2) Values (1,'Foo', 'Bar')
Code:Insert into FOO Select ID, Data1, Data2 From Bar
Leave a comment:
-
You can open an excel sheet as a rowset using OPENROWSET
There are some limitations to this however. You must have local and exclusive access to the file from the SQL server. Meaning it must be directly accessible from the server and no one...Code:Insert into WKOTABLE SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\spreadsheetname.xls', 'SELECT * FROM [worksheetname$]')Leave a comment:
-
The way I have done this in the past is to use SQL Mail. Its a little cumbersome to set up as you must install a MAPI client on your SQL server. I was able to use Outlook for this, but you can use others as well. You also need an exchange server, or compatible pop3 mail server.
Here's an article that should get you started.
http://support.microsoft.com/kb/263556
Another alternative you might look into is called...Leave a comment:
-
You should be able to set 'Results to File' in Query Analyzer. To do this in Query Analyzer click Query->Results to File, or press Ctrl+Shift+T.
Then when you execute your query, it will go to a file of your choosing.
The other way is to simply copy and paste the results grid to Excel.
What version of SQL are you running? Are you trying to do this once or twice or are you trying to write a program that...Leave a comment:
No activity results to display
Leave a comment: