Under SSMS I can add simple text and carriage returns to an extended property associated with a column. SSMS preserves the <CRLF> formatting that I used. But when I try to read this same text using, for example,
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty @name = 'col_Programs_ID_Description', @Value = 'The Unique Identifier for a specific Program', @level1Type = 'Programs', @level2Type = 'ID'
I get the following error: Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42 An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
Hello, Can I read and write the extended properties with Access?
If it€™s possible is better create a project (ADP) or a classic MDB?
When I try with this query in the query panel (in a MDB)
SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, 'schema', 'Person', 'table', default, NULL, NULL)
I have this error message:
The size of a field is too long
If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can€™t save the query.
Hi!I have a user on my database that has only "select" access(db_datareader).Problem is, I also want him to also be able to create/update extendedproperties on tables or views, but without modifying the tables'schema.I played around with GRANT but apparently, a member of "db_datareader"cannot create/modify extended properties on an object if he's not theowner of this object. I tried making this user a member of"db_datawriter", but it didn't work.Nothing short of making him member of "db_ddladmin" worked... but thenthis is too much, the user can now alter to delete tables: i DON'T wantthat!Any ideas anyone? Cheers!Ben
"Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."
However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...
I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties. FirstName have [First Name] extended propery called M_Caption. Then I created a view from the table to use in an access database. My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [Id] [int] NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[View_1] AS SELECT FirstName, LastName FROM dbo.Person GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Person" Begin Extent = Top = 6 Left = 38 Bottom = 99 Right = 189 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,
AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine. What I haven't found anywhere on Help or MSDN online is what these extended properties mean. Could someone enlighten me? 1. What is a description of the above properties? 2. What is the benefit of using them (clearly the function works without using them). Does it help debugging? 3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
I am trying to run a stored procedure in SQL 2005 which calls a registry value using the RegRead stored procedure which in conjunction executes a line of code. This stored procedure has previously worked in both Sql 7/2000.
However, I am getting the following error when trying to run it in SQL 2005:
Msg 22001, Level 16, State 1, Line 0 xp_regread() returned error 5, 'Access is denied.'
Does anyone know if Extended Stored Procedures are supported in SQL 2005?
Hello, I'm trying to enable extended logging to resolve some conflicts. After doing a google search, I found KB312292 which says to follow what's in books online on modifying the merge agent. I tried to add the following parameters in the agent profile:
-Output x:merge.txt
-OutputVerboseLevel 2
When I save the profile I get an error saying that SQL was expecting an integer for -Output. I've also tried using the -OutputMessageFile parameter but this didn't create the file, nor did it change the logging detail in the msmerge_conflicts_info table.
I am having an issue with the login time from my program to authenticate through the SQLExpress. I have the latest version and I am using for code this :
Dim cnn As New SqlConnection(Globals.m_con_str) Dim cmd As New SqlCommand("star_login", cnn)
dr = cmd.ExecuteReader() If dr.HasRows Then While dr.Read() ..........execution of successful login end while end if
If cnn.State = ConnectionState.Open Then cnn.Close() End If
My issue is this. Once you have done this once speed is good from there on out. After you log out of the program, the login is good. I have tried reindexing because I though this might be the issue but it did nothing.
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .
And that extended stored procedures should be abandonded in favour of CLR framework procedures.
And how most articles explain how to convert ESPs to CLR procedures!!!!!
But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.
This cannot be done by managed code, therefore cannot be done by C++ CLR.
It is also ill suited for C# .
Sounds like a job for C++ extended stored procedure.
But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB. I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable] [SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")] public class MyUDT : INullable, IBinarySerialize {
/// <summary> /// Set or Get the Table Name where the UDT is applied. /// </summary> public string MyTable { get { return this._myTable; } set { this._myTable = value; } }
/// <summary> /// Set or Get the Table's Column Name where the UDT is applied. /// </summary> public string MyColumn { get { return this._myColumn; } set { this._myColumn = value; } }
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from SQL Server Management Studio within the Column Properties Panel?
If it is not possible, is there a way for any UDT to get back from the sql server execution context the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
I've installed Windows 2003 SP2 in a stand-alone cluster (will be adding a second node later). I'm now installing SQL Server 2005 Developer Edition 64-bit extended and would like to have the "Create a SQL Server Failover Cluster" box to not be grayed out. Even when I check the "SQL Server Database Services" box, it still has the cluster box grayed out.
Another Newbie here...sorry if this is an easy question: How do I extract Table Properties (field names, type, length, PK/FK) from multiple tables and into an Excel spreadsheet?
In SQL 2005, the linked servers are not automatically enabled for RPC / RPC OUT like they were in SQL 2000. I have set the properties for my linked server by using SQL Management Studio.
I right click on the Linked Server --> Properties --> Server Options
There are options like: Collation Compatible, Data Access, RPC, RPC OUT, Use Remote Collation
There is a drop down box to set each value to TRUE or FALSE. Setting them through management studio works great, however for the production environment I need to set these properties programmatically. Is there a stored procedure or registry setting that would allow my program to set these values for a given linked server?
I have just upgraded to SQL Server 2005 from SQL Server 2000.In Microsoft SQL Server Management Studio, when I click on databaseproperties, I receive the following error:-Cannot resolve the collation conflict between"Latin1_General_CI_AS"and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.(Microsoft SQL Server, Error: 468)Some reference suggest that I can change the database collation byclicking database properties!What can I do?
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
i'm calling a create table SQL from my ASP. There are a lot of fields created by this sql. Wondering if an extended stored procedure would be a better choice? (can't use a stored procedure as i need to pass arrays) Will it enhance performance and also, can i use VB to make that DLL or i need to use VC?
I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
I'm attempting to copy over extended procedures from another database, and it's only copying stored when I use the "Transfer Database" wizard. Anyone know why it doesn't copy extended, and how I can get it to? Thanks!
I have to write a stored procedure to gather .dat files from multiple network shares to a single location and bulk insert them using format files. I have the SP working that accepts the filename and performs the bulk insert into my SQL tables using the appropriate format file.
Now, I had originally wrote the "copy files" portion in VB using the FSO, but was told it has to all be in a SQL SP. I have been investigating the xp_cmdshell extended procedure, which would work if I weren't pulling the .dat files from a Novell volume. For example connecting to a windows share this works...
exec xp_cmdshell 'dir servershare', no_output
but not when using a servervolume (Novell), even though from the command prompt this command will work to the novell volume.
Does anyone know of any other extended procedure or method in T-SQL that can perform file system functions?
I would like to know if I can determine the calling user from withinan extended stored procedure. I assume it's accessible in theSRV_PROC structure somewhere.Also, does anyone know of a comprehensive list of what is included inthe SRV_PROC structure?This is for SQL Server 2000.Thanks
