I see your comment that
Explicitly doing a "SET ROWCOUNT 0;" in the procedure resolved it
You can achieve the same by configuring the menu options in SSMS:
Tools > Options > Query Execution > SET ROWCOUNT: 0
The process failed (unfortunately I missed the error message) and now
the source database is missing as well. I found another post on the
same topic, which suggests locating and atttaching the .mdf file.
If the database is no longer listed on the instance, but you can still see the .mdf file, it somehow got detached during this first unsuccessful ...
I have never tried to use SSMS to copy a live database as you have done. But I have copied many, many databases without an issue.
Use the following approach in the future and you will not have the same issue again.
Take a full backup (use COPY_ONLY)
Then restore the backup with a new name.
Your live database will never be detached, so you won't have ...
All this information is the the catalog views. Here's a query to get you started:
select schema_name(t.schema_id) schema_name,
from sys.tables t
join sys.columns c
on t.object_id = c.object_id
join sys.database_permissions p
on c.object_id = p....
You can also group it in inner query.
SELECT emp.ID, emp.fname, emp.lname, emp.address1
, emp.address2, emp.city, emp.state, emp.zip, emp.phone, s.Sales
SELECT emp_id, SUM(sales) AS Sales
WHERE saledate BETWEEN @startdate AND @enddate
GROUP BY emp_id
JOIN employeeData emp ON emp.Id = s.emp_id
My issue was that I misunderstood how logins work in SQL. I was using the USER name and the LOGIN password instead of the LOGIN name and the LOGIN password. It was a noob problem. Hopefully this saves someone else the time.
Through a little experimentation, I discovered that rowcount requires "VIEW DATABASE STATE".
Methodology: Captured queries that SSMS runs and executed as a user with datareader permissions only. One in particular throws an error. After granting the permission, verified that rowcounts were now visible.
SSMS doesn't use ODBC drivers to connect to SQL Server databases. It uses the .Net Framework driver.
You cannot choose which driver it uses as it is hard-coded in the application. But why would you need to choose another one?
Linked servers are another thing. It is your DB Server itself connecting to another server. If it is SQL Server, it will use its ...
I had the same problem. I fixed it by changing the 'Login' status to 'Enabled' in Status section of the user property in 'Security/Login'section of my SQL server database
the red mark went away after changing this status.