Database

Re: SQLite sqlite_schema missing

4-Jan-22 7:16

Thanks a lot. That was the issue.

I have a small request: can you share which structure has this table, sqlite_master ?

I need a database_id, table_id, or appropriate.

_Flaviu4-Jan-22 7:17

Date and Time Add Incompatibility between sql 2012 and sql 2018

4-Jan-22 7:17

Ahh, I think I have found it: SQLite: sqlite_master (internal schema object)[^]

Medo-I23-Dec-21 7:42

Medo-I

23-Dec-21 7:42

Hello all

i have stored procedure running fine in MS sql 2012 but when i run it in ms sql 2018 i got this error message:

The data types datetime and time are incompatible in the add operator.

this is the line of the error :

SQL

(CONVERT(datetime, shfts.Date) + SftpStartTime) as SftStartDateTime,
case when ((CONVERT(datetime, shfts.Date) +SftpEndTime) 
			> (CONVERT(datetime, shfts.Date) + SftpStartTime) )

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

Richard Andrew x6423-Dec-21 8:56

Richard Andrew x64

23-Dec-21 8:56

I think you'll have to use the DATEADD function instead of just the addition operator.

The difficult we do right away...
...the impossible takes slightly longer.

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

CHill6029-Dec-21 5:31

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

29-Dec-21 5:31

Your problem is occurring because you cannot add time to a date using columns or variables - although you can do it with literals - but only if you convert the date to a datetimeThis is valid - but doesn't help you I know, just putting it out there

SQL

select convert(datetime, shftsDate) + '16:12:00'

SftStartDateTime is obviously just a time so you can't use the DateAdd function - if you try you will get another error

Quote:
Argument data type time is invalid for argument 2 of dateadd function.

Converting that time into "seconds from midnight" could be an option, but that is a real PITA - Google for it and see how awful the solutions are Smile | :)

The solution is to ensure that both operands of are of the same type. E.g.

SQL

select convert(datetime, @shftsDate) + convert(datetime, @SftpStartTime);

jschell29-Dec-21 7:34

jschell

29-Dec-21 7:34

Medo-I wrote:
SftpStartTime

Kind of curious if this is just a 'time' (no date.) If so then I would say the code is broken regardless.

I am more curious why it would fail for one version. Googling did not provide an answer but it does provide similar problems.

The data types datetime and time are incompatible in the add operator[^]

sql - Add datetime and time - Stack Overflow[^]

Since the example does not provide the values being used perhaps it works for some values and not others.

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

Richard Deeming4-Jan-22 0:44

Richard Deeming

4-Jan-22 0:44

Check your database compatibility level - it's likely that you had it set to 100 on your SQL Server 2012 instance, but it's set to 110 or higher on your SQL Server 2016/2019 instance.

(NB: There is no SQL Server 2018 version.)

As a temporary workaround, you may be able to set the compatibility level back to 100. But this will prevent you from using any features introduced after SQL Server 2008.

The correct fix would be to change your code to use DATEADD instead:

SQL

DATEADD(second, DATEDIFF(second, 0, SftpStartTime), shfts.Date) As SFtStartDateTime,

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

Roelf

floris rudolf vieira22-Dec-21 6:58

floris rudolf vieira

22-Dec-21 6:58

Is this app like 3rd party cookies?

Re: Roelf

jschell23-Dec-21 6:12

jschell

23-Dec-21 6:12

More like chocolate chip

Re: Roelf

0x01AA5-Jan-22 4:44

0x01AA

5-Jan-22 4:44

Don't get you question. Can you please elaborate?

SQL SERVER

ali khan Dec202118-Dec-21 22:36

ali khan Dec2021

18-Dec-21 22:36

a network-related or instance-specific error occured while establishing a connection to SQL Server.
the server was not found or was not accessible. very that the instance name is correct and that SQL Server 
is configured to allow remote connections.(Provider:Names pipes provider, error:40 - could not open a connection
to sql server ) (Microsoft sql server, error:53)

Re: SQL SERVER

Richard MacCutchan19-Dec-21 6:57

Richard MacCutchan

19-Dec-21 6:57

Please stop reposting this message. You have been told more than once what you need to do to resolve this.

Re: SQL SERVER

0x01AA5-Jan-22 4:46

0x01AA

5-Jan-22 4:46

Please have a look here: How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'? - Stack Overflow[^]

2 queries from Oracle

_Flaviu15-Dec-21 1:41

Re: 2 queries from Oracle

15-Dec-21 1:41

Hello all. If you own an Oracle DB, could you provide me two queries?
First, get database_id and name for every database, something like this (in MSSQL):

SQL

SELECT database_id, name FROM sys.databases ORDER BY 2

and the second one, that provide every table from a database, something like that (in MSSQL):

SQL

SELECT table_name FROM %s.information_schema.tables ORDER BY 1

where %s is the database name taken from the first query.

Thank you.

scottgp15-Dec-21 2:01

scottgp

15-Dec-21 2:01

In Oracle, the equivalent for SQL Server's information_schema or sys tables would be the DICTIONARY and dynamic performance views - (https://docs.oracle.com/cd/E11882_01/server.112/e40540/datadict.htm#CNCPT002)

Your first query doesn't make sense in an Oracle context since in Oracle you only connect to one database at a time.

The second could be (to list the tables in the connected database):

SELECT * FROM ALL_TABLES;

SELECT * FROM DBA_TABLES;
depending on your rights. See the link for the difference.

modified 15-Dec-21 8:07am.

Re: 2 queries from Oracle

_Flaviu15-Dec-21 2:41

Re: 2 queries from Oracle

15-Dec-21 2:41

Thank you Scott, so, in Oracle it is not possible to list all databases ?

scottgp15-Dec-21 3:05

scottgp

15-Dec-21 3:05

I confess I use SQL Server more these days than Oracle, but the only possible option I can find that would let you see multiple databases seems to be if you're using something like Enterprise Manager Cloud Control - https://www.mydbaworld.com/extract-databases-server-os-version-of-oem-repository/]">Extract the list of all databases with server and OS version of Oracle Enterprise Manager Cloud Control repository - My DBA World

How does google classrooms classroom code works?

Member 1546436511-Dec-21 19:43

Member 15464365

11-Dec-21 19:43

I'm a new Dev.

I wanted to learn more about how google classrooms database works, Like when a student enters a Code it will then proceed to the classroom and displays all the data on that classroom? I want to learn, how does that work. Would love to hear some suggestions on where to learn more about how it works.

Re: How does google classrooms classroom code works?

CHill6013-Dec-21 1:07

Data Warehouse vs Transaction Log tables

13-Dec-21 1:07

You should be asking Google that - Classroom Help[^] or the User Community - Google Classroom Community[^]

Member 100956563-Dec-21 0:28

Member 10095656

3-Dec-21 0:28

Good day,

How could I go about justifying a Data Warehouse for the business when there are already detailed transaction log tables in the OLTP database.

I would need to give examples of why a Data Warehouse would give more and better answers when there is already a history of all updates done on "Product" as an example.

In other words. What do I put in the Data Warehouse which is not already in the transaction log tables.

Maybe someone can provide some valuable answers.

CHill606-Dec-21 6:46

6-Dec-21 6:46

I think the first thing to point out would be ..."how do you intend to get that information back out of the transaction log tables in a timely and usable way?"

There are many many resources on line that will present the argument for you e.g. Top 10 Benefits of a Data Warehouse | Datamation[^] amongst millions of others[^]

Member 100956567-Dec-21 21:32

Member 10095656

7-Dec-21 21:32

Thank you for the reply and for the link ....

Yes, I understand. One of the most popular arguments for having Data Warehouse is that it should enable the user to access data/information faster.

I have been thinking about these points (Benefits of having a data Warehouse) for a long time now. At the moment we do not have a very large business or production database. The largest of tables transaction log) consist of around 8 million records with 40 fields. So, I don't think speed of access is our problem however, I do agree with some of the other benefits like:

2. Enhances Conformity And Quality Of Data
3. Boosts Efficiency "to have to gather data from multiple sources"

In our case we have many different types of databases (data islands) most of them SQL Server... So, I think that the DW would solve the challenge of bringing everything together in order to give the user a better view of how the business when all data is brought together into one view ("to have to gather data from multiple sources").

Again, thank you for the reply and the information ....

CHill607-Dec-21 21:57

7-Dec-21 21:57

Quote:
Boosts Efficiency "to have to gather data from multiple sources"

It also lowers frustration! Smile | :)

After I had posted my reply I had to spend (waste?) quite some time combining data from 3 different sources (oh - and we do have a DW here, it's just not comprehensive. So we've introduced a second one on a different platform - you couldn't make this up really)

It's "doable", especially with modern connectors and tools, but a real PITA - I seem to waste more time sourcing data to then be merged, than I do actually doing the analysis to gain insight. Like I said - frustrating.

Perhaps then that is your most compelling argument ... A DW makes it easier and quicker to bring real insight to the business, turn data into information, to drive down costs and raise profitability.

(I'm starting to sound like an advert so I will stop there Laugh | :laugh:

)