Database

Re: What's the syntax error?

27-Sep-24 8:21

What's the syntax error in the following TSQL stored procedure call?

SQL

EXEC StoredProcedureName 9,'723147504','1',CONVERT(DATETIME, '09-27-2024 14:14:28'),'calculated','','','',0,0,0,0,0,0,0,0;

SSMS is telling me "Syntax error near keyword CONVERT"

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

Ron Nicholson27-Sep-24 8:44

Ron Nicholson

27-Sep-24 8:44

I typed it in as a Select and it worked fine. Changing it to and EXEC proc failed. Try converting the date then see.

SQL

DECLARE @DateT DateTime = Convert(DateTime, '09-09-2024 14:14:28')
EXEC StoredProcName 9,'12341239487','1', @DateT,'calculated','','','',0,0,0,0,0,0,0,0,0;

This works fine for me. Raiserror is the same way, can't cast or convert inline.

Jack of all trades, master of none, though often times better than master of one.

Re: What's the syntax error?

Richard Andrew x6427-Sep-24 9:23

Re: What's the syntax error?

27-Sep-24 9:23

Thanks, Ron. I guess it's not a huge problem, but it is annoying!

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

jschell27-Sep-24 12:44

jschell

27-Sep-24 12:44

Interesting. Not a new problem apparently

https://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure[^]

SQL Server Permissions

Richard Andrew x6422-Aug-24 10:32

Re: SQL Server Permissions

22-Aug-24 10:32

If I give read-only access to a SQL Server login, does that mean they cannot call stored procedures that CRUD data?

What I'm looking for is the ability for users to:

1. Read the database,
2. Execute stored procedures that CRUD data.

But NOT run any DDL. Meaning they cannot change table structures or stored procedures.

I'm sure there must be a way to configure this.

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

Richard Deeming22-Aug-24 21:25

Re: SQL Server Permissions

22-Aug-24 21:25

So long as the user has permission to execute the stored procedure, and the stored procedure and the tables it references have the same owner, then it will work even if the user doesn't have any permissions on the referenced tables.

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

Richard Andrew x6423-Aug-24 5:05

SQL Server downloads and msoledbsql

23-Aug-24 5:05

Thanks, Richard!

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

Mycroft Holmes6-Aug-24 12:38

Mycroft Holmes

6-Aug-24 12:38

Attempting to recover from the Azure disaster I downloaded the developer and then the express versions of SQL Server 22. Both failed to install because of a missing/incorrect msoledbsql. I then downloaded the dll and installed it before attempting to reinstall, it still failed on the same issue.

I tried the same with 2019 version with the same issue.

Any suggestions as to how to get around the problem?

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

Re: SQL Server downloads and msoledbsql

jschell8-Aug-24 12:45

jschell

8-Aug-24 12:45

Google?

This one seemed encouraging.

https://stackoverflow.com/questions/71857345/error-when-installing-microsoft-sql-server-2019-cannot-find-the-microsoft-ole-d[^]

Re: SQL Server downloads and msoledbsql

Mycroft Holmes8-Aug-24 13:11

Mycroft Holmes

8-Aug-24 13:11

Excellent - that resolved the issue, thanks. Now any chance you can shake a person out of Azure Laugh | :laugh:

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

How to ignore and not return result sets

Richard Andrew x6427-Jul-24 8:09

Re: How to ignore and not return result sets

27-Jul-24 8:09

I have discovered that in T-SQL for Sql Server 2022, if I run a stored procedure that happens to call another stored procedure that returns a result set, then the server returns both result sets to my application.

How can I, within T-SQL, specify that the result set of an embedded stored procedure call is not part of the return to the client? Is there a way to specifically choose?

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

Richard Deeming28-Jul-24 21:29

Re: How to ignore and not return result sets

28-Jul-24 21:29

For a single resultset, you may be able to use INSERT .. EXEC to dump the resultset into a temporary table / table variable.

SQL

DECLARE @tmp TABLE ( ... );
INSERT INTO @tmp EXEC yourStoredProcedure ...;

But if you control the other stored procedure, a cleaner option would be to add another parameter to suppress the output.

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

Richard Andrew x6429-Jul-24 9:00

Migrate structure and data from SQL Server to SQLite

29-Jul-24 9:00

Thanks, Richard. I'm frankly surprised that there's no documented way to return only specific result sets. I'll probably end up using the additional parameter to suppress output.

Cheers.

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

_Flaviu24-Jul-24 5:11

Re: Migrate structure and data from SQL Server to SQLite

24-Jul-24 5:11

Is there any tool/solution to export the tables and then data from SQL Server to SQLite?

Richard MacCutchan24-Jul-24 5:15

Richard MacCutchan

24-Jul-24 5:15

Some suggestions: export sql data to sqlite - Google Search[^]

Re: Migrate structure and data from SQL Server to SQLite

_Flaviu24-Jul-24 20:49

Re: Migrate structure and data from SQL Server to SQLite

24-Jul-24 20:49

I know that tools, they simply don't run on my machine. They are C# apps and probably needs some net framework to install. But instead of digging to see why they are not run, I am concentrating to a reliable tool/solution (which I didn't find yet).

Richard Deeming24-Jul-24 21:09

Re: Migrate structure and data from SQL Server to SQLite

24-Jul-24 21:09

So basically, you've put as much effort into getting the tools you've found to run as you have into asking your question: ie: none. D'Oh! | :doh:

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

_Flaviu24-Jul-24 22:57

Re: Migrate structure and data from SQL Server to SQLite

24-Jul-24 22:57

No, that's not true. For instance, I have installed a net framework for a C# application, but still doesn't run, so, what can I do? To find out why that application is not working?

modified 25-Jul-24 5:17am.

Richard Deeming25-Jul-24 2:33

Re: Migrate structure and data from SQL Server to SQLite

25-Jul-24 2:33

_Flaviu wrote:
No, that's not true.

I suggest you re-read your own messages, remembering that nobody here can see your screen, access your computer, or read your mind. Do you really think you've put enough effort into describing the problem(s) you're having for someone to be able to help you? D'Oh! | :doh:

From my perspective, your thread so far reads as:

Summary:
I want a tool to do «x».

I've tried some tools - I won't tell you which - and none of them worked, probably because I didn't read the system requirements or install the prerequisites.

Rather than trying to fix the errors, or tell you what the errors are and ask for help with fixing them, I've added those tools to a secret shitlist of "unreliable tools".

I now demand that someone provide me with a tool that isn't on my secret shitlist - and no, I won't tell you what's on it! - that works first time, with zero effort on my part.

Remember, the effort someone is going to put into answering your question is directly related to the effort you put into asking it. If we have to keep dragging tiny snippets of information out of you in dribs and drabs, then why should we bother trying to help?

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

_Flaviu25-Jul-24 4:11

Re: Migrate structure and data from SQL Server to SQLite

25-Jul-24 4:11

I got your point.

_Flaviu24-Jul-24 23:20