Search Results

Search found 93284 results on 3732 pages for 'virtual server 2005 r2'.

Page 449/3732 | < Previous Page | 445 446 447 448 449 450 451 452 453 454 455 456  | Next Page >

• cannot find table or object

  - by jeni

  hi all, Am running asp.net,c# application with sql server 2005. I got some problems with database tables.I got an inconsistency errors in some tables.I tried to run the below dbcc command to remove the inconsistent datas; DBCC CHECKTABLE ('Customer',repair_allow_data_loss) WITH ALL_ERRORMSG At first i run DBCC CHECKTABLE ('Customer') it is working.but now it is not working, i got an error as Cannot find a table or object with the name "Customer". Check the system catalog. Is my commands wrong.

  Read the article

• How do I add a VMware ESXi Host to Microsoft Virtual Machine Manager?

  - by user63250

  I am trying to manage virtual machines running on a VMware ESXi host using Microsoft System Center Virtual Machine Manager. I was able to add the ESXi machine using the "Add VMware VirtualCenter server" option, but can't access any of the VMs on the datastore associated with this ESXi server. The datastore of the ESXi box is showing up with the correct name, but it won't let me see any of the VMs that have already been created; I get "There are no virtual machines on this host." Because I couldn't get any of the existing virtual machines to show up, I tried creating some new ones. When using VMM to connect to ESXi and create new VMs, I get the following error messages in the "rating explanation" section: The virtualization software on the selected host does not support virtual hard disks on an IDE bus. and The virtualization software on the host XXXXXX does not support the creation of dynamic virtual hard disk. Any ideas on why I can't manage existing machines and why I can't create new ones? The existing machines were created in vSphere. I should note that the ESXi server and the server running SCVMM are both on the same domain. I should also note that although the ESXi box has been added as a VirtualCetner server, when I try to add it through the "Add Host" option, I get an error message saying "Virtual Machine Manager cannot complete the VirtualCenter action on server EXSi because of the following error: The operation is not supported on the object."

  Read the article

• KVM Virtual guest Paused on Reboot

  - by David Hamilton

  I'm running REHL 6 and just installed a Ubuntu Server Guest via KVM set to start at boot. This works correctly and the guest loads, but it loads "paused" and requires that I manually un-pause it. Can someone give me a hint as to how I can I get the Guest OS to actually become active on boot? Here is the libvert dump as requested...Also tried libvert auto-start --- no effect. <domain type='kvm' id='1'> <name>MailServer</name> <uuid>a61dae75-1f5c-d536-718f-3c615d9b4868</uuid> <memory>4194304</memory> <currentMemory>4194304</currentMemory> <vcpu>4</vcpu> <os> <type arch='x86_64' machine='rhel6.0.0'>hvm</type> <boot dev='hd'/> </os> <features> <acpi/> <apic/> <pae/> </features> <clock offset='utc'/> <on_poweroff>destroy</on_poweroff> <on_reboot>restart</on_reboot> <on_crash>restart</on_crash> <devices> <emulator>/usr/libexec/qemu-kvm</emulator> <disk type='file' device='disk'> <driver name='qemu' type='raw' cache='none'/> <source file='/home/MailServer/MailServer-1.img'/> <target dev='hda' bus='ide'/> <alias name='ide0-0-0'/> <address type='drive' controller='0' bus='0' unit='0'/> </disk> <disk type='block' device='cdrom'> <driver name='qemu' type='raw'/> <target dev='hdc' bus='ide'/> <readonly/> <alias name='ide0-1-0'/> <address type='drive' controller='0' bus='1' unit='0'/> </disk> <controller type='ide' index='0'> <alias name='ide0'/> <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x1'/> </controller> <interface type='bridge'> <mac address='52:54:00:cd:f9:9f'/> <source bridge='br0'/> <target dev='vnet0'/> <model type='virtio'/> <alias name='net0'/> <address type='pci' domain='0x0000' bus='0x00' slot='0x03' function='0x0'/> </interface> <serial type='pty'> <source path='/dev/pts/1'/> <target port='0'/> <alias name='serial0'/> </serial> <console type='pty' tty='/dev/pts/1'> <source path='/dev/pts/1'/> <target port='0'/> <alias name='serial0'/> </console> <input type='mouse' bus='ps2'/> <graphics type='vnc' port='5900' autoport='yes'/> <sound model='ac97'> <alias name='sound0'/> <address type='pci' domain='0x0000' bus='0x00' slot='0x04' function='0x0'/> </sound> <video> <model type='cirrus' vram='9216' heads='1'/> <alias name='video0'/> <address type='pci' domain='0x0000' bus='0x00' slot='0x02' function='0x0'/> </video> <memballoon model='virtio'> <alias name='balloon0'/> <address type='pci' domain='0x0000' bus='0x00' slot='0x05' function='0x0'/> </memballoon> </devices> <seclabel type='dynamic' model='selinux'> <label>system_u:system_r:svirt_t:s0:c211,c271</label> <imagelabel>system_u:object_r:svirt_image_t:s0:c211,c271</imagelabel> </seclabel></domain>

  Read the article

• How do I add a WMware ESXi Host to Microsoft Virtual Machine Manager?

  - by user63250

  I am trying to manage virtual machines running on a VMware ESXi host using Microsoft System Center Virtual Machine Manager. I was able to add the ESXi machine using the "Add VMware VirtualCenter server" option, but can't access any of the VMs on the datastore associated with this ESXi server. The datastore of the ESXi box is showing up with the correct name, but it won't let me see any of the VMs that have already been created; I get "There are no virtual machines on this host." Because I couldn't get any of the existing virtual machines to show up, I tried creating some new ones. When using VMM to connect to ESXi and create new VMs, I get the following error messages in the "rating explanation" section: The virtualization software on the selected host does not support virtual hard disks on an IDE bus. and The virtualization software on the host XXXXXX does not support the creation of dynamic virtual hard disk. Any ideas on why I can't manage existing machines and why I can't create new ones? The existing machines were created in vSphere. I should note that the ESXi server and the server running SCVMM are both on the same domain. I should also note that although the ESXi box has been added as a VirtualCetner server, when I try to add it through the "Add Host" option, I get an error message saying "Virtual Machine Manager cannot complete the VirtualCenter action on server EXSi because of the following error: The operation is not supported on the object."

  Read the article

• netkit: why cant my router 4 pc4 ping my router 1 pc1 - how can I solve this please?

  - by donok

  Below I have four routers connected but my pc1 on r1 cannot ping my pc4 on r4 and also my pc2 on r2 cant ping my pc4 on r4 and vice versa. Below is a network diagram: and the configurations are below that, could anyone help me please on making them accessible? ![connecting 4 routers][1] I cant post my diagram on serverfault(less than 10 rep) so I did on stackoverflow and asked the same question. pc1: ifconfig eth0 195.11.14.5 netmask 255.255.255.0 broadcast 195.11.14.255 up route add default gw 195.11.14.1 dev eth0 pc2.start: ifconfig eth0 200.1.1.7 netmask 255.255.255.0 broadcast 200.1.1.255 up route add default gw 200.1.1.1 dev eth0 pc3: ifconfig eth0 195.20.14.9 netmask 255.255.255.0 broadcast 195.20.1.255 up route add default gw 195.20.14.1 dev eth0 pc4: ifconfig eth0 200.2.1.11 netmask 255.255.255.0 broadcast 200.2.1.255 up route add default gw 200.2.1.1 dev eth0 r1: ifconfig eth0 195.11.14.1 netmask 255.255.255.0 broadcast 195.11.14.255 up ifconfig eth1 100.0.0.9 netmask 255.255.255.252 broadcast 100.0.0.11 up route add -net 200.1.1.0 netmask 255.255.255.0 gw 100.0.0.10 dev eth1 route add default gw 100.0.0.10 lab.conf: if you need more on that Ill post it up but I think most of the info is there. Any help would be greatly appreciated especially trying to make a connection between pc4 and pc1, even if you think it does not make sense please explain why. Thank you.

  Read the article

• Give Access to a Subdirectory Without Giving Access to Parent Directories

  - by allquixotic

  I have a scenario involving a Windows file server where the "owner" wants to dole out permissions to a group of users of the following sort: \\server\dir1\dir2\dir3: Read & Execute and Write \\server\dir1\dir2: No permissions. \\server\dir1: No permissions. \\server: Read & Execute To my understanding, it is not possible to do this because Read & Execute permission must be granted to all the parent directories in a directory chain in order for the operating system to be able to "see" the child directories and get to them. Without this permission, you can't even obtain the security context token when trying to access the nested directory, even if you have full access to the subdirectory. We are looking for ways to get around this, without moving the data from \\server\dir1\dir2\dir3 to \\server\dir4. One workaround I thought of, but which I am not sure if it will work, is creating some sort of link or junction \\server\dir4 which is a reference to \\server\dir1\dir2\dir3. I am not sure which of the available options (if any) would work for this purpose if the user does not have Read & Execute permission on \\server\dir1\dir2 or \\server\dir1, but as far as I know, the options are these: NTFS Symbolic Link, Junction, Hard Link. So the questions: Are any of these methods suitable to accomplish my goal? Are there any other methods of linking or indirectly referencing a directory, which I haven't listed above, which might be suitable? Are there any direct solutions that don't involve granting Read & Execute to \\server\dir1 or \\server\dir2 but still allowing access to \\server\dir1\dir2\dir3?

  Read the article

• Imported Hyper-V VM hangs

  - by BParker

  I have a pair of Hyper-V servers (Server 2008 R2 + Hyper-V role). I have duplicated one VM (exported from the first Hyper-V server to the Second) which seems to keep hanging. The VM is running XP SP3, and has the Virtual Additions installed. The first version runs fine, cpu normally around 0%-2% when idle and responds happily. The second version runs very poorly, and keeps running at an idle of around 8% cpu. I have checked the VM with Process Explorer, and the Idle process is (as expected) taking nearly all the cpu time, but what it's not using doesn't seem to show up in any other process. For example at one point idle dipped to 73%, but i could only account for anoth 4.5% in other processes. After some time (around 1-2 days) the VM seems to become so unresponsive, the only way to resurect it is to power-off the VM and reboot (shutdown via the Hyper- V console of via the VM seems to be impossible). Does anyone have any suggestions on how to trace the source of the problem? Thanks

  Read the article

• Generating the query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

  - by TheImirOfGroofunkistan

  I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

  Read the article

• Classic ASP Session not working in IIS 7 Windows Server 2008 R2 x64

  - by user553361

  Hi, I've been googleing and searching here info about this but so far couldn't find anything relevant to my problem. We have a website currently working on II6 and Windows Server 2003 (x86) without any problem. Now we want to migrate our server to a Virtual Machine with Windows Server 2008 R2 (x64) and IIS7. Out current app is built in Classic ASP and SQL Server (This one located on a 2nd Server but this is staying the way it is now). The website is configured as a WebSite, not a virtual directory. Using DefaultAppPool with 4 applications. Now, the problem I'm getting is with the Sessions, or at least that's what I think since I created a simple hello.asp with this code <% response.write "Hello" response.write Session.SessionID %> And this is giving us this result: Hello error '8002801d' /hello.asp, line 3 ASP Sessions Properties Enable Session State : True Maximum Sessions : 2147483647 New ID On Secure Connection : True Time-out : 20 min This is the log in Event Viewer Warning 24/12/2010 14:03:42 Active Server Pages 9 None FailedReqLog Url http://apps.shocklogic.com:80/hello.asp App Pool DefaultAppPool Authentication anonymous User from token NT AUTHORITY\IUSR Activity ID {00000000-0000-0000-1400-0080000000F8} Site 1 Process 3312 Failure Reason STATUS_CODE Trigger Status 500 Final Status 500 Time Taken 110 msec Would be great if anyone has any ideas. Thanks, Federico

  Read the article

• Cascading Deletes in SQL Sever 2008 not working.

  - by Vaccano

  I have the following table setup. Bag | +-> BagID (Guid) +-> BagNumber (Int) BagCommentRelation | +-> BagID (Int) +-> CommentID (Guid) BagComment | +-> CommentID (Guid) +-> Text (varchar(200)) BagCommentRelation has Foreign Keys to Bag and BagComment. So, I turned on cascading deletes for both those Foreign Keys, but when I delete a bag, it does not delete the Comment row. Do need to break out a trigger for this? Or am I missing something? (I am using SQL Server 2008) Note: Posting requested SQL. This is the defintion of the BagCommentRelation table. (I had the type of the bagID wrong (I thought it was a guid but it is an int).) CREATE TABLE [dbo].[Bag_CommentRelation]( [Id] [int] IDENTITY(1,1) NOT NULL, [BagId] [int] NOT NULL, [Sequence] [int] NOT NULL, [CommentId] [int] NOT NULL, CONSTRAINT [PK_Bag_CommentRelation] PRIMARY KEY CLUSTERED ( [BagId] ASC, [Sequence] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Bag] FOREIGN KEY([BagId]) REFERENCES [dbo].[Bag] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Bag] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Comment] FOREIGN KEY([CommentId]) REFERENCES [dbo].[Comment] ([CommentId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Comment] GO The row in this table deletes but the row in the comment table does not.

  Read the article

• Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

  - by TheImirOfGroofunkistan

  I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

  Read the article

• What can be a cookie? How to set with OUTPUT? RETURNVALUE?

  - by Ronnie Chester Lynwood

  hello. i think i got some problems with setting a cookie data. for this code: Set cmdDB = Server.CreateObject("ADODB.Command") With cmdDB .ActiveConnection = ADOConM .CommandText = "usp_jaljava_member_select" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0) .Parameters.Append .CreateParameter("@TLoginName", adVarChar, adParamInput, 15,lcase(TLoginName)) .Parameters.Append .CreateParameter("@TPassword", adVarChar, adParamInput, 20,TPassword) .Parameters.Append .CreateParameter("@retval", adVarChar, adParamOutput, 50) ' .Parameters.Append .CreateParameter("@TPinCode", adVarChar, adParamInput, 15,TPinCode) .Execute,,adExecuteNoRecords RetVal = .Parameters("@retval") Ret = Trim(.Parameters("RETURN_VALUE")) 'Set .ActiveConnection = Nothing End With Set cmdDB = Nothing UTid = RetVal if Ret = 100 then deleteInvalidLogin(TLoginName) SetDomainCookie "UTid",UTid SetDomainCookie "Uid", TLoginName if redirect_domain <> "" then Response.Write "<form name=frm action=" & urlserver & " method=post><input type=hidden name=loginname value='" & TLoginName & "'><input type=hidden name=id value=""" & Request.Cookies("UTID") & """></form><script>frm.submit();</script>" Response.End else%> <% Response.Redirect ("kologin.asp?id=OK") Response.End end if RETURN_VALUE is returns as 100. But I don't know.. UTID! What is UTID have to be? If I set UTID same as UID will it work? thanks..

  Read the article

• SSIS: "Failure inserting into the read-only column <ColumnName>"

  - by Cory

  I have an Excel source going into an OLE DB destination. I'm inserting data into a view that has an INSTEAD OF trigger that handles all inserts. When I try to execute the package I receive this error: "Failure inserting into the read-only column ColumnName" What can I do to let SSIS know that this view is safe to insert into because there is an INSTEAD OF trigger that will handle the insert? EDIT (Additional info): Some more additional info. I have a flat file that is being inserted into a normalized database. My initial problem was how do I take a flat file and insert that data into multiple tables while keeping track of all the primary/foreign key relationships. My solution was to create a VIEW that mimicked the structure of the flat file and then create an INSTEAD OF trigger on that view. In my INSTEAD OF trigger I would handle the logic of maintaining all the relationships between tables My view looks something like this. CREATE VIEW ImportView AS SELECT CONVERT(varchar(100, NULL) AS CustomerName, CONVERT(varchar(100), NULL) AS Address1, CONVERT(varchar(100), NULL) AS Address2, CONVERT(varchar(100), NULL) AS City, CONVERT(char(2), NULL) AS State, CONVERT(varchar(250), NULL) AS ItemOrdered, CONVERT(int, NULL) AS QuantityOrdered ... I will never need to select from this view, I only use it to insert data into it from this flat file I receive. I need someway to tell SQL Server that the fields aren't really read only because there is an INSTEAD OF trigger on this view.

  Read the article

• Working with Decimal fields in SSIS

  - by CoffeeAddict

  I'm using SQL Server 2008 w/SP2. I've got an incoming decimal(9,2) field incoming through my OLE DB transformation to my recordset destination transformation. It's like it's reading it as something other than a decimal? I don't know..I'm not an SSIS guru. So continuing on...the problem I have starts here with me trying to stuff the value into a variable for this decimal field. In a foreach loop, I have a variable to represent this decimal field so I can work with it. The first problem that I believe is pretty well known is SSIS variables do not have a decimal type. And from my own testing and what I've read out there, people are using type object for the variable to make SSIS "happy" with decimal values? It makes mine happy. But, then in my foreach loop, I have a for loop. And inside that I'm using an E*xecute SQL Task transformation*. In it, I need to create a parameter mapping to my variable so I can work with that decimal field in my T-SQL call in here. So now I see a type decimal for the parameter and use it and set that to point to my variable. When I run SSIS and it hits my SQL call, I get this in my output window.: The type is not supported.DBTYPE_DECIMAL So I am hitting a wall here. All I wanna do is work with a decimal!!!

  Read the article

• Creating Emulated iSCSI Target in a Lab/Testing Environment using Windows Server 2008 R2

  - by Brian McCleary

  We have a single server running Windows Server 2008 with Hyper-V installed running 5 virtual machines. I have purchased a second DELL R805 Server so that we can create a fail-over cluster to our current R805 that is currently in production. Right now, our R805 connects via iSCSI to a MD3000i iSCSI SAN. Before we try to roll out the second server and clustering to our production environment, I want to be able to test and "play with" the clustering features in our lab before rolling it out. The problem is that I don't want to spend a couple thousand dollars on another iSCSI SAN server just for testing. I already have two servers in my lab that are installed with Windows Server 2008 R2 64bit (one is the R805 and another spare desktop that was laying around) and with the Hyper-V roll enabled that should be ready to test with, but I don't have an iSCSI target to use as the Cluster Shared Volume. Is there anyway to install, either on a Hyper-V image or on a external spare computer that we have some sort of emulated iSCSI target? In our lab, we obviously don't need a real SAN, just something that we can test out how to setup the clustering properly outside of our production environment. Any advise is appreciated. FYI - I have read Jose Barret's blog on WUDSS at http://blogs.technet.com/josebda/archive/2008/01/07/installing-the-evaluation-version-of-wudss-2003-refresh-and-the-microsoft-iscsi-software-target-version-3-1-on-a-vm.aspx, but it seems awfully complex. I'm hoping for an easier solution.

  Read the article

• Conversion failed when converting the varchar value to int

  - by onedaywhen

  Microsoft SQL Server 2008 (SP1), getting an unexpected 'Conversion failed' error. Not quite sure how to describe this problem, so below is a simple example. The CTE extracts the numeric portion of certain IDs using a search condition to ensure a numeric portion actually exists. The CTE is then used to find the lowest unused sequence number (kind of): CREATE TABLE IDs (ID CHAR(3) NOT NULL UNIQUE); INSERT INTO IDs (ID) VALUES ('A01'), ('A02'), ('A04'), ('ERR'); WITH ValidIDs (ID, seq) AS ( SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) FROM IDs WHERE ID LIKE 'A[0-9][0-9]' ) SELECT MIN(V1.seq) + 1 AS next_seq FROM ValidIDs AS V1 WHERE NOT EXISTS ( SELECT * FROM ValidIDs AS V2 WHERE V2.seq = V1.seq + 1 ); The error is, 'Conversion failed when converting the varchar value 'RR' to data type int.' I can't understand why the value ID = 'ERR' should be being considered for conversion because the predicate ID LIKE 'A[0-9][0-9]' should have removed the invalid row from the resultset. When the base table is substituted with an equivalent CTE the problem goes away i.e. WITH IDs (ID) AS ( SELECT 'A01' UNION ALL SELECT 'A02' UNION ALL SELECT 'A04' UNION ALL SELECT 'ERR' ), ValidIDs (ID, seq) AS ( SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) FROM IDs WHERE ID LIKE 'A[0-9][0-9]' ) SELECT MIN(V1.seq) + 1 AS next_seq FROM ValidIDs AS V1 WHERE NOT EXISTS ( SELECT * FROM ValidIDs AS V2 WHERE V2.seq = V1.seq + 1 ); Why would a base table cause this error? Is this a known issue? UPDATE @sgmoore: no, doing the filtering in one CTE and the casting in another CTE still results in the same error e.g. WITH FilteredIDs (ID) AS ( SELECT ID FROM IDs WHERE ID LIKE 'A[0-9][0-9]' ), ValidIDs (ID, seq) AS ( SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) FROM FilteredIDs ) SELECT MIN(V1.seq) + 1 AS next_seq FROM ValidIDs AS V1 WHERE NOT EXISTS ( SELECT * FROM ValidIDs AS V2 WHERE V2.seq = V1.seq + 1 );

  Read the article

• Two radically different queries against 4 mil records execute in the same time - one uses brute force.

  - by IanC

  I'm using SQL Server 2008. I have a table with over 3 million records, which is related to another table with a million records. I have spent a few days experimenting with different ways of querying these tables. I have it down to two radically different queries, both of which take 6s to execute on my laptop. The first query uses a brute force method of evaluating possibly likely matches, and removes incorrect matches via aggregate summation calculations. The second gets all possibly likely matches, then removes incorrect matches via an EXCEPT query that uses two dedicated indexes to find the low and high mismatches. Logically, one would expect the brute force to be slow and the indexes one to be fast. Not so. And I have experimented heavily with indexes until I got the best speed. Further, the brute force query doesn't require as many indexes, which means that technically it would yield better overall system performance. Below are the two execution plans. If you can't see them, please let me know and I'll re-post then in landscape orientation / mail them to you. Brute-force query: Index-based exception query: My question is, based on the execution plans, which one look more efficient? I realize that thing may change as my data grows.

  Read the article

• Is it possible to definitively identify whether a DML command was issued from a stored procedure?

  - by Ed Harper

  I have inherited a SQL Server 2008 database to which calling applications have access through stored procedures. Each table in the database has a shadow audit table into which Insert/Update/Delete operations for are logged. Performance testing on populating the audit tables showed that inserting the audit records using OUTPUT clauses was 20% or so faster than using triggers, so this has been implemented in the stored procedures. However, because this design cannot track changes made directly to the tables through DML statements issued directly against the tables, triggers have also been implemented which use the value of @@NESTLEVEL to determine whether or not to run the trigger (the assumption being that all DML run through stored procedures will have @@NESTLEVEL 1). i.e. the body of the trigger code looks something like: IF @@NESTLEVEL = 1 -- implies call is direct sql so generate history from here BEGIN ... insert into audit table This design is flawed because it won't track updates where DML statements are executed in dynamic SQL, or any other context where @@NESTLEVEL is raised above 1. Can anyone suggest a completely reliable method we can use in the triggers to execute them only if not triggered by a stored procedure? Or is this (as I suspect) not possible?

  Read the article

• After Delete Trigger Fires Only After Delete?

  - by Brandi

  I thought "after delete" meant that the trigger is not fired until after the delete has already taken place, but here is my situation... I made 3, nearly identical SQL CLR after delete triggers in C#, which worked beautifully for about a month. Suddenly, one of the three stopped working while an automated delete tool was run on it. By stopped working, I mean, records could not be deleted from the table via client software. Disabling the trigger caused deletes to be allowed, but re-enabling it interfered with the ability to delete. So my question is 'how can this be the case?' Is it possible the tool used on it futzed up the memory? It seems like even if the trigger threw an exception, if it is AFTER delete, shouldn't the records be gone? All the trigger looks like is this: ALTER TRIGGER [sysdba].[AccountTrigger] ON [sysdba].[ACCOUNT] AFTER DELETE AS EXTERNAL NAME [SQL_IO].[SQL_IO.WriteFunctions].[AccountTrigger] GO The CLR trigger does one select and one insert into another database. I don't yet know if there are any errors from SQL Server Mgmt Studio, but will update the question after I find out.

  Read the article

• Problem convert column values from VARCHAR(n) to DECIMAL

  - by Kevin Babcock

  I have a SQL Server 2000 database with a column of type VARCHAR(255). All the data is either NULL, or numeric data with up to two points of precision (e.g. '11.85'). I tried to run the following T-SQL query but received the error 'Error converting data type varchar to numeric' SELECT CAST([MyColumn] AS DECIMAL) FROM [MyTable]; I tried a more specific cast, which also failed. SELECT CAST([MyColumn] AS DECIMAL(6,2)) FROM [MyTable]; I also tried the following to see if any data is non-numeric, and the only values returned were NULL. SELECT ISNUMERIC([MyColumn]), [MyColumn] FROM [MyTable] WHERE ISNUMERIC([MyColumn]) = 0; I tried to convert to other data types, such as FLOAT and MONEY, but only MONEY was successful. So I tried the following: SELECT CAST(CAST([MyColumn] AS MONEY) AS DECIMAL) FROM [MyTable]; ...which worked just fine. Any ideas why the original query failed? Will there be a problem if I first convert to MONEY and then to DECIMAL? Thanks!

  Read the article

• Modify SQL result set before returning from stored procedure

  - by m0sa

  I have a simple table in my SQL Server 2008 DB: Tasks_Table -id -task_complete -task_active -column_1 -.. -column_N The table stores instructions for uncompleted tasks that have to be executed by a service. I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored procedure, that selects all uncompleted and inactive tasks. As the service begins to process tasks it updates the task_active flag in all the returned rows. To enable scaleing of the system I want to enable deployment of the service on more machines. Because I want to prevent a task being returned to more than 1 service I have to update the stored procedure that returns uncompleted and inactive tasks. I figured that i have to lock the table (only 1 reader at a time - I know I have to use an apropriate ISOLATION LEVEL), and updates the task_active flag in each row of the result set before returning the result set. So my question is how to modify the SELECT result set iin the stored procedure before returning it?

  Read the article

• Sql Compact and __sysobjects

  - by Scott Wisniewski

  I have some SQL Compact queries that create tables inside of transaction. This is mainly because I need to simulate temporary tables, which SQL Compact does not support. I do this by creating a real table, and then dropping it at the end of the transaction. This mostly works. Sometimes, however, when creating the tables Sql Compact will try to acquire PAGE level locks on the __sysobjects table. If there are several concurrent queries running that create "temp" tables, the attempt to acquire a page lock can result in a dead lock followed by a SqlLockTimeout exception. For normal tables I could fix this using a "with (rowlock)" hint. However, because I'm not writing the query to insert into __sysobjets (SQL server does that in response to "create table") I can't do this. Does anyone know of a way I could get around this? I've thought about pulling the table creation out of the transaction, but that opens up the possibility of phantom temporary tables that I'd then need to clean up regularly. Ideally I'd like to avoid that if possible.

  Read the article

• Can a T-SQL variable represent an entire row?

  - by elbillaf

  I'm coding for MS SQL Server 10. I have two databases that contain dozens of tables. Each table in one database contains a table with the same name in the other database. Tables with the same name have identical format (fields and data types). The contents of the two tables are similar but not identical. I need to update one based on changes made to the other, but only under certain circumstances. I think I want to use a cursor for this, but I can't find a good example to go by. So far, the MSDN examples are reading one field at a time into a variable. I do need to be able to read /modify two fields which are identical in each table, but I gotta believe there's something less tedious than declaring variables for every field of every table. I would like to be able to FETCH an entire row, check a couple of fields and then make a decision of whether I want to write the entire row to the other table after changing two fields - but do I have to declare variables for EVERY field I want to fetch / write? There's no way to just FETCH an entire row and write an entire row?

  Read the article

• How to add "missing" columns in a column group in reporting services?

  - by Gimly

  Hello, I'm trying to create a report that displays for each months of the year the quantity of goods sold. I have a query that returns the list of goods sold for each month, it looks something like this : SELECT Seller.FirstName, Seller.LastName, SellingHistory.Month, SUM(SellingHistory.QuantitySold) FROM SellingHistory JOIN Seller on SellingHistory.SellerId = Seller.SellerId WHERE SellingHistory.Year = @Year GOUP BY Seller.FirstName, Seller.LastName, SellingHistory.Month What I want to do is display a report that has a column for each months + a total column that will display for each Seller the quantity sold in the selected month. Seller Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total What I managed to do is using a matrix and a column group (group on Month) to display the columns for existing data, if I have data from January to March, it will display the 3 first columns and the total. What I would like to do is always display all the columns. I thought about making that by adding the missing months in the SQL request, but I find that a bit weird and I'm sure there must be some "cleanest" solution as this is something that must be quite frequent. Thanks. PS: I'm using SQL Server Express 2008

  Read the article

• Migrating SQL Server Databases – The DBA’s Checklist (Part 3)

  - by Sadequl Hussain

  Continuing from Part 2 of the Database Migration Checklist series: Step 10: Full-text catalogs and full-text indexing This is one area of SQL Server where people do not seem to take notice unless something goes wrong. Full-text functionality is a specialised area in database application development and is not usually implemented in your everyday OLTP systems. Nevertheless, if you are migrating a database that uses full-text indexing on one or more tables, you need to be aware a few points. First of all, SQL Server 2005 now allows full-text catalog files to be restored or attached along with the rest of the database. However, after migration, if you are unable to look at the properties of any full-text catalogs, you are probably better off dropping and recreating it. You may also get the following error messages along the way: Msg 9954, Level 16, State 2, Line 1 The Full-Text Service (msftesql) is disabled. The system administrator must enable this service. This basically means full text service is not running (disabled or stopped) in the destination instance. You will need to start it from the Configuration Manager. Similarly, if you get the following message, you will also need to drop and recreate the catalog and populate it. Msg 7624, Level 16, State 1, Line 1 Full-text catalog ‘catalog_name‘ is in an unusable state. Drop and re-create this full-text catalog. A full population of full-text indexes can be a time and resource intensive operation. Obviously you will want to schedule it for low usage hours if the database is restored in an existing production server. Also, bear in mind that any scheduled job that existed in the source server for populating the full text catalog (e.g. nightly process for incremental update) will need to be re-created in the destination. Step 11: Database collation considerations Another sticky area to consider during a migration is the collation setting. Ideally you would want to restore or attach the database in a SQL Server instance with the same collation. Although not used commonly, SQL Server allows you to change a database’s collation by using the ALTER DATABASE command: ALTER DATABASE database_name COLLATE collation_name You should not be using this command for no reason as it can get really dangerous.  When you change the database collation, it does not change the collation of the existing user table columns.  However the columns of every new table, every new UDT and subsequently created variables or parameters in code will use the new setting. The collation of every char, nchar, varchar, nvarchar, text or ntext field of the system tables will also be changed. Stored procedure and function parameters will be changed to the new collation and finally, every character-based system data type and user defined data types will also be affected. And the change may not be successful either if there are dependent objects involved. You may get one or multiple messages like the following: Cannot ALTER ‘object_name‘ because it is being referenced by object ‘dependent_object_name‘. That is why it is important to test and check for collation related issues. Collation also affects queries that use comparisons of character-based data.  If errors arise due to two sides of a comparison being in different collation orders, the COLLATE keyword can be used to cast one side to the same collation as the other. Continues…

  Read the article

< Previous Page | 445 446 447 448 449 450 451 452 453 454 455 456  | Next Page >