Database Discussion Boards

Re: complicated SQL Join request from two server

Richard Deeming16-Apr-20 0:08

Richard Deeming

16-Apr-20 0:08

Your join seems to be confused about the table names. According to your question, your tables are:

Database1.dbo.informationCity
Database2.dbo.city

But according to your query, the tables are:

mo.dbo.Database1 (Completely different name)
Database2.dbo.[city ] (Extra space at the end of the name)

There's also nothing in there that would suggest the databases are on different servers. If they are, you'd need to create a linked server on the server where this query will run pointing to the other server, and use the four part name of the table you're trying to query - [Linked Server Name].[Database Name].[Schema Name].[Table Name].

Create Linked Servers - SQL Server | Microsoft Docs[^]

If it still doesn't work once you've fixed that, you'll need to provide the full details of the error(s) you're getting.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: complicated SQL Join request from two server

Member 1473386616-Apr-20 2:36

Member 14733866

16-Apr-20 2:36

thank you sir for your Help , i fixed it and it works
now is that possible to get automaticaly my results to an Excel file => simply export my result to excel directly

Re: complicated SQL Join request from two server

Richard Deeming16-Apr-20 2:48

Richard Deeming

16-Apr-20 2:48

Yes. For example, from SQL Server Management Studio:
Start the SQL Server Import and Export Wizard - Integration Services (SSIS) | Microsoft Docs[^]

From .NET code, you could use a library such as ClosedXML[^] to create an Excel sheet containing the query results. (Previously, I would have recommended EPPlus, but that's no longer free for commercial use.)

Or you could write the results out to a CSV file, which Excel will be able to open. I like to use the CsvHelper[^] for reading and writing CSV files, but other libraries are available (eg: fastCSV[^]).

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: complicated SQL Join request from two server

Member 1473386616-Apr-20 3:04

Member 14733866

16-Apr-20 3:04

sorry but i am a beginner , i do not know how to start exactly
i have just my sql request which is correct and works well

Getting data from simple SQLite table returns NULL

Uranium-23511-Apr-20 23:02

Uranium-235

11-Apr-20 23:02

i'm used to MySQL, this is...different. A table with just one row

Using "DB Browser for SQLLite"...structure

CREATE TABLE "appinfo" (
"version" TEXT NOT NULL,
"passworded" INTEGER NOT NULL,
"username" TEXT NOT NULL
);

I added a row. "1", 1, "Admin" with DB Browser

when I query it "SELECT * FROM addinfo;" with DB browser I get NULL for each field (blank on my c# app)

what am I missing here?

thanks

Re: Getting data from simple SQLite table returns NULL

Richard MacCutchan11-Apr-20 23:19

Richard MacCutchan

11-Apr-20 23:19

Uranium-235 wrote:
what am I missing here?

The correct spelling of "appinfo".

Re: Getting data from simple SQLite table returns NULL

Uranium-23512-Apr-20 8:26

Uranium-235

12-Apr-20 8:26

no, that wasn't a copy & paste, it was a misspelling here, when I actually do it from my c# app it returns the column names (found a function to easily present col names and row data, so the columns are correct), and one row of empty data, just like in DB Browser

I just tried SELECT * FROM "main"."appinfo";

it returned <blank>, 0, <blank>

the middle number is supposed to be a 1

modified 12-Apr-20 14:39pm.

Re: Getting data from simple SQLite table returns NULL

Mycroft Holmes12-Apr-20 12:29

Mycroft Holmes

12-Apr-20 12:29

I'm having trouble parsing your query! I would presume that "Main" is your database and is included in your connection string.

If so then your query string should be "Select * from appinfo" and it should return the 1 record you have inserted.

If you want an empty datatable then "Select * from Appinfo where 1=1"

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Getting data from simple SQLite table returns NULL

Uranium-23512-Apr-20 14:28

Uranium-235

12-Apr-20 14:28

no this is from DB Browser, not even the app

Re: Getting data from simple SQLite table returns NULL

Uranium-23512-Apr-20 17:25

Uranium-235

12-Apr-20 17:25

Nevermind. I inserted it into the database with a query in DB Browser instead of using the browser and it seemed to work :shrug:

thanks for putting up with me

Re: Getting data from simple SQLite table returns NULL

Richard MacCutchan12-Apr-20 21:45

Richard MacCutchan

12-Apr-20 21:45

Whatever you think you entered perhaps did not happen. Or your query is somehow incorrect. You can easily get all the information from SQLite database files using the sqlite3 command line interface.

SQL Login Failed Message

Kevin Marois8-Apr-20 19:12

Kevin Marois

8-Apr-20 19:12

Just out the blue today I started getting this error on my Dev PC.

Cannot open database "MyDb" requested by the login. The login failed.
Login failed for user 'MicrosoftAccount\my_ms_account'.

The account name is one of my Microsoft account names, and happens to be the one tied this PC's Windows 10. There IS NO SQL login with that name. There have been no DB structure changes. Today I've done typical CRUD operations via C# & Linq-To-SQL. And again, this just started this afternoon.

I Googled it and found this. I'm hesitant to try that fix until I know what's happening. For now, if I log off and back on it seems to fix it, but then I have to close and reopen everything.

Anyone know what this is?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: SQL Login Failed Message

Richard Deeming9-Apr-20 0:27

Richard Deeming

9-Apr-20 0:27

Do you normally connect from an elevated process? If so, the BUILTIN\Administrators login would apply, and you wouldn't need a separate login for your account. But if you then tried to connect from a non-elevated process, you wouldn't be part of the Administrators group.

The SQL setup normally has a step where it adds the current user to the sysadmin role so that you don't have to elevate to connect. Was SQL installed by a different user, or did you prevent it from adding your user account?

Have you switched from a local account to a Microsoft account recently?

Did the restart fix it permanently, or did the problem come back?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Login Failed Message

Kevin Marois9-Apr-20 5:42

Kevin Marois

9-Apr-20 5:42

Richard Deeming wrote:
Do you normally connect from an elevated process?

I used Trusted Connection

Richard Deeming wrote:
Was SQL installed by a different user, or did you prevent it from adding your user account?

Richard Deeming wrote:
Have you switched from a local account to a Microsoft account recently?

Richard Deeming wrote:
Did the restart fix it permanently, or did the problem come back?

It reoccurs

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: SQL Login Failed Message

Kevin Marois9-Apr-20 6:05

Kevin Marois

9-Apr-20 6:05

Well, I just noticed that my database was in Singlr User Mode. I set it back to Multi User and the problem no longer seems to appear.

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Splitting large amounts of data in a table

Pita322-Apr-20 9:25

Pita32

2-Apr-20 9:25

Is there a way to split data into two separate tables with a single SQL statement? I know that sounds like an odd question, but I have a large table in terms of rows, and I would like to "split" the data into two separate tables for separate processing in parallel fashion. Call me crazy but I thought there was a way to do that in SQL Server, i.e. a SQL statement to divide the table into two separate tables.

Is there a way to do that or am I misremembering?

Re: Splitting large amounts of data in a table

Mycroft Holmes2-Apr-20 11:10

Mycroft Holmes

2-Apr-20 11:10

Take a look at partitioning although I would be wary of physically splitting a table simply for parallel processing, there are probably better ways of doing the job.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Splitting large amounts of data in a table

Gerry Schmitz2-Apr-20 18:29

Gerry Schmitz

2-Apr-20 18:29

Just run two selects ("in one statement"). Select "top half" (count / 2) in one case, select the rest for the other (not in; greater than; whichever).

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

Re: Splitting large amounts of data in a table

Victor Nijegorodov2-Apr-20 23:43

Victor Nijegorodov

2-Apr-20 23:43

Why not implement it in a procedure?

Question Regarding SQL Parameter Query

Michele Smith31-Mar-20 9:14

Michele Smith

31-Mar-20 9:14

Hello:

I need help with this:

SQL

SELECT *
FROM   
     (SELECT 
     CASE
          WHEN InvoiceMonth IN (1,2,3) THEN 1
          WHEN InvoiceMonth IN (4,5,6) THEN 2
          WHEN InvoiceMonth IN (7,8,9) THEN 3
          WHEN InvoiceMonth IN (10,11,12) THEN 4
     END as Quarter
FROM viewSalesTracker_Model1
WHERE Quarter = @Quarter

The error I am receiving is:

Error Source: .Net SqlClient Data Provider
Error Message: Must declare the scalar variable "@Quarter".

Re: Question Regarding SQL Parameter Query

ZurdoDev31-Mar-20 9:49

ZurdoDev

31-Mar-20 9:49

It means that the compiler does not know what @Quarter is. You have not declared it anywhere.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

Re: Question Regarding SQL Parameter Query

Victor Nijegorodov1-Apr-20 1:35

Victor Nijegorodov

1-Apr-20 1:35

Michele Smith wrote:
Error Source: .Net SqlClient Data Provider
Error Message: Must declare the scalar variable "@Quarter".

Then declare this "@Quarter"variable!

Re: Question Regarding SQL Parameter Query

Richard Deeming1-Apr-20 2:32

Richard Deeming

1-Apr-20 2:32

Once you've declared the @Quarter variable or parameter, you'll run into another error:

Invalid column name 'Quarter'.

You can't reference an alias introduced in the SELECT clause within the WHERE clause of the same statement. You either have to use a sub-query, a common table expression, or duplicate the logic of the alias in the WHERE clause.

SQL

SELECT Quarter
FROM
(
    SELECT CASE
        WHEN InvoiceMonth IN (1,2,3) THEN 1
        WHEN InvoiceMonth IN (4,5,6) THEN 2
        WHEN InvoiceMonth IN (7,8,9) THEN 3
        WHEN InvoiceMonth IN (10,11,12) THEN 4
    END As Quarter
    FROM viewSalesTracker_Model1
)
WHERE Quarter = @Quarter;

Or:

SQL

WITH cteQuarters As
(
    SELECT CASE
        WHEN InvoiceMonth IN (1,2,3) THEN 1
        WHEN InvoiceMonth IN (4,5,6) THEN 2
        WHEN InvoiceMonth IN (7,8,9) THEN 3
        WHEN InvoiceMonth IN (10,11,12) THEN 4
    END As Quarter
    FROM viewSalesTracker_Model1
)
SELECT Quarter
FROM cteQuarters
WHERE Quarter = @Quarter;

Or:

SQL

SELECT CASE
    WHEN InvoiceMonth IN (1,2,3) THEN 1
    WHEN InvoiceMonth IN (4,5,6) THEN 2
    WHEN InvoiceMonth IN (7,8,9) THEN 3
    WHEN InvoiceMonth IN (10,11,12) THEN 4
END As Quarter
FROM viewSalesTracker_Model1
WHERE CASE
    WHEN InvoiceMonth IN (1,2,3) THEN 1
    WHEN InvoiceMonth IN (4,5,6) THEN 2
    WHEN InvoiceMonth IN (7,8,9) THEN 3
    WHEN InvoiceMonth IN (10,11,12) THEN 4
END = @Quarter;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Trigger keeps inserting duplicates

greatwhite131-Mar-20 7:48

greatwhite1

31-Mar-20 7:48

I cannot figure out why my trigger keeps inserting duplicates. Do anyone see what might might be causing it.

/****** Object:  Trigger [dbo].[update_ee_so_email]    Script Date: 3/31/2020 10:34:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[update_ee_so_email]
   ON  [dbo].[import_new_soemailsubmission]

   AFTER INSERT , UPDATE, Delete
AS

BEGIN

Declare @new_agentId as uniqueidentifier --varchar(10)
Declare @new_email as varchar(50)
Declare @agentid as varchar(10)

Select  @agentid = SO.new_agentId, @new_email = new_email
FROM inserted SE join import_new_serviceorganization SO ON SE.new_agentId = SO.ID

 join app_users a on a.user_name = 'SO-' + SO.new_agentid

IF @agentid is not null
Begin

INSERT INTO EE_SO_MAILING_LISTS ([PARTNER], EMAIL_ADDRESS,STATEMENT_FLAG, REFRESH_DATE)
Values(@agentid, @new_email, 'N', GetDate())


END

Else if exists (Select * from deleted)
Begin

Select  @new_agentId = new_agentId, @new_email = new_email
        FROM deleted

     Select @agentid =new_agentId From import_new_serviceorganization Where id  = @new_agentId



      Delete from EE_SO_MAILING_LISTS where PARTNER = @agentid and EMAIL_ADDRESS  = @new_email
End


END

Re: Trigger keeps inserting duplicates

ZurdoDev31-Mar-20 9:51

ZurdoDev

31-Mar-20 9:51

You can't treat triggers like this. Triggers will often have multiple records in the inserted and deleted tables so you can't use variables.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.