Database

Eddy Vluggen28-Feb-13 9:25

Eddy Vluggen

28-Feb-13 9:25

You don't want to do this using a linked server; cleanest way is to generate the CSV (there's documentation on the internet); to send the CSV, and to bulkload it.

SQLite does not use TSQL. If you use multiple database-systems from different vendors, it'd be preferable to keep your SQL in the SQL92-dialect.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jojoba20111-Mar-13 20:23

1-Mar-13 20:23

Realy thanks!
I have installed an app android that do same as what i wanna and i have installed it and got this file that have info but cannot read that.
what type of data it have .is it SQLite or something else.
how can i do that same?
download the data file :
http://upload.ugm.ac.id/221asnaf
and download app :
http://upload.ugm.ac.id/561A0829-MashaghelHamrah.apk[^]

thanks in advanced !

Mycroft Holmes27-Feb-13 18:50

27-Feb-13 18:50

Is this a once off operation or do you need to repeat it. One time, use the CSV method.

If it must be repeatable then you need to set up a service (WCF) between the databases, you should not connect an online database directly to a remote database.

Never underestimate the power of human stupidity
RAH

jojoba201127-Feb-13 18:55

27-Feb-13 18:55

thanks for solution
I need to connect and get all info every 2 days.
cause the data of that table change every 2 days.

Mycroft Holmes27-Feb-13 19:28

27-Feb-13 19:28

Then you need to build a service between the databases, we use WCF.

Never underestimate the power of human stupidity
RAH

jojoba201127-Feb-13 19:43

27-Feb-13 19:43

can please give small sample?

Mycroft Holmes27-Feb-13 19:51

27-Feb-13 19:51

There are plenty available with a quick search here articles[^] and google[^]

Never underestimate the power of human stupidity
RAH

jojoba201128-Feb-13 2:41

28-Feb-13 2:41

XML

i got this code :
but cannot run for SQLite :

i get this error in sqlite :

<code>SQLite error near "INTO" : syntax error </code>


<pre lang="sql">cmd.CommandText = @&quot;SELECT top(5)  IdAmlak, ParentId, NameOpp, Price, [Square],
       OstanShahrMantagha, PhoneAddress, CountAmount
    INTO Melk
FROM      OPENDATASOURCE(
         &#39;SQLOLEDB&#39;,
         &#39;Data Source=217.66.216.77,1633;User ID=tinadbuser;Password=dbtin123ir&#39;
                 ).[CRM-CMS].dbo.tblTesting&quot;;</pre>

Mycroft Holmes28-Feb-13 11:34

28-Feb-13 11:34

At a guess I would say SQL Lite does not support INTO so make the query like a normal insert statement.

SQL

Insert Melk (field, names, list) Select ...

Never underestimate the power of human stupidity
RAH

sql server 2008 query,

gavindon27-Feb-13 15:57

gavindon

27-Feb-13 15:57

Ok here is my issue. I know how to do a count, count distinct and all that.
I have a column in my table that has 3 different values(only three, never more), so every row that is entered with have one of those three values.
The values are,
Bundle
Bowed Board
Jam

I am trying to figure out how to write a query that will count how many times of EACH value there is in the table. ie... 55 bundles... 10 bowed boards.... 5 jams...

for some reason I just cant get this one in my head. any help to be had? I do need it to be a single query as it is going in a dataset for a SSRS report Matrix.

Treat stressful situations like a dog, if you can't eat it, play with it or screw it, then just piss on it and walk away.

Be careful which toes you step on today, they might be connected to the foot that kicks your butt tomorrow.

_Damian S_27-Feb-13 16:11

27-Feb-13 16:11

SQL

select sum(iif(FIELDNAME = 'Bundle', 1, 0)) as NumBundles,  
sum(iif(FIELDNAME = 'Bowed Board', 1, 0)) as NumBowedBoards,  
sum(iif(FIELDNAME = 'Jam', 1, 0)) as NumJams  
from TABLENAME

One option that doesn't rely on any rollup or compute magic...

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

gavindon27-Feb-13 17:14

gavindon

27-Feb-13 17:14

That didn't quite work but it did set me on the right train of thought and for that I thank you much.
Solved with a couple of simple views combined in a query and let the SSRS report handle the count..
And I can easily reuse the views in a couple of other reports for different level of management..

_Damian S_27-Feb-13 17:34

27-Feb-13 17:34

No worries... Glad someone's making progress today... I'm still in SQL hell... (See post below)

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Mycroft Holmes27-Feb-13 18:45

27-Feb-13 18:45

What is wrong with group by and count

SQL

Select Fieldname, Count(*)
from Tablename
Group By FieldName

Never underestimate the power of human stupidity
RAH

_Damian S_27-Feb-13 18:53

27-Feb-13 18:53

The OP said they already knew about count, so I assumed they were wanting the three values on a single line...

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Mycroft Holmes27-Feb-13 19:26

27-Feb-13 19:26

Missed that, it did seem too simplistic to me!

Never underestimate the power of human stupidity
RAH

SQL Server 2012 annoyance...

gvprabu28-Feb-13 21:28

gvprabu

28-Feb-13 21:28

Hi

Its simple Use COUNT Function... like as follows

SQL

SELECT CAST(ISNULL(COUNT(ProdType),0) AS VARCHAR(10)) + ' '+ ProdType FROM ProdDtls GROUP BY ProdType

Regards,
GVPrabu

_Damian S_27-Feb-13 15:21

27-Feb-13 15:21

One of my clients has upgraded to 64bit SQL Server 2012. We have moved the test database there and I am having a bizarre issue that's doing my head in...

Even the most simple update query causes an error, which of course, is causing stored procedures to fall over...

SQL

update tbl_User
set USR_Email = 'myemail@domain.name.au'
where UserID = 5

causes the following error: "Msg 102, Level 15, State 1, Procedure tbl_Userupdate, Line 53
Incorrect syntax near '@errorNumber'."

The strange part is that the query itself runs fine. Any suggestions? Perhaps a setting that's been missed by the sysadmin who did the installation?

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

_Damian S_27-Feb-13 19:27

27-Feb-13 19:27

Alright, so a massive facepalm moment...

Further testing revealed that the problem is with a trigger on the table that was system generated to handle table relationships, and the error raising code in the trigger is being handled differently by SQL 2012 (this code is legacy from many versions of SQL ago)... so if I fix the trigger, I fix the problem (one would assume).

FML.

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Mycroft Holmes27-Feb-13 19:57

27-Feb-13 19:57

I hate f***ing triggers spit for this exact reason, for some completely unknown reason you get an error that just does not make sense and if you are not in the habit of looking under that particular rock you can spend hours chasing down the issue. I allow audit triggers and absolutely none others, the audit triggers are generated with a script that will both remove and create them according to schema table.

Never underestimate the power of human stupidity
RAH

_Damian S_27-Feb-13 20:06

27-Feb-13 20:06

These were in the system generated update and insert triggers (handling table relationships and cascades), auto generated by SQL Server about a decade ago when the system diagrams were created... any wonder I didn't think to look there!!

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Mycroft Holmes27-Feb-13 20:47

What order to be followed while generating schema upgrade script?

27-Feb-13 20:47

_Damian S_ wrote:
any wonder I didn't think to look there!!

Especially if you are not in the habit of using the bloody things. I wonder how many versions they have upgraded through, I don't recall sql auto generating triggers.

Never underestimate the power of human stupidity
RAH

Member 313707826-Feb-13 18:10

Member 3137078

26-Feb-13 18:10

I've two databases consider as OldVersion and NewVersion. What I would like to do is that I need to compare the database for DDL changes (schema changes such as column difference, datatype difference, table difference). I would like to compare and generate a script such a way that it will upgrade the OldVersion database same as NewVersion database.

This is not a straight way to do that. Say for example, if a column is FK, that column could not be altered. This is one constraint. Likewise what are the possible constraints to generate the script? How could I resolve them?

To generate the script for entire database in what order I've to work on. I look for answer such as Drop Index, Drop Constraints, Drop Columns or Alter Column, Alter Index, Add Contraints in particular order.

I know I've some tools in place for that. But I would like to generate it.

The question is lengthier one. Please bear with me and provide your solution.

Re: What order to be followed while generating schema upgrade script?

Mycroft Holmes27-Feb-13 20:01