Hi all, I'm trying to connect to SQL and enumerate all Agent jobs to find a particular and call job.envoke on it. Looked every where but can't find a code snippet on it or a sample. Anyone can assist? Cheers ...more >>

Scenario one: I have a variety of sql tables. The number of columns varys from 27 to 59. The columns headings are numbers, commencing with 1 and incremented by 1. (and the rows are dates) The data in the columns is either 1 or null . I have built a simplistic stored procedure that 'pair...more >>

Hi all, How can I find the fully qualified name of a table, if all I have is the table name I realise that this might be a bit ambigious, but I was wondering. For example, if I had a table name like employees. It could be in northwind, or in corporate or both is there a way to find this out ...more >>

Hello, I am wondering if somone could provide me with a sample SELECT for an auto number query. For example I have a table called People with two columns first_name, and last_name. There isn't a unique id to correspond with the table but would like to dynamically make one during the ret...more >>

Bumping this up to the top, scroll down a bit to see the original thread. --- I have updated the DDL with the actual tables and a sample of table data of what I am actually using. http://damageinc.org/DDL.html (The DDL is too large to post, the message gets kicked back to me) AFter you e...more >>

HI all, Learning MSSQL. What makes the SP tables etc in the master database available to all databases in the current session of MSSQL server Thanks Robert ...more >>

Hi Group, I'm looking for an alternate solution to delete rows from a temporary table. Now, when the users run reports, the data belonging to each user is stored in a temporary table, having an userid attached to each row. Before starting a new report, the program issues a delete command li...more >>

I would like to create a trigger that after a DML event, let's say an INSERT, will call an external exe file using xp_cmdshell and pass to the exe the @@identity which resulted by the INSERT. Is there a way to achieve this? ...more >>

I have a dropdown box in a .net app that I am populating from a couple tables. One is a salesrep table, with the sales rep code and a name in it. The other table is a table with a sold to sales rep code and a ship to sales rep code - which may not always be the same in both these columns. ...more >>

Okay so I'll give it another try I have an assigment design a web solution with a presention of energy consumptions from some appartments. All appartments are identical and have 5 meters, the only difference is the appartment number (3 digits) e.g. "105" all meters gets logged once every ...more >>

DECLARE @X FLOAT EXEC @X = GetArchievedTableValueNoStaff 9006, 1 PRINT @X Returns 21, but when I use this within my tsql like : DECLARE @TableSales FLOAT SET @TableSales = 1 exec @TableSales = dbo.GetArchievedTableValueNoStaff @Table_ArchiveID, @DepartmentID PRINT TableSales...more >>

Our stored procedure throws the following error when running on 2005 w/compatibility set to 90, but not 80: "Each GROUP BY expression must contain at least one column that is not an outer reference. Severity 15, State 1, Procedure "procname", line 351" The code in question is: ...... ...more >>

Hi all, A short question about min and max functions. Let's say I have the following table: contract sales_2005 sales_2006 1234 100 120 5678 870 560 Now, I want to build a query that gives the following result: contract maximum_sa...more >>

Please, the below xp_sendmail & FETCH code works wonderfully. However I've modified the @message variable to include a variable indicating the amount of records the end user needs to approve but it fails. I've tried everything I could like creating a message string in a variable and feeding ...more >>

I am trying to update a text field using dynamic sql, but am getting the following error: Invalid operator for data type. Operator equals add, type equals text. How can I update a text field using dynamic sql. I do not know what field is being updated ahead of time. sql statement from my...more >>

Hi I'm sure this is far too much of an ambiguous question, but is it better to use #tables, in an sp to bring back additional data in a table join, or is it better to create views and join them in the SP? Kind Regards Ricky ...more >>

I am trying to convert a declare syntax from SQL to MySQL the syntax is as follows: declare @x int ; set @x = (SELECT max(ixBugEvent) FROM bugevent) ; UPDATE bugevent SET ixAttachment = (SELECT max(ixAttachment) FROM attachment), ixBug = (SELECT max(ixBug) FROM bug) WHERE ix...more >>

How do I schedule the jobs to run in sequence? I have created a package to call the sql server agent jobs in sequence but looks like all the jobs are running at the same time, not waiting for the other jobs to complete. I did put the constriants not to run the second job until the first job is...more >>

I have a stored procedure that pulls from several tables and does a PRINT to generate a report. I can save the report thru Query Analyzer as a Text file, but would like this to be done automatically either in the SP or maybe a DTS package. Any examples/suggestions would be appreciated... ...more >>

Hi - I have 2 tables, which I would like to 'merge'. tblAccess and tblCars tblAccess: a_id int (identity) a_user (varchar50) a_pass (varchar20) a_cid (bigint) tblCars: c_id (identity) c_user (varchar50) c_carname (varchar50) These were joined using the a_cid to c_id field, but...more >>

My C++ program is able to read the "Enabled Protocols" for SQL2005 by reading the registry at: HKLM\Software\Microsoft\Microsoft SQL Server\<instance name>\MSSQLServer\SuperSocketLib\<protocol alias> and then reading the "Enabled" value to see if it is enabled or not. This works fine fo...more >>

I have a CreatedOn field , datetime, which has GetDate() as the default value. When I create a new record in the table itself, the field gets populated OK, but when I try to create a new record with a Microsoft datagridview control, I notice that the datecreated value does not get created. I...more >>

I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this question is amatuer. I am trying to create a stored proc that will read in a list of values from one table (Possibly using a cte?) then iterate through each record running another sp to populate a temp table and return all m...more >>

Using ms sql 2000 I have 2 tables. I have a table which has information regarding a computer scan. Each record in this table has a column called MAC which is the unique ID for each Scan. The table in question holds the various scan results of every scan from different computers. I have an in...more >>

I need to either add to the famous "Calendar" table a column called "hourlyPayPeriod" or to create table payPeriod pay interval = 14 days. For 2006: pay period#1: beginPeriodDate = 12/25/2005 endPeriodDate = 1/7/2006 (hence payperiodID = 2006-01) this shoudl allow computation of subsequen...more >>

Hello everyone, I am working with the following code: select section_master.trm_cde, section_master.crs_cde, crs_title, crs_capacity, crs_enrollment, section_master.udef_3a_1, monday, tuesday, wednesday, thursday, friday, begin_time, end_time, bldg_cde, room_cde, name_master.last_name f...more >>

I am trying to execute a visual basic script using a SQL job. I can schedule this as a scheduled task and then use a separate SQL job to compelte the formatting tasks, but prefer to keep all steps consolidated so it is easier to trouble shoot. I tried using xp_cmdshell 'job.vbs', no_output...more >>

Is xp_sqlagent_proxy_account still a valid stored procedure in sql 2005?...more >>

Hello, I have a question that has been nagging me for some time. I have a large database where several parts have JOINs to what would be the equivalent of a drop-down list in the user interface (e.g. a list of types). The (types) table only has a TypeDescID field (PK, identity) with a Typ...more >>

Does anyone know of a good resource for explaining DBCC SQLPERF(SpinLockStats)? Mostly I'm wondering what each entry is actually measuring and how that information can be applied for profiling a system?...more >>

Issue is a batch processing SP that does multiple inserts (6 tables) This statement contains a "repeat offending" NOT IN ( ) clause. -- start code SELECT @BatchID, MA.Merchant_Account_ID, 0, 0, @Batch_Date, 0, 0 FROM Merchant_Account MA WHERE Merchant_Account_ID NOT IN (SELECT El_Accoun...more >>

I need to know if it is possible to link to a SQL 2000 DB table from a different SQL 2000 DB table? We can address the different servers as part 2 of this question, if possible. Please provide an example or detailed explanation of how to code the link (in SQL 2000 Enterprise Manager?). Tha...more >>

Hi, I am using: select itemid,count(itemid) as cnt from icitemunits group by itemid Which will show me the count of records with the same itemid. It is when I add: select itemid,count(itemid) as cnt from icitemunits where cnt>1 group by itemid I get the message: Msg 207, Level...more >>

Hello All, I'd read in BOL that it is possible to connect "explicitly" to a specific TCP/IP endpoint on the server. Does anyone know how to do this? I can't find an example anywhere. Thanks in advance. -KJ ...more >>

Does anyone have a good UDF for Encryption/Decryption? I thought about using an example I found for TEA (http://www.codeproject.com/netcf/teaencryption.asp), but am not a huge SQL Server programmer. I would need this function to be called when I write a simple SELECT SQL statement on such ...more >>

I need to manage both 2000 and 2005 SQLservers. Is it possible to install SQL Enterprise admin (2000) on my desktop (which already has 2005 installed)? IF it is, how? Thanks a million Bill ...more >>

Is there a way to easily search through all stored procedures looking for a particuler text string (i.e. dsn name?)...more >>

I have a stored procedure that selects several fields from a table, including a date field. I'd like to format the date field in the stored proc so that the result reads mmddyy or mmddyyyy - without any slashes or dashes, and without the time (06/26/2006 06:53:06.373 would read 06262006). Any id...more >>

Good morning all - I need to run a report against the previous full week... for example, today the report should run for dates from 6-18-2006 to 6-24-2006. I have found code that will tell me the previous Saturday and could just subtract 7 days from that, but I am wondering if there isn't so...more >>

Hello - I can connect to other servers through QA with no problem, however, when I try to access another server through Enterprise Manager, I cannot find where I can change the server. It is defaulting to my local machine. Can someone tell me how to do this? Any help is greatly apprec...more >>

create 2 tables: USE TEMPDB GO CREATE TABLE TEST1 (ID INT) GO CREATE TABLE TEST2 (BLA INT) GO --Load some data INSERT INTO TEST1(1) INSERT INTO TEST1(2) INSERT INTO TEST1(3) INSERT INTO TEST2(1) GO -- The following statement raises an exception SELECT ID FROM TEST2 WHERE BLA = 1 GO ...more >>

We link to an Oracle DB from a SQL2000 box. The System Admins on the Oracle side are going to rename all their tables. Is there a way to rename linked tables from the new name back to the old. (i.e. Access has a right click/rename function) something that will buy us time until we can get our ...more >>

Hi, I've table named Item which is having one column "State". "State" column is of data type XML and in which some scheduleing Information is stored with some other element. Data is looks like, --- <item> <scheduling> <enabled>True</enabled> <schedule name="Custom Schedule"> <Trigg...more >>

I have a table with a column name Tdate, data type datetime, and length 8. The look of the existing data in this column is from this format DD/MM/YYYY HH: MI: SS. I want to pass to a stored procedure string and another data that will update a specific row, where the string I pass is equal to t...more >>

Does anyone have a link or something? regards Henry ...more >>

Hi, Im considering using a MS SQL database for storing logs from a set of serverapplications - today im writing loginformation in ordinary files, but since i need to do some statistics and want to perform some events when some conditions are met in the log (and ofcourse the flexibility that...more >>