We have a utility that does some SQL magic by reflecting the definition for some views and stored procs. For this to work, the code needs access to various fields in the tables INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.VIEWS, etc. So we wrote a cursor that applies GRANT VIEW DEFINITION on ' + @Name + ' TO tenant' where tenant i...
Hi All, We have changed some base functions we use in an application and I am trying to figure out what the best way would be for me to find and replace the existing function with the new one? The input / outputs don't change, just the schema and some of the internal logic. Is there a good approach to finding the objects (views, proc...
Invoice ID, PO Number and dueDate are shown in duplicates. TotalPrice is an alias (It should be Unit Price, total price is a mistake, so assume it Unit Price not total price) TotalShippingPrice shows the shipping price that was associated with the InvoiceID/PONumber, for every invoiceID/PONumber there will be single shipping price. Sa...
The way i designed my working sheet doesn't help me to calculate working hours easily. The output in snapshot has been gathered from multiple tables. Don't worry regarding setDate and timeEntered formatting. SetDate represents working day. tsTypeTitle represents type of shift, is it lunch time, etc. timeEntered represents the actual...
I have dts package in 2008. It was a ftp task and a mail task. i want to send an email with the task execution results (exact error messages) Is there a way to receive previous task's error codes/messages in the send email task? ...
Can I (How do I) configure Sql Server 2008 to notify an operator if any step in the Job fails? I have a Sql Server job with several steps to update data from multiple different sources, followed by one final step which performs several calculations on the data. All of the "data refresh" steps are set to "Go to next step on failure". G...
I'm trying to insert trailing spaces into a VARCHAR(50) column and the SQL insert seems to be cutting them off. Here's my code: create table #temp (field varchar(10)); insert into #temp select ' '; select LEN(field) from #temp; Unfortunately, this returns a length of zero, meaning the ' ' was inserted as a ''. I need a blank space to ...
I'm wondering if anyone has implemented a web UI on top of the AdventureWorks database? IOW, I'm not looking for just a application data layer on top of the schema. Rather, I'm wondering whether anyone has created an ecommerce front end on top the AdventureWorks schema? ...
I have a DBML on a single database in a named instance. The instance has a linked server to another MSSQL database in another server/instance. Both instances and databases have the same dbo-level user. Both databases are MSSQL 2008. I have some C# code (Framework 3.5) that uses the DBML that accesses the tables and views and sprocs on D...
The query below is working: update top(1) ShipBillInfo set shipfirstname='kkk' where CustomerId='134'; but it is showing error if i try to order by some Id: for example: update top(1) ShipBillInfo set shipfirstname='kkk' where CustomerId='134' order by OredrGUID desc; ...
id1 id2 year State Gender ==== ====== ====== ===== ======= 1 A 2008 ca M 1 B 2008 ca M 3 A 2009 ny F 3 A 2008 ny F 4 A 2009 tx F select state, gender, [year], count (distinct(cast(id1 as varchar(10)) + i...
I receive the following error: Could not load file or assembly 'Microsoft.SqlServer.Management.SqlParser, Version=10.0.0.0 ....etc... The system cannot find the file specified. Reading the following link I made a repair to SQL Server Management Objects (SMO) Class Library but no success there. Any help would be apreciated. ...
I am using Microsoft SQL Server 2008. While generating custom reports, I need to provide specific parameters based on the logged in user. That's why my app requires a table for users with their ids, so that I can join whatever else I want with them. The problem starts when I inspect the results of querying Master..SysUsers. My NT user is...
Why the output is different of this query SELECT DATALENGTH(CAST('test' AS VARCHAR)), DATALENGTH(CAST('test' AS CHAR)) outPut: 4,30 ...
Hi I have a tables of rows that exists in multiple databases. I will need to update the rows from one database into the other database and I have a primary key of int. So I am looking for a way to uniquely identify the server that added a particular row. I could use a uniqueidentifier in a default using the newid() or newseqentialid() b...
We are using sp's to convert a uploaded file into corresponding master values and their combination as the business rule demands. For this we have made 4 sp's . All these sp's are called from a single sp which the user calls from application along with the data from the file. Note: Each sp must be called after the previous one has finis...
Hai all, How to subtract two time values in sql server 2008.I am using time variables in stored procedure. please help ...
In most of the book we read that cluster index is the physical order and noncluster index is logical order of data. I have a table with more than 80 lac rows with cluster index, How this will impact on row insertion. means if cluster index is physical order of data then new data insertion must be very slow and time must be increase on ne...
I have an updatable transactional replication set with SQL Server 2008. Everything is working fine. I added a new table to the existing publication thru sp_addarticle followed by sp_addsubscription. After that I ran the snapshot agent. Snapshot has been generated only for newly added table. So the new table was successfully replicated to...
A question about updatable db views: I'm reading through some MSDN documentation on the subject, and I come across the following restriction: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. I just want to be sure I understand the restriction. I'd like to use views...