I am new to using BCP but I am trying to extract data generated via a stored proc. This is a continuation of my original question which was a stored procedure with temp tables. This procedure has grown a bit but the core is the same, generate temp tables, run dynamic SQL to populate the tables a...
@swasheck I don't care at this point, I just want to finish it. I believe whatever our choice they will have another integrate into a process to trigger. Either through a WCF webservice, etc
@bluefeet Although I know why that fails with temp tables, I'm not sure why it fails with the dynamic (although the inserts obviously shouldn't be run at all).
@swasheck that is the great unknown. A new table is created when the users create a new survey. The columns are created based on the number of questions for each survey. (I hope you are beginning to see the fun).
At this time I would say between 20-50 tables each with 5-10 columns.
Mikael's comment is the root of the problem with the dynamic. SET FMTONLY means that these parts aren't working: SELECT @TableName = tableName, @ColumnName = columnName FROM cl.SurveyData WHERE @RowCount = rownum
Then the empty @TableName/@Column name result in your dynamic SQL during that phase being invalid.
It's the old leaky abstraction problem.
I think 2012 has a new feature where you can declare the output format so that it doesn't have to try to infer it.
At this point, I am considering using the sql agent job to run the first stored proc with the dynamic sql and then running a second procedure after I know the data is populated
Ok, so based on our conversation if you make sure that when a friend's request is created you also create an entry in the other direction: (i.e. Friend_A asks Friend_B, but you also need a relationship between Friend_B and Friend_A) then you can run the following query to obtain the results that ...
@dezso @JackDouglas you have no idea, that was a tough cookie!
@JackDouglas you are correct, I can post but not chat while I am in the office. This one came through while I was on my way back home - I thought we had solved it last time.
@dezso you asked me a question last night but I was offline <http://chat.stackexchange.com/transcript/message/5798911#5798911> what was you refering to? :-)
Oh ok, if you selected SQL Server as the database engine it then says it depends if your trying to run a nuclear power station with it. If you click yes it takes you to a news article about how there are loads of bugs in it. The article was from 2000 when the spammer worm came out and totalled everyone's instances.... bad times! :-(
Yeah genuinely do, it's kind of cool but you just have to ensure you take a spare pair of shorts with you when you need to do a site visit. No matter how many times I go, I still am scared sh*itless...
@dezso When I first started at my company we had this 3 day workshop which was all about how important it is we check, double check and triple check what we do as people may die. When I got back to my desk somebody give me a script to run against one of the safety systems, it was only a select query but I didn't want to press run!!!
Say for instance table1 has three columns col1, col2, and col3 indexed and named as index1.
When we select data using col1 alone, will the query be as fast as when we select using three columns?
select * from table1 where col1 = ?
select * from table1 where col1 = ? and col2 = ? and col3 = ?
...
We run an in house system that uses syntax similar to SQL. In a few months were will convert entirely to Microsoft SQL Server. How do I rewrite this statement in SQL?
define vLength = if (pos('"',so_sku:length) > 0 and trun(um) = "PC")
then val(subfield(length,'"',1)) divide...
@JakeFeasel Also, the user needs reference permission on a stoplist if the stoplist is used (by default, public has the reference right on the default stoplist)
@JakeFeasel it seems like your employer supports this to some degree - do they mind that you put so much time into it? (or are you off hours right now)?
@swasheck I don't usually spend much time on it during the day - typically I work on it late in the evening, after my kids go to bed. It's lunchtime, and not real busy at the moment, so I'm spending a bit more time during the day than usual today
What can be done, if anything, when the special guest user is orphaned (not linked to any login)?
For one of my databases (SQL Server 2005), executing the following lists the guest user as an orphaned user.
exec sp_change_users_login 'report'
Results:
UserName UserSID
guest 0x3C2E66...
I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008.
Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on the new server. Then I tried to restore 'User Mappings' for the existing user but I got "User, grou...
Good news iOS users - SQL Fiddle finally is fully functional on your devices. In order to support copy/paste, I had to simplify the editors down to just textareas, so a bit of a loss there, but otherwise - good to go!
Also simplified for Android/WebOS users. I think CodeMirror wasn't working well on those either
One of my next tricks will be to support "offline" mode - using SQLite (either option) while offline. Possible since it runs completely in the browser. Only downside is no sharable URL
Might make for a fun (?) way to pass the time - fiddle with your phone's internal DB engine, while disconnected
@CadeRoux too much driving. too many preservatives. when i was in turkey, we'd walk to market every day to get food. fresh. exercise. get only what you need. good stuff
@swasheck What's not accounted for by high fructose corn syrup is regular sugar, fat and no exercise.
@swasheck I've been successful at cutting out most of the HFCS in stuff we buy from the grocery. And less sugar in most other things. Cheesy carbs are a challenge with the kids, though, between the mac and cheese and the grilled cheese and the quesadillas.
did you hear about how they got around the "hfcs" thing? they relabeled it as "corn sugar" and now they're in compliance
quesadillas are a big hit in our home
part of it is the whole concept of who is in control. the reality is that the US is a capitalism that has run amuck --- no longer dictated by the people but by the dollar. i'm not all anti-us anarchist ... just observing what's happenin'
i'd still rather be here than anywhere (other than turkey or new zealand)
@swasheck Just got to keep plugging away at it with our own dollars and getting people educated. I think people should seriously enjoy their food. And everything just needs to be done in enlightened disciplined moderation. That's why I support the Bloomberg-style laws. People just can't control themselves. Free-refills, sure!
@swasheck I have a soda a couple times a week and I try only to have Mexican Coke. Otherwise, beer is healthier and I have that instead - deliberately. I get water instead of a refill at a restaurant and I go up to the fountain machine and get a plain soda water instead of a syrup drink.
@swasheck I just love to drink most things. When I first started working my first job, we had free sodas - I must have drunk a 6-pack of Coke or Barq's every day. I gave it up and felt so much better - between the caffeine and the sugar - that really cut down on the stress.
In my stored procedure I am trying to insert records in to a temporary table
--Temporary table
create table #CR_TMP
(
ct_co_id int NULL
, ct_nbr int NULL
, ctrct_srvc_type char(4) NULL
, start_date ...
It's amazing how people think we're actually mind-readers.
I mean if I could read minds I wouldn't be sitting here now. I'd be making billions on Wall St ... or more likely sitting in a corner, rocking silently, waiting for the bad thoughts to go away. :v
my query is:
select a from b where c in (
select d from e where f in (
select f from e where d=100)
and e!=100 group by e order by count(e) desc
)
)
this query will output result what i want but I want to order it by this subquery
select d from e where f in (
select f f...
I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in sysjobschedules and sysschedules, I can see that the ...
About an hour. Had a lot of time to kill waiting around for the stupid background thread to finally update the table a second time.
I'm really surprised there isn't a whitepaper on Agent internals. It's one of the oldest features in SQL Server and it's probably the one that is one of the least understood.
Hmm. Just discovered one of our databases hasn't had any maintenance in the last year and a half because our custom version of sp_msforeachdb deliberately skipped it. Oh the joy
And anoher database hasn't had any maintenance in six months because that same version of sp_msforeachdb will skip any databases that have user connections. Egads.
Say for instance table1 has three columns col1, col2, and col3 indexed and named as index1.
When we select data using col1 alone, will the query be as fast as when we select using three columns?
select * from table1 where col1 = ?
select * from table1 where col1 = ? and col2 = ? and col3 = ?
...
We run an in house system that uses syntax similar to SQL. In a few months were will convert entirely to Microsoft SQL Server. How do I rewrite this statement in SQL?
define vLength = if (pos('"',so_sku:length) > 0 and trun(um) = "PC")
then val(subfield(length,'"',1)) divide...
In my stored procedure I am trying to insert records in to a temporary table
--Temporary table
create table #CR_TMP
(
ct_co_id int NULL
, ct_nbr int NULL
, ctrct_srvc_type char(4) NULL
, start_date ...
my query is:
select a from b where c in (
select d from e where f in (
select f from e where d=100)
and e!=100 group by e order by count(e) desc
)
)
this query will output result what i want but I want to order it by this subquery
select d from e where f in (
select f f...
I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in sysjobschedules and sysschedules, I can see that the ...
