In a SQL server database, I have a table which contains a TEXT field which is set to allow NULLs. I need to change this to not allow NULLs. I can do this no problem via Enterprise Manager, but when I try to run the following script, alter table dbo.[EventLog] Alter column [Message] text Not null, I get an error: Cannot alter column 'Err...
When I subscribe for a report, I may chose to have a subject like: @ReportName was executed at: @ExecutionTime I would like a name like this: Your "@ReportName" report covering Sep 10 2008 - Sep 16 2008 Sep 10 2008 - Sep 16 2008 are values of the two report parameters: @DateFrom and @DateTo, respectively. Can I specify something like ...
When supplying dates to a stored procedure via a parameter I'm a little confused over which format to use for the dates. My original VBA syntax used the ADO Connection object to execute the stored procedure: Set SentDetailRS = Me.ADOConnectionToIntegrity.Execute("dbo.s_SelectAggregatedSentDetailList '" & fCSQLDate(EffectiveDate) & "'", ...
I had this question in mind and since I just discovered this site I decided to post it here. Let's say I have a table with a timestamp and a state for a given "object" (generic meaning, not OOP object); is there an optimal way to calculate the time between a state and the next occurrence of another (or same) state (what I call a "trip")...
SQL Server Version 2000. We've a bunch of desktops talking to MSSQL Server. When looking for a specific record, some desktops return the correct data, but some do not. The SQL Command is "SELECT * FROM PODORDH WHERE ([NO]=6141)" On one or two desktops, this returns a record. On the server and on all other desktops, no record is return...
I have a large csv file and I want to execute a stored procedure for each line. What's the best way to execute a stored procedure from Powershell? ...
Hi folks, Working with a SqlCommand in C# I've created a query that contains a IN (list...) part in the where clause. Instead of looping through my string list generating the list I need for the query (dangerous if you think in sqlInjection). I thought that I could create a parameter like: SELECT blahblahblah WHERE blahblahblah IN @LIS...
I erroneously delete all the rows from a MS SQL 2000 table that is used in merge replication (the table is on the publisher). I then compounded the issue by using a DTS operation to retrieve the rows from a backup database and repopulate the table. This has created the following issue: The delete operation marked the rows for deletion o...
I'm trying to write a stored procedure to select employees who have birthdays that are upcoming. SELECT * FROM Employees WHERE Birthday > @Today AND Birthday < @Today + @NumDays This will not work because the birth year is part of Birthday, so if my birthday was '09-18-1983' that will not fall between '09-18-2008' and '09-25-2008'. ...
I've got a new varchar(10) field in a database with 1000+ records. I'd like to update the table so I can have random data in the field. I'm looking for a SQL solution. I know I can use a cursor, but that seems inelegant. MS-SQL 2000,BTW ...
I'm trying to use SQLBindParameter to prepare my driver for input via SQLPutData. The field in the database is a TEXT field. My function is crafted based on MS's example here: http://msdn.microsoft.com/en-us/library/ms713824(VS.85).aspx. I've setup the environment, made the connection, and prepared my statement successfully but when I ...
I am attempting to provide a general solution for the migration of data from one schema version to another. A problem arises when the column data type from the source schema does not match that of the destination. I would like to create a query that will perform a preliminary compare on the columns data types to return which columns need...
The DBAs here maintain all SQL Server and SQL Reporting servers. I have a custom developed SQL Reporting 2005 project in Visual Studio that runs fine on my local SQL Database and Reporting instances. I need to deploy to a production server, so I had a folder created on a SQL Reporting 2005 server with permissions to upload files. Norm...
I'm about to inherit a set of large and complex set of stored procedures that do monthly processing on very large sets of data. We are in the process of debugging them so they match the original process which was written in VB6. The reason they decided to re write them in t-sql is because the vb process takes days and this new process t...
Hi All, I'm trying to register an externally hosted SQL 2000 server through Enterprise Manager which isn't on the default port and I can't see anywhere to change it within Enterprise Manager. So, the question is, how do I connect to the database if: I.P Address is 123.456.789 (example) Port is 1334 Thanks in advance EDIT: Both Rob...
I want to make a table in SqlServer that will add, on insert, a auto incremented primary key. This should be an autoincremented id similar to MySql auto_increment functionality. (Below) create table foo ( user_id int not null auto_increment, name varchar(50) ) Is there a way of doing this with out creating an insert trigger? ...
I Need to manually migrate modified stored procedures from a DEV SQL Server 2005 database instance to a TEST instance. Except for the changes I'm migrating, the databases have the same schemas. How can I quickly identify which stored procedures have been modified in the DEV database for migration to the TEST instance? I assume I can w...
I've long been a fan of Stored Procedure Keyboard Accelerators, as described in this article. When we moved from SQL 2000 to 2005, though, and from Query Analyzer to Management Studio, the handling of the arguments changed. In QA, comma-separated arguments were automatically read as two separate arguments. In SSMS -- at least for me -...
This is pretty weird. I have my Profiler open and it obviously shows that a stored procedure is called. I open the database and the SP list, but the SP doesn't exist. However, there's another SP whose name is the same except it has a prefix 'x' Is SQL Server 2005 mapping the SP name to a different one for security purposes? EDIT: I fo...
Is there an equivalent schema & data export/dumping tool for SQL Server as there is for MySQL with mysqldump. Trying to relocate a legacy ASP site and I am way out of happy place with working on a windows server. Note: The DTS export utility own seems to export data, without table defs. Using the Enterprise Manager and exporting the ...